ERROR 2342 DoCmd.RunSQL (1 Viewer)

doco · Feb 25, 2008

Example
The following example updates the Employees table, changing each sales manager's title to Regional Sales Manager:

Public Sub DoSQL()

Dim SQL As String

SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager'"

DoCmd.RunSQL SQL

End Sub

Yet no manner of SQL statement is accepted without error. The following example is an oversimplification to make a point.

Code:

    DoCmd.RunSQL "Select * from dbo_property"

'    or even populating a variable
    SQL = "Select * from dbo_property"
    DoCmd.RunSQL SQL   '    throws same error

Throws Run Time error '2342' : "A RunSQL action requires an argument consisting of an SQL statement"

Even copy and paste SQL directly from Access QBE SQL editor of a query that does indeed work without error, throws the same error.

Any ideas?

TIA

ajetrumpet · Feb 25, 2008

doco said:
Any ideas?

Yes,

you are violating a very simple rule in Visual Basic:

Action commands in Visual Basic can only carry out actions. This includes queries, or SQL statements that represent queries. The reason you are getting the error is probably because the SQL statement you are dealing with does not constitute an action.

Action queries are: UPDATE, DELETE, APPEND, and MAKE TABLE. These are the only ones that can be used in Visual Basic with the Docmd.RunSQL and CurrentDB.Execute commands.

Moniker · Feb 25, 2008

And therefore, the solution is:

DoCmd.OpenQuery "Select * from dbo_property"

That will, by the way, open the query in datasheet view. If you're trying to open it as a recordset, you'll need to use DAO or ADO.

doco · Feb 25, 2008

Too bad then, the error report doesn't give the error instead of an erroneous error message. Because the fact of the case was the argument DID constist "of an SQL statement." Why not say 'does not consist of and ACTION SQL statement'? The confusion may have been avoided. ARGH!

Thanks much...

BTW.

QueryName Required Variant. A string expression that's the valid name of a query in the current database.

Apparently, DAO/ADO or that whole other lengthy process of creating Querydefs is the only way to create a query on the fly.

gemma-the-husky · Feb 25, 2008

moniker has explained how easily you can use the docmd.openquery statement to open a query directly, and explained that docmd.runsql doesnt quite do the same thing.

you dont need dao or ado to do this stuff

any language has syntax to learn - we just have to take the trouble to learn and understand it.

eg, with an inout box, you cant hide the characters entered with password mask. thats irritating, as it would be useful. It just means if we need to get a password in a popup box, we need to design a module/function to do it ourselves.

Moniker · Feb 25, 2008

Straight out of Access Help for RunSQL:

A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.

That should sound pretty familiar by now.

doco · Feb 25, 2008

It's not a problem 'take the trouble to learn and understand". It is a problem when learning involves misleading syntax and even more misleading error messages.

I learned the hard way that RunSQL does not mean run or execute just any old SQL statement. I learned you cannot run a SELECT statement but must be an action query only. Interestingly, if you do something odd with DoCmd.RunSQL such as

Code:

'    place statement in double and single quotes
    Public Sub myFunc()
        DoCmd.RunSQL "'SELECT * FROM Mailing_List'"
    End Sub

You get and even stranger

Run-time error '3129':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

(emphasis mine)
Now that's a rather odd thing to state given that apparently, SELECT queries are taboo. That error led to this discussion. Because I was entering a SELECT SQL statement in the argument: even that goofy one with the double and single quotes IS a select query.

At any rate, I do know ADO/DAO and use it often. Just thought I would venture out and learn a few new tricks with DoCmd and learn I did - silly me.

Thanks guys.

doco · Feb 25, 2008

Yes, Moniker and to my embarassment somewhat. I did in fact look to help at the first. Unfortuantely, I looked at the example only and saw that a SQL was being used and looked no further. What made me a little nuts ( a little more nuts

) is the error was leading me to believe just any old SQL statement was needed in the argument. Anyway, no amount of explaining will excuse the fact that I did not read every 'jot and tittle' of the help text.

Thanks

gemma-the-husky · Feb 25, 2008

from the above posts it appears that the RUNSQL command DOES allow "select" as an option, so you can in fact run a SQL "select query".

Perhaps this just stores the result in some way, without showing you what it has found. Perhaps you then need to do something else with it, so maybe this isnt so useful on its own. Can someone else clarify what you do next after a runsql "select" statement

Moniker · Feb 25, 2008

SELECT doesn't work with RunSQL. The error message is just wrong. It's the same as when you have an object open (say, an Excel Object through code), you manually pause the code, and then restart it. You'll get the error "The remote server or host machine cannot be found." It has nothing to do with the actual error (the object is confused and needs to be reinitialized). The way around that particular error is to explicitly reference the object in your code, btw.

He just found a case of the Access errors not being as descriptive or even as accurate as they should be.

doco · Feb 25, 2008

Moniker said:
He just found a case of the Access errors not being as descriptive or even as accurate as they should be.

Its a habit I have if something is giving an error that doesn't make sense or I cannot figure out how to fix, I begin a process of purposefully doing some things I know are errant to see what else comes up trying to get 'warm and fuzzy' with the thing. In this case it only got worse.

I could CREATE TABLE then DoCmd.RunSQL INSERT ... or just do the QueryDef then DoCmd.OpenQuery, yada, yada.

A long drawn out way of discovering RunSQL does not do what the name implies...

Again thanks much

mcward316 · Feb 6, 2009

So, I have a more complex, but similar issue. The following code gives me the same error as described in this thread.

strSQL = "USE TEST " & _
"DECLARE @Pointer binary(16), @StringToAppend varchar(100) " & _
"SET @StringToAppend = '...TEST STRING...' " & _
"SELECT @Pointer = TEXTPTR(bin.BITS) " & _
"FROM CUST_ORDER_BINARY bin " & _
"WHERE bin.CUST_ORDER_ID = 1005833728 AND bin.ROWID = 1 " & _
"IF @Pointer IS NULL " & _
"UPDATE CUST_ORDER_BINARY SET BITS = @StringToAppend " & _
"WHERE bin.CUST_ORDER_ID = 1005833728 AND bin.ROWID = 1 " & _
"Else " & _
"UPDATETEXT CUST_ORDER_BINARY.BITS @Pointer NULL NULL @StringToAppend"

DoCmd.RunSQL strSQL

Honestly, I don't know where to go from here. This code was recommended to me by someone who says it works. It's just supposed to allow me to append data to a binary field from Access. If anyone knows how to make this work or has a better idea, please share it. I'm at a standstill right now. Thanks.

pbaldy · Feb 6, 2009

Welcome to the site. I'm fairly sure that's T-SQL, which would be valid in SQL Server, not Access.

mcward316 · Feb 9, 2009

Well, I need a way to write to the binary field from Access. Does anyone know how to do this?

jason97m · Apr 10, 2011

I need to correct this post, you CAN DO A SELECT with DoCMD.RUNSQL, the way to do it is by SELECTING into a new table like this:

DoCMD.RUNSQL "SELECT * INTO table2 FROM table1"....this will work correctly and not give a 2342 error. The table2 name can be arbitrary, table1 should be your source table.

ERROR 2342 DoCmd.RunSQL (1 Viewer)

doco

Power User

ajetrumpet

Banned

Moniker

VBA Pro

doco

Power User

gemma-the-husky

Super Moderator

Moniker

VBA Pro

doco

Power User

doco

Power User

gemma-the-husky

Super Moderator

Moniker

VBA Pro

doco

Power User

mcward316

New member

pbaldy

Wino Moderator

mcward316

New member

jason97m

New member

Similar threads

Users who are viewing this thread