scott’s spot

So, since I spend so much time in Excel, I thought I’d do some blogging about my favorite business application. Yes, I’m a geek, I know. Sue me.

Recently I was trying to figure our how to change an Access query from Excel. By way of some background, I have a report that is generated in Excel via a number of SQL statements passed from Excel to Access via ADO, and the resulting recordset dropped into data sheets in the Excel file.  Okay, fine, no problem. There are endless examples on the web of how to do this. However…

I was recently tasked with resdesigning what I had originally designed to make it a little more portable. So, in that respect, I needed to pass a single criteria (or parameter) back to an Access query that I had created as part of the overall report function. This query is a time-based query, setting a week number as chosen by a user.

The previous method I used was a number of individual data files, each of which contained Sales and other data up to that week. In other words, the week 1 file had data from week 1, the week 2 file had data from weeks 1 and 2, the week 3 file had weeks 1, 2, and 3, blah blah, etc etc. The purpose of this was to help isolate a beginning of period number and and end of period inventory number.

(The yawn you just heard was my wife dropping off to sleep at this point.)

What I needed to do was to run with a single data set, but allow the users to choose the week they wanted to report on. But to do that, I needed one of the base queries to accept a criteria of that particular week number, and since the report is generated in Excel, I needed to pass that query parameter as part of the report engine.

Okay, so why pass a parameter? Why not just submit the SQL as a command? Because this parameter-accepting query is the basis for sub-queries that allow the whole thing the run (the final SQl statement is an ugly, ugly beast.)

All this being said, I tried a couple of things before landing on the solution. I tried using ADO to change the query, tried ADOX, and final landed on a hybird solution of sorts.

First, I needed an instance of the Access application, so I defined a very generic Object variable and then used CreateObject to create an instance of Access.

Then, using a reference to the Access object library, I was able to use commands normally found in the Access object model to do the work I was trying to do. The work, by the way, was simply to change the QueryDef in the one query and save it.

After some testing, debugging, and commenting, I nailed the solution.

And here it is, heavily commented for your reading enjoyment:

Sub Pass_Parameter_To_MaxTimeQry(sWeek As String)
‘   This passes a parameter to the max of time qry in order
‘   to enable dynamic time period run of the report.
‘
‘   NOTE: in order to run this, you’ll need a reference to the
‘   Microsoft DAO X.X Object Libray.
    
    ‘ Some variables
    Dim sDBPath As String       ‘ This is the path of the DB.
    Dim sDBName As String       ‘ This is the name of the DB
    Dim accObj As Object        ‘ This will be our Access object
    Dim dbs As DataBase         ‘ This is the database object we’ll use (requires the reference)
    Dim qryDef As QueryDef      ‘ This is the query definition we’ll use (requires the reference)
    Dim sSQL As String          ‘ This is our SQL string.
    Dim sQueryName As String    ‘ This is our query name.
    
    ‘ We’ll start out by setting the string variables.
    
    ‘   The DB path and name are acutally global constants I use elsewhere,
    ‘   so I don’t set them specifically here.
    sDBPath = msDATA_FILE_PATH
    sDBName = msDATABASE_NAME
    ‘   This is the name of the query we’ll be changing.
    sQueryName = “qryTimeMaxes”
    ‘   This is out SQL statement: the dynamic part that will be fed
    ‘   from a user form is the Week field.
    sSQL = “SELECT Max(MonRptData.Year) AS MaxOfYear, Max(MonRptData.[Fiscal Season]) AS [MaxOfFiscal Season], Max(MonRptData.[Fiscal Quarter]) AS [MaxOfFiscal Quarter], Max(MonRptData.Month) AS MaxOfMonth, MonRptData.Week ” _
            & “FROM MonRptData ” _
            & “GROUP BY MonRptData.Week ” _
            & “HAVING (((MonRptData.Week)=” & sWeek & “));”
    
    ‘ Now we’ll play with our objects (nothing dirty!)
    
    ‘   First we set the access object. The access object isn’t really an
    ‘   access object at all, just a plain old ordinary object that we make
    ‘   into an access object using the “Access.Application” string.
    Set accObj = CreateObject(“Access.Application”)

    ‘   Then we open the object, using an access object model method,
    ‘   OpenCurrentDatabase, the the DB set by our path and DB name strings.
    accObj.OpenCurrentDatabase sDBPath & sDBName, False
    
    ‘   Next we’ll set the database variable to the database we’ve just opened.
    ‘   Note that, because we didn’t make our DB visible when we opened it, you can’t
    ‘   see it, but it’s open nevertheless.
    Set dbs = accObj.CurrentDb
    
    ‘   Now we’ll get into the query itself.
    ‘   First we delete the old one…
    dbs.QueryDefs.Delete sQueryName
    ‘   … then we recreate it with our new SQL string
    Set qryDef = dbs.CreateQueryDef(sQueryName, sSQL)

    ‘ That’s it. The only left if to clean up. We’ll close the database…
    dbs.Close
    ‘ …then destroy our objects to free up all the juicy memory they take up.
    Set qryDef = Nothing
    Set dbs = Nothing
    Set accObj = Nothing
    
End Sub

You can see there’s no error handling here, and that’s a flaw, but an easy one to fix.