Hi - have you ever had a random disconnect to SQL server? I've gotten that but MORE relevant now, it seems our tech guys have implemented a time-out feature to disconnect ODBC connections after some time. Is there a way to "catch" such a disconnect and force a reconnection especially when running a batch process that takes a long time? THANKS!!!
Reconnect ODBC?
- Thread starter CedarTree
- Start date
GPGeorge
George Hepworth
- Local time
- Yesterday, 22:34
- Joined
- Nov 25, 2004
- Messages
- 2,850
Ouch. Time to have a meeting with the IT guys, your supervisor or manager, and the users stuck with this bad decision.
Unfortunately, I know of no way to "catch" an impending disconnect. Sort of like "catching" any random event. How would you know it's going to happen until it's happened.
Moreover, Access is very intolerant of even minor network fluctuations, which is one reason Wireless networks are highly discouraged when Access is involved.
The newer versions of Access do have more robust ODBC handling, but it's not fool-proof.
What you could try is to set up a timer than hits a linked table, or runs a pass thru query at intervals frequently enough to reset their network time out and forestall the disconnect.
But in the long run, the solution is going to be establishing communications at a sufficiently high level to make sure the network people understand the problems they are causing for users. And, hopefully, getting them to see the error of their way.
AccessBlaster
Registered User.
- Local time
- Yesterday, 22:34
- Joined
- May 22, 2010
- Messages
- 7,484
IT would regularly do maintenance on the backup SQL Server where my tables were located, the tables would usually reconnect on their own once the server came back online.
GPGeorge
George Hepworth
- Local time
- Yesterday, 22:34
- Joined
- Nov 25, 2004
- Messages
- 2,850
Did they actually do maintenance during the workday when people were actively using the Access/SQL Server relational database applications? Brave and bold choice, if indeed that is what they did.
AccessBlaster
Registered User.
- Local time
- Yesterday, 22:34
- Joined
- May 22, 2010
- Messages
- 7,484
The school district I retired from wasn't run like a corporation or a small business. Think of the DMV or the Post Office. We had great infrastructure due to massive grants but usually only an eight hour day and tons of labor issues like unions.
Anyway I don't want to derail this thread.
OKay more info. Getting sql server error 10054. I'll also google in the meantime.
---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '08001'
SQL Server Error: 10054
[Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.
Connection failed:
SQLState: '08001'
SQL Server Error: 10054
[Microsoft][SQL Server Native Client 11.0]Client unable to establish connection
---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '08001'
SQL Server Error: 10054
[Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.
Connection failed:
SQLState: '08001'
SQL Server Error: 10054
[Microsoft][SQL Server Native Client 11.0]Client unable to establish connection
GPGeorge
George Hepworth
- Local time
- Yesterday, 22:34
- Joined
- Nov 25, 2004
- Messages
- 2,850
Okay, so they did reboot the server while people were trying to work? Ouch.
The point is that when Access loses an ODBC connection during an active session, usually you have to close and reopen the accdb to reestablish that connection. It doesn't lose linked tables, just that active connection. Perhaps we're talking about different things?
Yup, they are timing out your connection. Bad juju. Talk to them about the havoc they're creating, but do it in a nice way.
