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
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.
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
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
ASKER
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.
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 connectionStrings.com, each connection string should reference the applicable instance.
http://www.connectionstrings.com/sql-server#6
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.
http://www.connectionstrings.com/sql-server#6
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 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?
http://www.connectionstrings.com/sql-server
http://www.connectionstrings.com/access
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.
Does any of this help?
http://www.connectionstrings.com/sql-server
http://www.connectionstrings.com/access
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.
ASKER
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.
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.
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Possible "cure" found by self.
Can you create this pass-through query by hand and run and navigate through rows successfully from the Access front end?