Run Time Error -2147217900 - Invalid SQL Statement - WHY?

[AccessUser22]

I have a form set up which has three list boxes. The first list box lists a group of employees. The second lists the employees that the user selects, and the third lists the final selection the user makes. There are add and delete buttons on the form which allow the user to move employees from the first list to the second list. There are also buttons which will clear out the user selection and commit the selection to memory (the third list box).

This worked flawlessly in the past. However, the other day it started acting up on me. At the present I'm getting the following error.

Run-time Error -2147217900(80040e14)
Invalid SQL Statement;expected 'Delete', 'Insert','Procedure','Select', or 'Update'

The code I'm using comes straight out of a Microsoft Knowledge base article #209878

Private Sub cmdClear_Click()

Dim conn As ADODB.Connection
Dim MyRS As ADODB.Recordset

'Set up the connection and recordset.
Set conn = CurrentProject.Connection
Set MyRS = New ADODB.Recordset

'Open the Recordset
MyRS.Open "tblCorrespondenceList", conn, adOpenDynamic, adLockOptimistic

With MyRS
'Loop through all records and set the Yes-No_fld to "Yes".
Do While Not .EOF
.Fields("Yes-No_fld&quot.Value = "Yes"
.Update
.MoveNext
Loop
End With

Set MyRS = Nothing
Set conn = Nothing

' make sure the list boxes have the current values.
Me.ListYes.Requery
Me.ListNo.Requery

End Sub

Right now the error is being generated when the recordset is opened and set to the tblCorrespondenceList.

I have no idea why this error is suddenly being generated. Any ideas would be greatly appreciated. Thanks.

 

[SQLBI]

Hi,

The .Open method of the Recordset object is expecting an SQL query.

Looks like you want the entire table so you need to use the following code;

Private Sub cmdClear_Click()

    Dim conn As ADODB.Connection
    Dim MyRS As ADODB.Recordset
    
    'Set up the connection and recordset.
    Set conn = CurrentProject.Connection
    Set MyRS = New ADODB.Recordset

    'Open the Recordset
    MyRS.Open "SELECT * FROM tblCorrespondenceList", conn, adOpenKeySet, adLockOptimistic
    
    With MyRS
    'Loop through all records and set the Yes-No_fld to "Yes".
    Do While Not .EOF
        .Fields("Yes-No_fld").Value = "Yes"
        .Update
        .MoveNext
    Loop
    End With
    
    Set MyRS = Nothing
    Set conn = Nothing
         
   ' make sure the list boxes have the current values.
   Me.ListYes.Requery
   Me.ListNo.Requery

End Sub

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[AccessUser22]

Yep, I tried that before and it still produced the same error for me. No idea what is going on here....I also get the runtime error -2147467259 which is "cannot open any more databases"

 

[cmmrfrds]

Leigh, fyi, there is also a 5th parameter on the open statement that will indicate a table since the default is expecting an sql statement. Without looking it up, I believe it is adCMDTable.

MyRS.Open "tblCorrespondenceList", conn, adOpenKeySet, adLockOptimistic, adCMDTable

 

[AccessUser22]

Any idea how to correct the "Cannot Open Any More Databases" error?

 

[cmmrfrds]

Normally, when declaring an Object such as a connection, it is necessary to create an instance of the object with the New keyword. ADO may create the instance on
Set conn = CurrentProject.Connection
but I am not sure.
I usually create the instance right in the declaration.

Dim conn As New ADODB.Connection
Dim MyRS As New ADODB.Recordset

'Set up the connection and recordset.
Set conn = CurrentProject.Connection

Other than what Leigh pointed out, I don't see anything obivious. Do you have the table Open/locked when performing this routine?

Also, if this is an Access database, you might as well use adOpenStatic since everything is client side anyhow. Also, it is more flexible.

 

[AccessUser22]

Well I seem to have diverted around my error message. I was originally running a thick form with multiple subforms. This last subform I decided to remove from the main form and now it opens with no problem. So, apparently I had too many subforms running.