The logic of what you are doing is, to say the least, "unusual." If you
explain what you have and what you are trying to accomplish, there's a
really good chance that someone will be able to suggest a simpler approach
than creating so many (temporary?) tables. At best, if you do this, you
will have to Compact and Repair frequently to prevent database bloat.
However, to answer your question: You can construct the entire SQL
statement, and probably will only need a couple of concatenations. The
"source" of a Db.Execute is a string. Here's a quick example, using a
delete query because it was simpler... you code your own loop, table read,
etc. use your own text as basis for the SQL, and retrieve your table name
from your "table of tablenames." For the query you describe, you won't need
a WHERE clause. "tblDelMe" is a little test table with a text field for
primary key.
Function ExecuteQuery(pstrK As String) As Boolean
On Error GoTo Proc_Error
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strQ As String
Dim strT As String
strT = "tblDelMe"
strQ = "DELETE * FROM " & strT & " WHERE [ID] = """ & pstrK & """"
Set db = CurrentDb()
db.Execute (strQ)
ExecuteQuery = True
Proc_Exit:
On Error GoTo 0
Exit Function
Proc_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ExecuteQuery of Module basDAOMiscCode"
ExecuteQuery = False
Resume Proc_Exit
End Function
Larry Linson
Microsoft Access MVP
<hm*****@hartford.eduwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
Hey there folks,
I was wondering if there was a way to store a list of variables in a
table and then call them one at a time in some loop method.
Here's what I've got:
A table "Tab Names" with 50 values all running down the first column
named "Tabs"
I then have a snippet of VBA that executes a SQL statement to make a
table.
- The SQL has a variable in it "tName"
- The SQL is run through the execute command not DoCmd.RunSQL
Is there a way to look to the table, grab the first variable, run the
SQL with that variable, and then loop through the next 49 variables
until the last variable in the table.
Thanks in advance for you expertice,