How to execute queries from VBa in Access 2003 - again



  • Once again, the forum software is TRWTF (or maybe it's me, i've been here for years, i should know)

    Anyway, here is the original post, only this time formatted :)


    Hello everyone, today i shall pass on to you teachings that i have learned from sensei Dan, previous developer in charge of this unholy access mess they call "production database frontend"



    Both the front and the back end access files with linked tables that you have to keep next to each other in a single folder (i hope you're seeing the advantage of seperating the front and back) are chock full of WTF's



    Here, for example, is how you call Access queries:





    First, you must put Query names into constants.



    Const GET_COUNTRIES As String = "qryTURGetCountries"

    Const GET_TARGET_PARTS As String = "qryTURGetTargetParts"

    Const ROLLUP_COUNTRY As String = "qryTURRollupCountry"

    Const ROLLUP_HISTORY As String = "qryTURRollupHistory"

    Const ROLLUP_TRUE_UP_BY_COUNTRY As String = "qryTURRollupTrueUpByCountry"

    Const INSERT_DETAIL As String = "qryTURInsertDetail"





    You're probably thinking the next step is something among the lines of CurrentDb.Execute INSERT_DETAIL



    NO!

    that's not how it goes, silly.



    You create a procedure called CompileTURData (yeah, TURD, i mentioned this before, don't ask -_-)

    in this procedure you create a local array



    Dim cmds() As ADODB.Command



    then it's time to add some more global constants like this:



    Const CMD_CACHE_TARGET_PART As Integer = 0

    Const CMD_TARGET_PART As Integer = 1

    Const CMD_ROLLUP_COUNTRY As Integer = 2

    Const CMD_ROLLUP_HISTORY As Integer = 3

    Const CMD_ROLLUP_TRUE_UP_BY_COUNTRY As Integer = 4

    Const CMD_INSERT_DETAIL As Integer = 5

    Const CMD_INSERT_COUNTRY As Integer = 6

    Const CMD_INSERT_HISTORY As Integer = 7

    Const CMD_INSERT_TRUE_UP_BY_COUNTRY As Integer = 8



    Don't forget to keep track of how many commands you have!!!



    Const TOTAL_CMD_OBJECTS = 9



    and now you create a new procedure that loads up the array with commands



    ReDim cmds(TOTAL_CMD_OBJECTS)

    Set cmds(CMD_CACHE_TARGET_PART) = SetupCommandObject(CACHE_TARGET_PARTS)

    Set cmds(CMD_TARGET_PART) = SetupCommandObject(GET_TARGET_PARTS)

    Set cmds(CMD_ROLLUP_COUNTRY) = SetupCommandObject(ROLLUP_COUNTRY)

    Set cmds(CMD_ROLLUP_HISTORY) = SetupCommandObject(ROLLUP_HISTORY)





    and finally, you need to create a procedure that you can call and pass it the array of commands (because it's a local variable that gets passed around) and the number constant of the command you wish to invoke



    but, before doing that, don't forget to call the procedure that instantiates the other array with parameters you want to pass !


    Happy happy fun time with Access 2003!


    The worst part is, management decided to improve reliability and speed by copying the data structure of the backend file to a remote SQL server and keeping the front end as is.



    So instead of pulling whole tables (we all know every "SELECT" must be followed by "* FROM") from a local file, it's now going to pull them from a remote server over a sluggish VPN link. HOOORAAYYY!



    My sole mission is to make them let me do the whole thing from scratch.



  • Wow.  This is eerily similar to code that I work with.  (Except ours is C#.)  It's not complete until you map the query constant values to the actual stored proc in a configuration file, and in code you do all that stuff, or if it doesn't exist in the file, you just do it the regular way.



  • Management interferes with how you should solve something? Or what do you mean by management? Architects? Project leads?



  • @Weps said:

    Management interferes with how you should solve something?

    That's how it works with my company.



  • @DanceMaster said:

    @Weps said:
    Management interferes with how you should solve something?

    That's how it works with my company.

    I don't know enough, but I already feel sorry for you. 



  • Management hires consulting agency, i guess the consultants advise moving to SQL server, with everything else staying exactly the same, which actually makes the whole matter worse because internally the old database is still being developed and each change needs to be tracked so it can be implemented on the SQL Server as well. (because the SQL server migration is almost complete) and thus the magic circle of wtfness.

    And now i'm brought into the whole mess as a new assignment. Hopefully they'll let me work with SQL Server directly (not the production db, i'm referring to skipping the step where a change is first implemented in the access backend), and hopefully, but less likely, let me rewrite the client in C#. But none of this can happen until the blackout is over, sometime early january.

    Until then i'm stuck with this mess. Well, at least i'm on an all expenses paid bussiness trip. At least i get to enjoy the pool and free cookies at the hotel.

    -EDIT-
    Btw, my trip was approved like half an hour before the ban on all travel and unnecessary expenses meant to show pretty numbers on reports. All of that money, plus more, is gonna go away anyway in early January.

    All in all, i'm left wondering if i got lucky or am i cursed. :)


Log in to reply