ADO Connection gets RunTime Error - 2147217900 Invalid SQL Statement

I am trying to open an ADO connection & recordset. Having had some difficulty I downloaded some code in a small database and it worked. I opened a new DB file in access and copied the code across and it didn't work. Giving nme the same problem I started with.

I get the error: RunTime Error - 2147217900 Invalid SQL Statement; expected 'DELETE' 'INSERT' etc.

The downloaded data base that works I have called test good.mbd; the data base I opened & copied the data into I called test bad.mbd.

In opening the connections:
------ If I change the strConnection in test bad to reference test good.mdb it works and the form works, all the edits & updates work etc.
----- If I change the strConnection in test good.mdb to reference test bad.mdb the same error comes up.

The problem seems to be in the way my version of Access is opening the database. I have Office Pro XP and am running Access 2002.

Any suggestions -- I have tried almost everything except the one that works!!
Thanks

Stephen

The code from Test Bad.mbd (identical to Test Good.mdb apart from file reference) is:

Dim rsContacts As ADODB.Recordset
Dim cnCh5 As ADODB.Connection
Dim strConnection As String

Private Sub Form_Load()

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\Test Bad.mdb;"

'create a new connection instance and open it using the connection string
Set cnCh5 = New ADODB.Connection
cnCh5.Open strConnection

'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset

'set various properties of the recordset
With rsContacts
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tblContacts table using the existing connection
.Open "tblContacts", cnCh5
'disconnect the recordset
.ActiveConnection = Nothing
'sort the recordset
.Sort = "txtLastName, txtFirstName, txtMiddleName"
End With

I can't see anything directly wrong, but it seems it is expecting a query string, not a table name, you may want to specify what you intend opening (would it work with "select * from sometable"?), and the table name, you are sure it's correctly spelled, not any typos in the table name?:

.Open "tblContacts", cnCh5,,,adcmdtable

You are sure you close and release both the connection object and recordset object when closing the form?

Problem somewhere in the mdw file

Hi RoyVidar
Thanks for the suggestions. Just to be on the safe side I rechecked all spelling etc this morning & everything ok. Tried your seggestions & no go.

However when I added your suggested ,,,adcmdtable I got a read error on the table in Test Bad.mdb. I rechecked all the permissions and they appeared corect. However I removed teh .mdw file I had set up and replaced it with the system .mdw file AND IT WORKED!

It seems that Access throws up the invalid sql error when there is a permissioning problem ... now that makes sense@#$??

However I cannot see whatthe problem was in teh mdw fiel .. I have cheked severla times and I was logged on, was the owner & had every permission possible turned on..... but it still had that error.

Oh well

Thanks . it was your comments that helped track down the source of the error

Rocinante

I get this error (-2147217900) when I use SQL statements to post text into a text field in the table, where the text contains an apostrophe -- ' --

I know I have seen an example of how to handle apostrophes in SQL statements, and that's when I found this post.

Hello, just a thought

Looks to me like the Test Bad.mdb is stored somewhere else on your drives.
and

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\Test Bad.mdb;"

could be causing the problem.

Try changing that to something like:


Changing it to your location.

OR

as Roy-Vidar suggests does your copied database definately have the tblContacts in it.

Roy, the code is not closing the Recordset just setting the ActiveConnection to nothing, this is releasing the resource from the server.

BTW looks very much like the Wrox Press to me.
If it is, I found the code useful until it worked and realised the AddNew button seems to be doing 2 jobs and..... you'll see. Also, had problems with the Permissions each time it breaks on an error.

Hope my ramblings make sense and even better, help.