MS Access OpenRecordset fails intermittently

We use Access 2003 with a 2000 format database as the front end for a number of linked tables to SQL Server 2000. We recently added SSL encryption to the SQL Server for another project. Using the Server Network Utility I've confirmed that Protocol Encryption is checked. However, the web server ASP.NET connection string did not require any changes when connecting to this database server. Specifically, we did not add Encrypt=YES to the connection string but our app still connects to the database server.

The Access database is separate from the .NET app and resides on the database server. It uses a DSN for all of it's linked tables. It did not require any changes eiher once we enabled the encrypted protocol.

Since the protocol encryption, we have begun to get 3146 ODBC Call Failed errors. The code below is called when a user moves between records on a form and causes the error when the OpenRecordset method is called. The Connect string is as follows:

ODBC;DSN=MyDSN;Description=MyDatabaseDSN;APP=Microsoft Office 2003;WSID=MyServer;DATABASE=MyDatabase;Trusted_Connection=Yes

The OpenRecordset call is made whenever the user changes records but the error doesn't always happen. I'm interested in the following:

1) Is the database server actually forcing protocol encryption?
2) What do I need to do to fix the access database so it stops causing errors?

Thank you in advance for your help.

strSql = "SELECT dbo.uMyFunction(" & _
         nID & ", " & _
         dValue1 & ", " & _
         dValue2 & ", " & _
         0 & ")"
   Set db = CurrentDb
   Set qdf = db.CreateQueryDef("", strSql)
   qdf.Connect = CurrentDb.TableDefs("dbo_ClientUser").Connect
   Set rs = qdf.OpenRecordset

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dwe761Software EngineerCommented:
You said this happens intermittently?  Can you identify any conditions under which it occurs?

Can you create this pass-through query by hand and run and navigate through rows successfully from the Access front end?
ZekeLAAuthor Commented:
It doesn't happen every time but most times when we navigate. The problem doesn't appear to be with the navigation. The code above is called during Form_BeforeUpdate which occurs when we change from one record to another.

Here's some additional info:

The db server has two databases: Prod and Test, each linked to a different but identical access front end. The Prod mdb has the errors which occur very frequently now. The Test mdb almost never has the error. (I've only been able to get it to happen once.)

The querydef ODBCTimeout is set to 60 seconds. Since we have encryption, the amount of data transferred would be greater which I started to think would cause a timeout error. But the error is almost instanteous. I've also occasionally gotten an ODBC SQL Server Driver Communication link failure (#0) error message.

It seems like the connection is being broken and not being reconnected automatically.

Are Prod and Test running on seprate instances of sql server?  According to, each connection string should reference the applicable instance.
Connecting to an SQL Server instance:
Use serverName\instanceName as Data Source to use a specific SQL Server instance. Please note that the multiple SQL Server instances feature is available only from SQL Server version 2000 and not in any previous versions.  The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dwe761Software EngineerCommented:
The Before_update event would not occur just from navigating to a different row. Something had to be updated in order for this event to kick off.  In each occurrence of the error, did it happen to the same user?  Did the user have update permissions on that data source as well as execute permissions on the function?

Does any of this help?

Although I tend to think it is not the connection string since it is intermittent.

Have you tried attaching the Test.mdb to the Production SQL database or vice-versa?

The intermittent problems are always more difficult to determine until more can be identified.
ZekeLAAuthor Commented:
The user changes a field value for the row. When they change rows, the before update event occurs which calls the code above which displays the query result (a single string value) in an unbound field in the row just updated.

Both mdbs are the same with the exception of some labels on the main form showing which mdb it is and the database the tables are linked to. (When rolling out changes, test.mdb is copied as the new prod.mdb, the labels are changed and the tables relinked to the prod sql database.

All users use the same credentials (trusted connection). The connection string is pulled from the tabledef of one table and therefore matches the DSN the tables are all linked to.

When changing rows, they are usually creating a new row, but not always. The problem occurs both ways, I believe.
dwe761Software EngineerCommented:
dwe761Software EngineerCommented:
Could it also be intermittent depending on the number of concurrent users hitting the same row?  Can you reproduce the error if two users try to concurrently add a new row or update the same row?  Is your BeforeUpdate code locking the table preventing other users from doing anything?
ZekeLAAuthor Commented:
My server is set to return the number of rows. Per that article, my config value is set to 0, not 512. So that's not the cause. Thanks for the suggestion.
ZekeLAAuthor Commented:
The "solution" seems to be to disable enforce protocol encryption. For the two days since we did that, no errors have been observed. So I'm abandoning this question.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZekeLAAuthor Commented:
Possible "cure" found by self.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.