Pass-through queries fail even after SQL Server reconnected

>I have an Access 2000 mde application which uses ADO and pass through
>queries to communicate with SQL Server 7, 2000 or 2005.
>
>Some of my customers, especially those with SQL Server 2005, have had
>pass-through queries fail due to intermittent connection failures.
>
>I can easily restablish a connection for ADO.
>
>My problem is with pass-through queries.
>
>It appears from my experiments that MSAccess establishes a connection
>to the SQL Server the first time it runs a pass-through query. It then
>tries to use that same connection whenever any later pass-through
>queries are run.
>
>If there is a network problem or the SQL Server temporarily goes
>offline or the SQL Server rejects the connection for whatever reason,
>any later attempts to use pass-through queries fail, even after the
>SQL Server is back online and connected via ADO.
>
>The first one to fail returns the following error message:
>
>ODBC--call failed.
>[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite
>(WrapperWrite()). (#55) [Microsoft][ODBC SQL Server Driver][Shared
>Memory]General Network Error. Check your network documentation. (#11)
>
>Subsequent failures return:
>
>ODBC--call failed.
>[Microsoft][ODBC SQL Server Driver]Communication link failure (#0)
>
>Obviously these errors are telling me to fix something in the network
>or SQL Server.
>
>Unfortunately the networks and SQL Servers where my application is
>deployed are not under my control.
>
>I just want to accept the fact the connection has been interrupted and
>establish a fresh connection.
>
>I have tried using DAO to create a querydef object from the query and
>reset its connection string. The code runs but the queries still fail.
>
>Likewise I have tried using ADOX to reset the Pass Through Query
>Connect String property of the command of the relevant procedure in
>the ADOX catalog . The code runs but the queries still fail.
>
>I tried closing and reopening the application without closing Access.
>The queries still fail.
>
>If I close Access altogether then reopen it and the application, the
>queries work again.
>
>Also, if leave the application open and wait a long time (e.g. half an
>hour), the queries work again.
>
>I don't want my users to have to wait half an hour or close and reopen
>Access.
>
>Is there some property I can set or method I can run on the Access
>instance that will allow my pass-through queries to run again once the
>SQL Server is back online?
>
>I have tried setting all the various cryptically-named intervals that
>appear in the Access 2000 Tools/Options/Advanced dialog to low
>numbers. It makes no difference.
>
>Does anyone have any suggestions?
>
>Mike

On Wed, 30 Apr 2008 17:49:37 -0700 (PDT), marshmallo...@yahoo.com
wrote:
>
In my experience "General Network Error" is a very serious event and
indicates (perhaps intermittent) network connectivity problems. Think
of the entire chain between the client machine and the server machine:
the cables, the nic, the switch, some part of it is intermittently
failing and until it is identified and fixed there is little software
can do.
>
ODBC is a pretty old way to connect to SQL Server. I think no
development has been going on there for many years. At least consider
upgrading to OLEDB. Of course the real development is with ADO.NET,
but then you're leaving the realms of Access.
>
-Tom.
>
>
>>>>>>>>>>>>>>>>>>>>>>>>
- Show quoted text -

>Thanks Tom,
>
>This is a big old application and I'm stuck with pass-through queries.
>
>I'm also not in a position to exert much influence on the IT
>departments of our numerous customers. I suspect that the real culprit
>is SQL Server 2005 closing connections that are still needed. Our SQL
>Server 7 and 2000 customers have not reported this problem.
>
>Ideally I'd like to find a solution that lets my app reconnect
>whatever the cause of the lost connection.
>
>Does anyone have any other ideas, especially on how to tell Access to
>renew/replace the connection it uses for pass-through queries?
>
>Mike
>