[Help] execute SQL scripts from VB

[Fjordur]

Hi,
this is Access 2000. I have a set of append queries, which I need to run all
together each time, with a macro that goes
openQuery A
openQuery B
etc...
Not too nice, and I would like more flexibility in modifying the list of
queries or their contents. I thought of putting the queries source in a
table and run them from the table. I found an article that shows how to do
just that at http://www.mvps.org/access/queries/qry0014.htm.

I modified the code from the site slightly to adapt it to my needs, and I
end up with:
Table BatchSQLScripts
field SQLString (memo) : the actual SQL source to be run
field ScriptNumber (single) : the number of the script
Module function:
Set lodb = CurrentDb
Set loSQLRS = lodb.OpenRecordset("Select * from BatchSQLScripts where
SQLString is not null order by ScriptNumber", dbOpenSnapshot)
With loSQLRS
.MoveFirst
While Not .BOF And Not .EOF
Debug.Print !ScriptNumber 'shows OK
strSQL = adhHandleQuotes(!SQLString) 'replace quotes with
Chr(34), didn't touch that function, anyway most scripts don't have quotes
Debug.Print !SQLString 'shows OK
lodb.Execute strSQL, dbFailOnError '** this generates an error
3078 **
.MoveNext
Wend
End With
(...)

What happens is, the debug print shows the scripts OK, but the Execute
method generates an error code 3078 - The Microsoft Jet database engine
cannot find the input table or query '(here goes the entire script)'. Make
sure it exists and that its name is spelled correctly
I don't even understand what "cannot find the query "blah blah" means.
I've tried writing a string directly in the lodb.Execute line, I've spent
hours on the net trying to get an answer, nothing.
Can anyone help?

 

[Fjordur]

Hi,
this is Access 2000. I have a set of append queries, which I need to run all
together each time, with a macro that goes
openQuery A
openQuery B
etc...
Not too nice, and I would like more flexibility in modifying the list of
queries or their contents. I thought of putting the queries source in a
table and run them from the table. I found an article that shows how to do
just that at http://www.mvps.org/access/queries/qry0014.htm.

I modified the code from the site slightly to adapt it to my needs, and I
end up with:
Table BatchSQLScripts
field SQLString (memo) : the actual SQL source to be run
field ScriptNumber (single) : the number of the script
Module function:
Set lodb = CurrentDb
Set loSQLRS = lodb.OpenRecordset("Select * from BatchSQLScripts where
SQLString is not null order by ScriptNumber", dbOpenSnapshot)
With loSQLRS
.MoveFirst
While Not .BOF And Not .EOF
Debug.Print !ScriptNumber 'shows OK
strSQL = adhHandleQuotes(!SQLString) 'replace quotes with
Chr(34), didn't touch that function, anyway most scripts don't have quotes
Debug.Print !SQLString 'shows OK
lodb.Execute strSQL, dbFailOnError '** this generates an error
3078 **
.MoveNext
Wend
End With
(...)

What happens is, the debug print shows the scripts OK, but the Execute
method generates an error code 3078 - The Microsoft Jet database engine
cannot find the input table or query '(here goes the entire script)'. Make
sure it exists and that its name is spelled correctly
I don't even understand what "cannot find the query "blah blah" means.
I've tried writing a string directly in the lodb.Execute line, I've spent
hours on the net trying to get an answer, nothing.

I just found the problem is linked with the quotes: the
adhHandleQuotes(!SQLString) function (from the site above) returns the
string with quotes around it. If I remove these, and if the original query
code has no quote in it, the Execute method runs fine.
Now I get syntax errors for query code that contains quotes. So I suppress
the replacement of quotes and hand the string !SQLString straight from the
database to Execute, and don't get any errors.

Out of curiosity, why does the code from the site above bother to replace
all quotes by QUOTE & " & Chr$(34) & " & QUOTE before handing the string to
Execute?