ODBC errors
-
We have a Microsoft Access Application that uses ODBC links to our SQL Server Database. Apparently, when the application is left open for a long time; the following errors are issued, see below. Any help is appreciated. Thanks.
1.) ODBC--call failed. [Microsoft][ODBC SQL Server Driver]Communication link failure (#0)
2.) ODBC--call failed. [Microsoft][ODBC SQL Server Driver][DBMSSOCNDATABASE=]ConnectionWrite(send()). (#10054) [Microsoft][ODBC SQL Server Driver][DBMSSOCNDATABASE=]General network error. Check your network documentation.(#11)
-
I have also run into the same problem. Front end Access DB with back end SQL DB, and from time to time I get the 'runtime error 3146 - ODBC call failed' error. It seems some feel this is due to the query timeout parameter, which if is set to 0 means no timeout, but this did not help me. Others feel it is due to setting 'nocount' to 'on', which will prevent SQL Server from send messages to the clients stating the number of rows affected from updates/inserts/deletes, but this did not solve the problem for me either. If either of these help let me know, otherwise if you find a solution I would love to hear it. Thanks.
-
It may not help, but I have been experiencing a similar problem with a VB6 application that runs on either Access or SQLserver but uses linked tables from access to SQLServer to extract data for emailing data between remote users.
I believe the problem is due to the client's network even though packet sniffing and other network diagnostics have failed to find the problem. It could be due to an odd lost network packet or a maybe a timeout when the server (SQLServer?) polls the client PC.
I get around the problem with the linked tables by testing that they OK before they are used, by running query 'SELECT TOP 1 * FROM tablename' for each table. If there are any errors raised I delete and recreate the linked tables in the program. This is OK for me because the linked tables are not used very often. In your case it maybe something you can do when a connection error is raised.
Also, when the VB application runs against SQLServer I changed it from using ODBC to OLEDB because OLEDB has two useful ADO recordset properties ("Preserve On Commit" and "Preserve On Abort"). When error -2147467259 is raised for a recordset ("operation not allowed when object is closed" I think) I simply close and open the ado connection object and carry on processing as normal (the connection is re-established).
I hope this gives you some ideas.
Peter
-
Hello, all. I don't know if this has anything to do with your experience or not, but here goes. When I am in SQL Server Enterprise Manager and I leave a table open too long I finally get a message to the effect that there has been no activity and SQL Server will close the table unless I tell it I want to keep it open.
When you get the ODBC error in Access have you left a table or other object (query, report, etc.) open for a long period of time without activity? Shoot, for all I know just leaving the database open may produce the same error, since the database is dependent on the ODBC drivers.
Hope this sheds some light.
Regards,
Don
-
Hello,
Did anyone ever definitively resolve this issue? We're having the same symptoms (i.e. ODBC call failed/general network error) with an Access 2010 front end linked to an SQL Server back end.
Also similar circumstances (i.e. after the Access user has been idle for awhile).
Any insights appreciated.
-
We will need to know a good deal more about your situation. As a preface we've run numerous workstations running Access 20XX against versions of SQL Server from 7 forward with good success and limited network error problems. When they did occur it was generally either flawed Access design, or actual network issues.
What version of SQL Server are you using?
Are all of your tables linked permanently or are some linked dynamically each time the app is started?
What OS are your workstations running?
What sort of DSN is created on each workstation - System, User or other?
What speed is your network?
Is it all a wired network, or are some users on WiFi?
Does your Access app close all tables after a task is complete?
Roughly how many SQL tables are linked to your app?
Are your forms bound to specific tables or queries?
Unfortunately, solving these kind of problems is complex, and all of the above factors can contribute to them.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
Hello Wendell
Thank you for your response.
Here are some answers to your questions and some additional information:
- We're running SQL Server 2008.
- The workstations are all MS Windows 7 Professional.
- The network is capable of Gigabit speed but the real throughput is much lower.
- The forms are bound to tables.
- The ODBC connection is via a System DSN.
- I don't release the bound table when a form closes.
- All of the bound forms are static, not dynamic.
- The error doesn't occur when I'm on the main menu screen (which isn't bound to a table).
- The error occurs not only when we're in forms that are bound to tables, but also when there is a table open for direct editing.
- The error typically occurs after the system sits for awhile without any user activity.
Any direction you could provide about this would be appreciated. Of course, we have folks from SQL Server support, MS Access support, and Network support looking at it, but it's not clear where the problem may reside.
Appreciatively,
SG
-
The thing I would suspect the most is the use of a System DSN - from Win7 forward our experience with System DSNs has been problematic. As long as users are being validated on the network (i.e. have a login generated on the domain controller), User DSNs work much more reliably. They are a pain to maintain of course, as when the user changes you have to be sure the new user has a DSN defined. Any other issue is going to be a challenge to identify.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
Hello Wendell,
Thanks for the lead. Our user base is very small, so User DSNs may be manageable. At least it's worth a test.
-
The assumption that I made in that reply is that you are using Integrated Security rather than SQL Server Security. There are several advantages in that approach, less SQL Server administrative work, the ability to track a user when they roam, the ability to see the user in error logs, and the ability to track who changed what using triggers or other automation tools. Sorry I didn't think to add that in my last reply.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain! -
Hello Wendell,
Thank you for the clarification. I tried using a User DSN but it didn't resolve the problem. Will check to see what kind of security we're using.
Best,
Grace
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply