Invalid SQL Statement - Why????

[morechocolate]

I am trying to execute Access action queries (Append queries and Make Queries) using ADO, 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

 

[JJR]

Perhaps if you submit the sql statement we could have a look at it. And also NEVER dim as new. It creates overhead because everytime you call a property or method of your object it first checks if it is allready created.

Use:

Dim cnAccessTables As ADODB.Connection
Set cnAccessTables = new ADODB.Connection

Jordi Reineman

 

[morechocolate]

Thanks for the tip Jordi.

Here is an example of one of the SQL statements created by Access. BTW Access runs this query successfully

INSERT INTO RollTrans ( CURRHOLD_NO, ACCOUNT, PORTFOLIO, TRAN_TYPE, SETL_Y_N, TRADE_DATE, QUANTITY, LINKED_TRADE_NO, TRADER, USER_DESC4 )
SELECT Transac.CURRHOLD_NO, Transac.ACCOUNT, Transac.PORTFOLIO, Transac.TRAN_TYPE, Transac.SETL_Y_N, Transac.TRADE_DATE, Transac.QUANTITY, Transac.LINKED_TRADE_NO, Transac.TRADER, TranInfo.USER_DESC4
FROM Transac INNER JOIN TranInfo ON (Transac.TRADE_NO = TranInfo.TRADE_NO) AND (Transac.CURRHOLD_NO = TranInfo.CURRHOLD_NO)
WHERE (((Transac.TRAN_TYPE)=&quot;SELL&quot AND ((Transac.SETL_Y_N)=&quot;Y&quot AND ((Transac.LINKED_TRADE_NO)<>0) AND ((Transac.TRADER)=&quot;SELL DR&quot) OR (((Transac.TRAN_TYPE)=&quot;BUY&quot AND ((Transac.SETL_Y_N)=&quot;Y&quot AND ((Transac.LINKED_TRADE_NO)<>0) AND ((Transac.TRADER)=&quot;BUY DR&quot; Or (Transac.TRADER) Is Null));

 

[morechocolate]

This is what I assigned to a variable in the code and I get a SYNTAX error (missing operator) --ugly

&quot;INSERT INTO RollTrans ( CURRHOLD_NO, ACCOUNT, PORTFOLIO, TRAN_TYPE, SETL_Y_N, TRADE_DATE, QUANTITY, LINKED_TRADE_NO, TRADER, USER_DESC4 )&quot; & _
&quot;SELECT Transac.CURRHOLD_NO, Transac.ACCOUNT, Transac.PORTFOLIO, Transac.TRAN_TYPE, Transac.SETL_Y_N, Transac.TRADE_DATE, Transac.QUANTITY, Transac.LINKED_TRADE_NO, Transac.TRADER, TranInfo.USER_DESC4&quot; & _
&quot;FROM Transac INNER JOIN TranInfo ON (Transac.TRADE_NO = TranInfo.TRADE_NO) AND (Transac.CURRHOLD_NO = TranInfo.CURRHOLD_NO)&quot; & _
&quot;WHERE (((Transac.TRAN_TYPE)=('SELL')AND ((Transac.SETL_Y_N)='Y') AND ((Transac.LINKED_TRADE_NO)<>0) AND ((Transac.TRADER)= 'SELL DR')) OR (((Transac.TRAN_TYPE)= 'BUY') AND ((Transac.SETL_Y_N)= 'Y') AND ((Transac.LINKED_TRADE_NO)<>0) AND ((Transac.TRADER)= 'BUY DR' Or (Transac.TRADER) Is Null))&quot;

 

[morechocolate]

I am getting closer, I just need another set of eyes. My new error is missing ,) ], or item in query expression. To get to this I had to add a space before the SELECT, FROM and WHERE.

&quot;INSERT INTO RollTrans ( CURRHOLD_NO, ACCOUNT, PORTFOLIO, TRAN_TYPE, SETL_Y_N, TRADE_DATE, QUANTITY, LINKED_TRADE_NO, TRADER, USER_DESC4 )&quot; & _
&quot; SELECT Transac.CURRHOLD_NO, Transac.ACCOUNT, Transac.PORTFOLIO, Transac.TRAN_TYPE, Transac.SETL_Y_N, Transac.TRADE_DATE, Transac.QUANTITY, Transac.LINKED_TRADE_NO, Transac.TRADER, TranInfo.USER_DESC4&quot; & _
&quot; FROM Transac INNER JOIN TranInfo ON (Transac.TRADE_NO = TranInfo.TRADE_NO) AND (Transac.CURRHOLD_NO = TranInfo.CURRHOLD_NO)&quot; & _
&quot; WHERE (((Transac.TRAN_TYPE)=('SELL')AND ((Transac.SETL_Y_N)='Y') AND ((Transac.LINKED_TRADE_NO)<>0) AND ((Transac.TRADER)= 'SELL DR')) OR (((Transac.TRAN_TYPE)= 'BUY') AND ((Transac.SETL_Y_N)= 'Y') AND ((Transac.LINKED_TRADE_NO)<>0) AND ((Transac.TRADER)= 'BUY DR' Or (Transac.TRADER) Is Null))&quot;

 

[morechocolate]

I found it!!! I had an extra (.