Problem running an Action Query with VBA

[Guest]

I'm having a problem. I have a set of Append Queries that I need to run, or
initiate through VBA. This will be attached to the Click event on a command
button on my form.

I tried adding DoCmd.RunSQL but the SQL text contains quotes. "my criteria
here". The sqlText As String requires "beginning and ending quotes" and my
"criteria quotes" generate a compile error.

Then I tried DoCmd.RunMacro. I set up a Macro and the RunSQL Action but
the SQL Statement is longer than the Macro will allow.

The two ways I know of to run my Action query are not working for different
reasons.

I wasn't sure if using OpenQuery in a Macro would work? Is there another
way to run my Action Query with VBA? Thanks.

 

[Sylvain Lafontaine]

Use RunSQL but double any embedded quotes:

sql = Replace (sql, """", """""")

 

[Tony Toews [MVP]]

I tried adding DoCmd.RunSQL but the SQL text contains quotes.

Docmd.runsql won't tell you about errors. Thus, if DAO, to use
Currentdb.Execute strSQL,dbfailonerror command instead of
docmd.runsql. For ADO use CurrentProject.Connection.Execute
strCommand, lngRecordsAffected, adCmdText. If there are any errors
you will be notified in your error handling routine.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[John W. Vinson]

I'm having a problem. I have a set of Append Queries that I need to run, or
initiate through VBA. This will be attached to the Click event on a command
button on my form.

I tried adding DoCmd.RunSQL but the SQL text contains quotes. "my criteria
here". The sqlText As String requires "beginning and ending quotes" and my
"criteria quotes" generate a compile error.

Then I tried DoCmd.RunMacro. I set up a Macro and the RunSQL Action but
the SQL Statement is longer than the Macro will allow.

The two ways I know of to run my Action query are not working for different
reasons.

I wasn't sure if using OpenQuery in a Macro would work? Is there another
way to run my Action Query with VBA? Thanks.

Either replace the " characters delimiting the criteria with '; or, if the
text being searched for might contain an apostrophe, replace the " with two
consecutive doublequotes.

E.g.

sqlText = "SELECT this, that FROM here WHERE LastName = ""O'Brien"";"

John W. Vinson [MVP]

 

[Guest]

Thanks everyone!!

The double quotes is perfect and the apostrphophe works as well. I've had
this problem before and always had to run a macro to get around it. Now I
know.

I went with DAO, "CurrentDb.Execute strSQL, dbFailOnError". It took a little
while to get through some of the errors I was getting but I read through some
Help files and its working great.

Thanks!!!!!