Run-time error 3146 ODBC--call failed

[Carol Grismore]

I am using Access 2002. The following code segment executes the first read,
from rs, successfully, but gives me a "Run-time error 3146 ODBC--call
failed" on the second one from rs1. Both rs and rs1 are queries. Can anyone
give me information on this error?

Dim db as Database
Dim rs as Recordset
Dim rs1 as Recordset
Dim strUserID as String
Dim strProgram as String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT program_id FROM sysuser WHERE sysuser_id =
'" & strUserID & "'", dbOpenDynaset)

rs.MoveFirst
strProgram = rs!program_id

If Right(strStartLoc, 2) = "10" Then
Set rs1 = db.OpenRecordset("SELECT * FROM report_db WHERE enabled = '" &
strUserID & "'", dbOpenDynaset)
....
End If

 

[Ofer]

Try and run the queries, see if you get this error message.
Running the query, not by code, will give you the odbc call failed, and then
it will give another message, of what is the real error.
If the query will run, then run the code, put a break before the open
recordset, copy the string of the SQL to the immidiate window
?"SELECT program_id FROM sysuser WHERE sysuser_id = '" & strUserID & "'"

and copy it to a new query and run, it will give you the real error.
And as a side note
1. try and run the tables, mybe you are not connected to the server
2. Is user id is text type, if not then the sql should look like
"SELECT program_id FROM sysuser WHERE sysuser_id = " & strUserID

 

[Carol Grismore]

These are some good ideas, but I have actually done all this and no errors
occur. Any further ideas would be appreciated.

 

[Ofer]

I dont know if that the case, but when I moved from access 97 to Access 2003,
I work with reference to other mdb's, and I got that message because I used
the
Set db = CurrentDb
but when I changed it to
Set db = Codedb
It worked fine, don't know why.

 

[Carol Grismore]

Well, that did it -- put me on the right track, since I just converted all
this from 2000 to 2002. I didn't have to change from CurrentDB, but I did
have to re-install, repair and compact, log out, log back in, and so on,
until Access finally gave up and started working. Thank you!