Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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 October 10th, 2010, 01:13 PM
Registered User
 
Join Date: Oct 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default RunTime Error - 2147217900 Invalid SQL Statement; expected 'DELETE' 'INSERT' etc.

Listed below is my code that creates the error. I have several other Access 2007 VBA programs and they all run with the same basic code! Please advise to the problem I have created for myself!

Code:
Private Sub CreateShipRecordset()

    Dim cnShips As ADODB.Connection
    Dim rsReportData As Recordset
    Dim strConn As String
    
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                "Data Source=" & "C:\Documents and Settings\SunBelt\My Documents\tsra-report.accdb"
                
    ' create a new connection and open it
    Set cnShips = New ADODB.Connection
    cnShips.Open strConn
    
    ' create a new recordset
    Set rsReportData = New ADODB.Recordset

    ' set properties of recordset
    With rsReportData
        ' specify a cursortype and locktype that will allow updates
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        
        ' open the recordset based no the tblReportData
        .Open "tblReportData", cnShips - Code causing the error according to the debuger
        
    End With
    Stop
   
End Sub

Last edited by borenstael; October 10th, 2010 at 01:17 PM..
 
Old October 10th, 2010, 01:31 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Quote:
Originally Posted by borenstael View Post
Listed below is my code that creates the error. I have several other Access 2007 VBA programs and they all run with the same basic code! Please advise to the problem I have created for myself!

Code:
Private Sub CreateShipRecordset()

    Dim cnShips As ADODB.Connection
    Dim rsReportData As Recordset
    Dim strConn As String
    
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                "Data Source=" & "C:\Documents and Settings\SunBelt\My Documents\tsra-report.accdb"
                
    ' create a new connection and open it
    Set cnShips = New ADODB.Connection
    cnShips.Open strConn
    
    ' create a new recordset
    Set rsReportData = New ADODB.Recordset

    ' set properties of recordset
    With rsReportData
        ' specify a cursortype and locktype that will allow updates
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        
        ' open the recordset based no the tblReportData
        .Open "tblReportData", cnShips - Code causing the error according to the debuger
        
    End With
    Stop
   
End Sub


Where is the open statement that goes after the comment?
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old October 10th, 2010, 01:36 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Well I see it now in the quotes test of the original post.

Ah . you set the font color to yellow and I could not see it.

Try using an actual SQL statement not just a table name. That will work with DAO and JET. AFAIK JET will translate the table name to a valid SQL statement for you. This is not true with ADO.

Try this:
Code:
 .Open "Select tblReportData.* from tblReportData;", cnShips, adOpenDynamic, adLockOptimistic
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old October 10th, 2010, 02:36 PM
Registered User
 
Join Date: Oct 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Problem solved!! Thanks for your assistance!
 
Old October 10th, 2010, 06:52 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

You're welcoem!

Glad to be able to assist.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015





Similar Threads
Thread Thread Starter Forum Replies Last Post
Microsoft Jscript runtime error: object expected swathi reddy ASP.NET 3.5 Basics 1 April 20th, 2010 03:22 AM
Error: Expected end of statement crabjoe Classic ASP Basics 3 December 4th, 2007 12:52 AM
T-SQL statement causing error with insert/update saf SQL Language 0 September 7th, 2007 04:48 PM
Invalid SQL statement expected; ‘DELETE’,’INSERT’, knowledge76 Access VBA 4 May 21st, 2007 08:04 AM
Insert Error Number "-2147217900" shabirmaher General .NET 0 August 3rd, 2005 05:30 AM





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