How to execute query in a for loop

[Judy Ward]

Currently I have 14 individual queries in my Access database that differ only
by the criteria for one of the fields (the IR field). I have code that sends
each query to an Excel file. When a change is requested to the query (add or
delete a field) I have to edit all 14 queries.

What I would like to do is have an array of the 14 different IR's and
execute this query in a loop. I made sure the sql works when I run it for
one index of the array, but I really don't know enough about queries to get
this working in a loop. This is what I have so far (that is not working):

' Not showing the declaration of the array, etc.
Dim qry As New DAO.QueryDef
For i = 1 To 14
strSQL = "SELECT <lots of fields> WHERE myTable.IR Like '*" & IR_List(i) &
"*';"
qry.sql = strSQL
qry.Name = IR_List(i)
On Error Resume Next
DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.Name
On Error GoTo 0
DAO.Workspaces(0).Databases(0).QueryDefs.Append qry
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qry.Name, filenm
Next i

It works for the first index of the array and then stops with this error:
Run-time error '3219': Invalid operation
and hilights this line in my code:
DAO.Workspaces(0).Databases(0).QueryDefs.Append qry

I would appreciate help figuring out why this is stopping here.
Thank you,
Judy

 

[Ofer]

In the append Command, try and chamge to the qry, to qry.name

 

[Judy Ward]

That results in a Compile Error: Type mismatch

In the append Command, try and chamge to the qry, to qry.name

 

[Ofer]

Try this
1. Create a query and save it under the name GlobalQuery, save it empty, the
code will insert the sql into it
2.

Try this code

For i = 1 To 14
strSQL = "SELECT <lots of fields> WHERE myTable.IR Like '*" & IR_List(i) &
"*';"
currentdb.QueryDefs("GlobalQuery").SQL=strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, GlobalQuery,
filenm
Next i

 

['69 Camaro]

Hi, Judy.

Instead of messing around with QueryDef Objects, try to use SQL for the
export:

sPath = "[Text;HDR=YES;DATABASE=C:\Data\]."

For i= 1 To 14
'sFile(i) = "MyExport.csv" ' <--- Sample file name.
' Must change for each file in
loop.
strSQL = "SELECT <lots of fields> INTO " & sPath & sFile(i) & _
" FROM myTable " & _
"WHERE myTable.IR Like '*" & IR_List(i) & "*';"

CurrentDb().Execute strSQL, dbFailOnError
Next idx

.. . . where C:\Data is the path, sPath is a String variable containing the
connection info and destination path, and sFile( ) is a String array of file
names.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Judy Ward]

Thank you for trying, but this results in:
Run-time error '3265': Item not found in this collection.
on this line:
CurrentDb.QueryDefs("GlobalQuery").sql = strSQL

Unfortunately, I'm not experienced enough with this to know why it's not
working.
Thank you,
Judy

 

[Ofer]

Have you created a query called GlobalQuery?