Acc97 SQL statements fail

[bradmaunsell]

I have a problem running SQL statements - read recordset works but cannot run INSERT or DELETE.

Windows 2000 SP3
Access 97
Active X Data Objects 2.5
ADo Ext. 25

THIS SUB WORKS OK
Private Sub newRecordset()
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection
cn.ConnectionString = "DSN=EOsysTables;UID=Admin;PWD='';"
cn.Open

strSQL = " SELECT * FROM tblStaff"
Set rs = cn.Execute(strSQL)

Do Until rs.EOF
Debug.Print rs!FirstName & " " & rs!LastName
rs.MoveNext
Loop

MsgBox "Done"

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

THIS SUB RESULTS IN ERROR
Private Sub cmdDelete_Click()

Dim strSQL As String

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.ConnectionString = "DSN=EOsysTables;UID=Admin;PWD='';"
cn.Open

strSQL = "DELETE * FROM tblAppQuotes"
cn.Execute ("strSQL&quot

End Sub

Run-Time Error '-2147217900(980040e14)':

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE",'INSERT','PROCEDURE','SELECT', or 'UPDATE'

I get this same error with an UPDATE routine. I can go to the Access query tab and create a DELETE or an UPDATE query that work OK. I have cut and pasted those SQL statements and still cannot get them run with vba.

Any help will be greatly appreciated.

Brad Maunsell
South Burlington, VT

 

[VBslammer]

Remove the quotes from your variable:

cn.Execute ("strSQL")

Should be:

cn.Execute (strSQL)

VBSlammer

Unemployed in Houston, Texas

 

[bradmaunsell]

OH!!! How stupid of me!!!! I know better than that!!!

I looks like I should have stepped away for bid.

Thanks so much.

Brad