Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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...)
Reply With Quote
  #3 (permalink)  
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.

Reply With Quote
  #4 (permalink)  
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...
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:55 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.