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