Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 1st, 2005, 05:05 AM
Authorized User
 
Join Date: Jan 2005
Location: , , Uruguay.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Invalid SQL statement expected; ‘DELETE’,’INSERT’,

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
 
Old June 1st, 2005, 12:12 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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...)
 
Old June 29th, 2005, 02:52 PM
YP YP is offline
Registered User
 
Join Date: Jun 2005
Location: , , Ukraine.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old May 18th, 2007, 10:06 AM
Registered User
 
Join Date: May 2007
Location: Akron, OH, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...
 
Old May 21st, 2007, 08:04 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Error: Expected end of statement crabjoe Classic ASP Basics 3 December 4th, 2007 12:52 AM
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
Expected statement jonnyfive Classic ASP Basics 3 January 4th, 2006 07:04 PM
expected end of statement Adam H-W Classic ASP Databases 3 January 4th, 2005 05:37 AM
expected end of statement....help.... chaoz83 Classic ASP Databases 3 September 23rd, 2003 01:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.