Call a saved query from VBA module

[John T Ingato]

How do I call a saved append query from VBA module. I tried using
docmd.runsql() and hand typing in the code but I am getting error. The saved
query in the database (qryPreImport)works fine.

Here is the code in the module with the SQL hard coded, which fails. Error
states: "Query input must contain one table or query". I actually cut and
pasted this code directly from the query in SQL view. As I said, the saved
query works fine.

tblHDStoreList is a local table in the database StoreNumber is a fiels in
this table
PreImport852Report is a linked table to an excel spreadsheet on my computer.

DoCmd.RunSQL ( _
"INSERT INTO tblHDStoreList (StoreNumber )" & _
"SELECT DISTINCT PreImport852Report.StoreNumber AS Expr1" & _
"FROM PreImport852Report LEFT JOIN tblHDStoreList ON
PreImport852Report.StoreNumber = tblHDStoreList.StoreNumber" & _
"WHERE (((tblHDStoreList.StoreNumber) Is Null));")

 

[Allen Browne]

You can:
DoCmd.RunSql "qryPreImport"
or if you care about the results:
dbEngine(0)(0).Execute "qryPreImport", dbFailOnError

Executing the string works too. You may be running into problems because
spaces are missing between the words at the line endings, e.g. Access will
choke on:
... AS Expr1FROM ...

It might help to create a string variable, and print it to the Debug window
while you are testing:
Dim strSql As String
strSql = "INSERT INTO ...
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError

Then when it fails, you can open the Immediate Window (Ctrl+G) and see
what's wrong with the query string.

Other suggestions:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[John T Ingato]

Thank you allen. The missing spaces were the problem.

I have programmed in excel for a few years, but access is new to me. That
was a newbee mistake.

I noticed you suggested dbEngine(0)(0).Execute "qryPreImport",
dbFailOnError and DoCmd.RunSql.
How do they differ?
What about DoCmd.OpenQuery?

 

[John T Ingato]

I am now just reading your suggested reading, which is answering my previous
questions... Thanks again.