accessNator
Registered User.
- Local time
- Today, 14:36
- Joined
- Oct 17, 2008
- Messages
- 132
MODS: If this isnt the appropriate area, please move to the appropriate forum.
I have researched this topic in this forum and still have not found a viable answer. Maybe this will help someone answer my question by actually give the steps on how to duplicate this problem.
I am running Access 2010 FE and SQL Server 2005 BE.
I can execute pass through queries to my SQL Server succesfully by using DSNless connections.
During my testing phase sometimes I need to restore my database to get back to my original records so I can rerun my pass through queries. What I have found is when I run a pass through query, it creates an active connection on my SQL Server. I see the connection via the SQL Server Management Console under the MANAGEMENT | SQL Server Logs | Activity Monitor, select view processes. There I can see which process ID is being used and who is using it when I run my pass through query.
Now the only way for me to restore my database is to KILL the PROCESS e.g. Active connection
Now when I have my restored database in place and re-run the pass through query, I receive a ODBC -- Call Failed message box. I have attempted to run a procedure to refresh my querydefs but to no avail, I will still get the ODBC-- Call Failed message box when I click on those objects.
Now there are two options on how to fix this problem, which in either case I find not USER Friendly.
ODBC Error Number: 0
Error Description: [Microsoft][ODBC SQL Server Driver]Communication link failure
ODBC Error Number: 3146
Error Description: ODBC--call failed.
So if for some reason, I need to restart my SQL server or kill a process (Active Connection) on my SQL server while the Access Application is currently connected via ODBC, the objects created via ODBC will not perform properly till I execute the 2 workaround solutions as stated above.
Can anyone shed some advice on a solution? I appreciate any insight.
I have researched this topic in this forum and still have not found a viable answer. Maybe this will help someone answer my question by actually give the steps on how to duplicate this problem.
I am running Access 2010 FE and SQL Server 2005 BE.
I can execute pass through queries to my SQL Server succesfully by using DSNless connections.
During my testing phase sometimes I need to restore my database to get back to my original records so I can rerun my pass through queries. What I have found is when I run a pass through query, it creates an active connection on my SQL Server. I see the connection via the SQL Server Management Console under the MANAGEMENT | SQL Server Logs | Activity Monitor, select view processes. There I can see which process ID is being used and who is using it when I run my pass through query.
Now the only way for me to restore my database is to KILL the PROCESS e.g. Active connection
Now when I have my restored database in place and re-run the pass through query, I receive a ODBC -- Call Failed message box. I have attempted to run a procedure to refresh my querydefs but to no avail, I will still get the ODBC-- Call Failed message box when I click on those objects.
Now there are two options on how to fix this problem, which in either case I find not USER Friendly.
- Restart my Access Application
- Wait approx 5-10 minutes to rerun the Pass Through Query
ODBC Error Number: 0
Error Description: [Microsoft][ODBC SQL Server Driver]Communication link failure
ODBC Error Number: 3146
Error Description: ODBC--call failed.
So if for some reason, I need to restart my SQL server or kill a process (Active Connection) on my SQL server while the Access Application is currently connected via ODBC, the objects created via ODBC will not perform properly till I execute the 2 workaround solutions as stated above.
Can anyone shed some advice on a solution? I appreciate any insight.
