VBA -- SQL

J

Jeanne

I'm trying to execute an SQL statement and continue getting this error
Runtime error 2342 " A RunSQL statement requires an argument consisting of
an SQL statement.
I've simplified the SQL. I'm running other SQL successfully. Can't seem to
run a Select statement.

Here's my statement
sSQL = "SELECT CU_Balance.* FROM CU_Balance"
DoCmd.RunSQL sSQL

Any ideas?
 
S

Stefan Hoffmann

hi Jeanne,
I'm trying to execute an SQL statement and continue getting this error
Runtime error 2342 " A RunSQL statement requires an argument consisting of
an SQL statement.
I've simplified the SQL. I'm running other SQL successfully. Can't seem to
run a Select statement.

Here's my statement
sSQL = "SELECT CU_Balance.* FROM CU_Balance"
DoCmd.RunSQL sSQL

Any ideas?
Executing a SELECT statement with DoCmd.RunSQL makes no sense. It is for
executing action queries (DELETE or UPDATE).

What are you trying to achieve?


mfG
--> stefan <--
 
D

Dorian

Look up RunSQL in Access help, it will give you the answer.
RunSQL is for 'action' queries only.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
T

Tony Toews [MVP]

Stefan Hoffmann said:
Executing a SELECT statement with DoCmd.RunSQL makes no sense. It is for
executing action queries (DELETE or UPDATE).

Or Insert queries.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
D

Daniel Pineault

Read the article found at

http://www.databasejournal.com/feat...5836/Executing-SQL-Statements-in-VBA-Code.htm

It will clarify the various methods available to you to run/execute SQL
within VBA and illustrating the pro and cons of each. Then you will be in a
position to determine which method best suits your needs.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
T

Tony Toews [MVP]

Daniel Pineault said:
Read the article found at

http://www.databasejournal.com/feat...5836/Executing-SQL-Statements-in-VBA-Code.htm

It will clarify the various methods available to you to run/execute SQL
within VBA and illustrating the pro and cons of each. Then you will be in a
position to determine which method best suits your needs.

He misses explaining all the problems with DoCmd.SetWarnings which I
totally avoid using. The currentdb or connection execute method is
much better.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
T

Tony Toews [MVP]

Dorian said:
Look up RunSQL in Access help, it will give you the answer.
RunSQL is for 'action' queries only.

The problem with DoCmd.RunSQL is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

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
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
D

David W. Fenton

The problem with DoCmd.RunSQL is that it ignores any errors.
Either of the following will display any error messages received
by the query. If using DAO, use Currentdb.Execute
strSQL,dbfailonerror.. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText You can then remove the docmd.setwarnings lines.

I have been using a replacement for RunSQL in my most active current
project. I call the function SQLRun and you can search and replace
to drop it in where using DoCmd.RunSQL and it will work
transparently. You might have unnecessary SetWarnings statements
then, but you can take those out as well. The code is after my
signature. You may or may not want to uncomment the transactions --
I found that it was causing some problems, so I took it out.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function SQLRun(strSQL As String, Optional db As Database, _
Optional lngRecordsAffected As Long) As Long
On Error GoTo errHandler

If db Is Nothing Then Set db = CurrentDb
' DBEngine.Workspaces(0).BeginTrans
db.Execute strSQL, dbFailOnError
lngRecordsAffected = db.RecordsAffected
' DBEngine.Workspaces(0).CommitTrans

exitRoutine:
SQLRun = lngRecordsAffected
' Debug.Print strSQL
Exit Function

errHandler:
MsgBox "There was an error executing your SQL string: " _
& vbCrLf & vbCrLf & Err.Number & ": " & Err.Description, _
vbExclamation, "Error in SQLRun()"
' Debug.Print "SQL Error: " & strSQL
' DBEngine.Workspaces(0).Rollback
Resume exitRoutine
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top