Invalid SQL statement expected; âDELETEâ,âINSERTâ,

knowledge76 · June 1st, 2005, 05:05 AM

I am having a problem from the last couple of days: I am getting the error"Invalid SQL statement expected; âDELETEâ,âINSERTâ,'UPDATE' or SELECT". I have tried to solve it but no success. I need your help

Code:

 Dim conn1 As ADODB.Connection, rs As ADODB.Recordset
  
  Set conn1 = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  
  
rs.Open "Customers", conn1, adOpenDynamic, adCmdTable
    rs.AddNew
 rs("Name") = Me!Name  
  rs("Id") = Me!Id
  rs.Update     
  rs.Close        
  DoCmd.Close
  Forms![MainForm].Form!Uform1.Requery


End Sub

BrianWren · June 1st, 2005, 12:12 PM

On which line does it fail?

You could switch to:

Code:

    rs.Open "SELECT NAME, ID FROM Customers", conn1, adOpenDynamic, adCmdText

(I know this doesn't actually answer the question you asked...)

Alternately, you could:

Code:

    Dim conn1 As ADODB.Connection

    Set conn1 = CurrentProject.Connection

    conn1.Execute "INSERT INTO " & _
                  "CUSTOMERS (       ID,             NAME ) " & _
                  "VALUES    (" & Me!ID & ", '" & Me!Name & "')", , adCmdText

    Forms![MainForm].Form!Uform1.Requery

End Sub

Again, this doesn't answer the actual question you asked, but it should get you rolling again.
It takes time to open and to close a recordset. Since you are only adding one record, using INSERT INTO is better. (IMHO...)

YP · June 29th, 2005, 02:52 PM

Last time I had same error when run query, written by sintax and for external SQL-Server, to MS Jet. For example, "Customers" is the "Query to [External] Server" (somthing like "Set @CastCount:=(Select Count(Id) from Contagents)"), but You try to run it as usual access query.

MarkStich · May 18th, 2007, 10:06 AM

I had this same error when I was trying to query a recordset from an Excel file and I had the "Source" or tab name incorrect...

mmcdonal · May 21st, 2007, 08:04 AM

This is the line that will cause the error:

rs.Open "Customers", conn1, adOpenDynamic, adCmdTable

What you should do is:

Dim sSQL As String

sSQL = "SELECT * FROM Customers"

rs.Open sSQL, conn1, adOpenDynamic, adLockOptimistic

If you are updating records, this will work fine. It is expecting a SELECT statement in your case. The "Customers" that you are using is not a fully formed SQL string. You need to tell ADO what you are doing. In your case, SELECT and rs.AddNew are consistent.

Did that help?

mmcdonal