RunTime Error - 2147217900 Invalid SQL Statement; expected 'DELETE' 'INSERT' etc.

borenstael · October 10th, 2010, 01:13 PM

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

HiTechCoach · October 10th, 2010, 01:31 PM

Quote:

Originally Posted by borenstael

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?

HiTechCoach · October 10th, 2010, 01:36 PM

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

borenstael · October 10th, 2010, 02:36 PM

Problem solved!! Thanks for your assistance!

HiTechCoach · October 10th, 2010, 06:52 PM

You're welcoem!

Glad to be able to assist.