If you read the “About Me” page, then you’ll see that the first thing I say is that I’m an Excel guru. And I am. It’s what I do for a living where I work (among other things).
But till now, I haven’t logged any Excel or VBA related posts. Not a one.
Let’s change that!
Fair warning, this one is going to be geeky. Now’s your chance to bail.
Still there?
Okay, let’s go then!
I do a lot of “Excel as a front end” work, where Excel is the user interface, and the data gets sent back to a database, usually Access, because it so prevalent.
I ran into a situation where I needed to pass a parameter to a query in Access because that query was a perquisite of my running another query that had the first one as a dependency.
Most of the time, when I’m connecting to Access from Excel, I use ADO. But in this case, I used DAO because it was simpler. Below is the resulting routine, in which I pass a string variable to the query prior to using that query elsewhere. Fully commented for your reading pleasure.
Sub Pass_Parameter_To_Query(sWeek As String)
' This passes a parameter to the max of time qry in order to enable dynamic time period run of the report.
'
' Inbound: sWeek = a string variable containing the week in question, in whatever format is required by the query.
'
' NOTE: in order to run this, you'll need a reference to the Microsoft DAO X.X Object Library.
' Variables declarations.
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 actually global constants I use elsewhere, so I don't set them specifically here.
sDBPath = msDATABASE_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
If you followed that all the way through, then you’ll probably realize that I didn’t technically pass the parameter to the query in question. What I actually did was delete the query, then recreate it with the new parameter. This gives you the same result as setting the query parameter if you were actually in the Access database.
That’s it. Hope somebody out there finds it useful.
Scott C Lyerly 
Dang. You are a geek!
Oh, yeah I am. This one barely scratches the surface!
While I was a programmer, I always wished that my predecessors documented their code as well as you do. I always did too and it was appreciated.
Fran: While I try to document my code as cleanly and thoroughly as possible, sometimes the programming has to be fast rather than readable. So I have my moments where I sacrifice comments for speed. I’m trying to avoid that these days, sort of like a New Year’s resolution.