Invalid SQL Statement

[morechocolate]

I am trying to run the queries that I have set up in Access, but I get the following error:

Invalid SQL Statement; expected DELETE,INSERT,PROCEDURE,SELECT or UPDATE.

I tried two different methods of code but received the same results for both.

1.

Dim cnAccessTables As New ADODB.Connection
Dim cmAccessTables As New ADODB.Command
Dim rsAccessTables As New ADODB.Recordset

dim QueryName as String

'connect to Jet database(Microsofe Access)
cnAccessTables.Provider = "Microsoft.Jet.OLEDB.3.51;"

'set the connection string to Secured Borrowing database
cnAccessTables.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & glo_strDatabase

'establish connection
cnAccessTables.Open

Set cmAccessTables.ActiveConnection = cnAccessTables
cmAccessTables.CommandType = adCmdStoredProc

QueryName = "qryMakeRollTrans"
cmAccessTables.CommandText = QueryName

'run the query that is in Access
set rsAccessTables = cmAccessTables.Execute

Set cmAccessTables = Nothing

rsAccessTables.Close
Set rsAccessTables = Nothing

cnAccessTables.Close
set cnAccessTables = Nothing

2.

Dim cnAccessTables As New ADODB.Connection
Dim rsAccessTables As New ADODB.Recordset

dim QueryName as String

'connect to Jet database(Microsofe Access)
cnAccessTables.Provider = "Microsoft.Jet.OLEDB.3.51;"

'set the connection string to Secured Borrowing database
cnAccessTables.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & glo_strDatabase

'establish connection
cnAccessTables.Open

rsAccessTables.open QueryName, cnAccessTables

rsAccessTables.Close
Set rsAccessTables = Nothing

cnAccessTables.Close
set cnAccessTables = Nothing

In the first instance the error occurs at set rsAccessTables = cmAccessTables.Execute. In the second instance it occurs at rsAccessTables.open QueryName, cnAccessTables.

The Access query is an Insert Into. I also tried the same query as an Insert, but still got the same errors.

Thanks for your help.

mc

 

[MatsHulten]

I may be wrong about this, but I don't think that the Jet engine supports stored procedures.

Suggest you try to store the command text in a string variable, and then open the recordset using that instead.

Good Luck!
-Mats Hulten

 

[morechocolate]

Thanks Mats.

I was afraid of that. I searched other user groups last night and found a note from someone that said Jet 3.51 does not support stored queries.

Thanks for your response.

Pam