I've been working with an Access 2007 (12.0.6514.5000) SP2 MSO (12.0.6425.1000) application that has been around for a long time, since Version 2, and updating it to work with multiple SQL Server backends.
Since the SQL Server instances the application may need to connect to vary, each Linked Table is deleted and recreated on startup using SQL Authentication based upon defined configuration settings. Due to the legacy code, DAO recordsets are primarily used to interact with the Linked Tables. Some newer ADO connections are also created to execute a few stored procedures in the application.
The application uses DSN-Less techniques and the SQL Server 2008 SQLNCLI driver to connect to SQL Server Instances.
The first issue appeared to be related to the Access Connectivity Engine connection timeout of 600. If the application was not used for 10 mins, it would logout of SQL Server. That is fine and all, but a subsequent VBA DAO OpenRecordSet causes a connection failure. I changed the connection timeout to 0 and this issue seems to work around the issue.
The next and primary issue at the moment is also related to DAO OpenRecordSet method causing 3151 Connection Failure errors. The strange thing though is that a SQL Server Trace shows all the SPIDS still connected to SQL Server when the error occurs.
Setting the TraceODBCAPI flag provides some additional information on two errors I am getting:
DIAG [S1000] [Microsoft][SQL Server Native Client 10.0]Connection is busy with results for another command (0)
Adding error handling for error 3151 with a resume loop every second or so seems to fix the above within 20-30 seconds and actually ends up using the existing SQL Server SPIDs that had never disconnected. The performance of his loop method waiting obviously is not acceptable, but it works.
However, I cannot seem to recover from this error:
DIAG [S1000] [Microsoft][ODBC Driver Manager] General error: invalid window handle (0)
There is one puzzling exception that does not cause the above error. If there is a Hidden breakpoint in a module that is encountered before the problematic part of the application, it works just fine, no issues. I assume everyone knows what I mean about Hidden breakpoint, a breakpoint that does not clear properly, but still exists and is not marked in Red. Once I add a space to the code and do the necessary to get rid of the hidden breakpoint and compile, the issue comes back.