Mass query execution

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I have a database that links to mulitple Named Ranges within a XLS
spreadsheet. Each Named Range is linked as a seperate table, over 200 tables
total. These tables/named ranges are populated via Update Queries, over 300
total. My question/problem is, I would like to run all of the Update Queries
at once without have to designate each one seperatly within my code. Is this
possible and if so how? The naming conventions of these queries are
similiar:
Example:
qry_ATL_CF_APR_XLS

Within the name of the queries the only differences are:
ATL= Division
CF=Product
APR=Month

Thanks!

Anthony
(e-mail address removed)
 
If you set a reference to DAO, you can loop through the QueryDefs
collection, checking the name of each query to see whether it's one you want
to run.

Dim dbCurr As DAO.Database
Dim qryCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
For Each qryCurr In dbCurr.QueryDefs
With qryCurr
If Right$(.Name, 4) = "_XLS" Then
.Execute, dbFailOnError
End If
End With
Next qryCurr

Set dbCurr = Nothing
 
Doug,
I've tried to use the code that you've cited below, but I get the
following:

Wrong number of arguments or invalid property assignment

Any thoughts?

Anthony
 
Sorry: my fault.

Remove the comma after the Execute instruction:

.Execute dbFailOnError

(I should have warned you it was untested air-code!)
 
This will set a progress bar on the status bar. (and this time I'll remember
to warn you that it's untested!)

Dim dbCurr As DAO.Database
Dim qryCurr As DAO.QueryDef
Dim lngCount As Long

Set dbCurr = CurrentDb()
SysCmd acSysCmdInitMeter, "Working on update queries",
dbCurr.QueryDefs.Count
For Each qryCurr In dbCurr.QueryDefs
lngCount = lngCount + 1
SysCmd acSysCmdUpdateMeter, lngCount
With qryCurr
If Right$(.Name, 4) = "_XLS" Then
.Execute dbFailOnError
End If
End With
Next qryCurr

SysCmd acSysCmdClearStatus
Set dbCurr = Nothing
 
Doug,
The code doesn't seem to like the "dbCurr.QueryDefs.Count". I receive
the "invalid use of property error" for the .Count.

Any ideas?

Anthony
 
Is

SysCmd acSysCmdInitMeter, "Working on update queries",
dbCurr.QueryDefs.Count

all on one line?
 
Back
Top