Click here to Skip to main content
15,847,264 members
Please Sign up or sign in to vote.
4.33/5 (3 votes)
See more:
Hi All,

I have an access 2002 application, I'm using ADODB to insert a record into a table in a SQL Server 2000 backend. I am using a connection object and a recordset object.

The code for works fine for 40 users, but simply will not work for 2 particular people.

When I step through the code as these users I see that the recordset never opens, I have tested my connnection object at this point and it is open.

At the point below the recordset does not open.

Set rs = New ADODB.Recordset
rs.Open strSQL, dbRDDTCnn, adOpenDynamic, adLockOptimistic

I cannot fathom why vba code that works perfectly for so many people would not work for just two people.

I would be very grateful for any suggestions you might have

Thank you!!
Updated 25-Aug-11 15:21pm
Dr.Walt Fair, PE 25-Aug-11 23:13pm    
Does it give any error message? What are the values of the rs.Open parameters for those 2 users?
Jennifer McCartthy 26-Aug-11 8:51am    
Your answer
thank you for your reply.

Yes, after the line it tells me "ERROR #3704 Operation is not allowed when the object is closed"

The parameters for are

rs.Open strSQL, dbRDDTCnn, adOpenDynamic, adLockOptimistic
strSQL is a simple SQL statement, and dbRDDTcnn is my connection object that tests to open.

Thanks again
Maciej Los 29-Aug-11 5:20am    
Which Office application: Excel, Word, Access? Which version? MS Office versions are the same on all computers? Have you try to reinstall MS Office?
Jennifer McCartthy 29-Aug-11 12:07pm    
Afternoon, thank you for your reply.
we are using Access 2002. We are in a terminal services environment so they are all the same version. I haven't tried to reinstall office as the users are sharing the same instance of access and yet this problem is only happening for two users.
Maciej Los 31-Aug-11 2:23am    
Without knowing more details about connection object we can't help you (security settings). It looks like the connection is closed. Is it happend only for those 2 users no matter of terminal?

1 solution

First of all, read my (losmac) comment to your question. Before you call open method[^] for recordset, you need to open connection. If connection is open, try to change CurstorType[^] parameter, from: adOpenDynamic to: adOpenStatic or adOpenUnspecified.

More: How To Open ADO Connection and Recordset Objects[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900