ODBC Driver has become erratic when making connection and processing SQL query

Question

I have an Excel Macro Workbook (binary extension) that suddenly has developed 2 issues occasionally (sometimes it runs fine).

#1 Issue - Occasionally there is an error on Line 5 It appears the connection does not open. We get the error: Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x7f8 Thread 0x1174 DBC

#2 Issue - Occasionally there is an error on Line 7 It appears the SQL can not run. It indicates the Worksheet tab can not be found even though it does exist. Note for this error we have no issue with Line 5 (Open Connection). The error that displays is: [Microsoft][ODBC Excel Driver] The microsoft jet database engine could not find the object 'Worksheet Name'. Make sure the object exists... For Issue #2 once we close the error dialog box and THE MACRO GETS DELETED !!!

Notes

The code module has not been changed in years Some days we get no errors/issues

This issue occurs for multiple Excel workbooks run by multiple people that run different VBA code blocks. However 1 of the code blocks is the same across all these macros which is the code snippet above.

The source data has not changed in years in terms of formatting or volume.

All these workbooks are on a Network server.

We are on Windows7/Excel 2010/ODBC Excel Driver (xls xlsx xlsm xlsb) 14.00.7180.5000

Code Snippet

1-Set adoConn = Nothing
2-Set adoRS = Nothing
3-Application.EnableCancelKey = xldisabled
4-strConn = "Driver={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};DriverId=1046;Dbq=" & strFileName & ";IMEX=1;"
5-Set adoConn = New ADODB.Connection: adoConn.Open strConn
6-Application.EnableCancelKey = xlInterrupt
7-Set adoRS = New ADODB.Recordset:adoRS.Open Source:=strSQL,ActiveConnection:=adoConn

What is the value of strFileName ? (ie., is it a UNC path?) — ashleedawg
No its the drive letter- Z:/Orders/June/Today.xlsb - Please note for 6 years we have used this code block and no issue..Yesterday fine..today we had issues as detailed above.... — daPlayaURH
Does that mean 6 years and no maintenance on the server either? :-\ So i assume Z: in a mapped drive, where is it mapped to? — ashleedawg
Actually before you explain any more - I'm pretty sure this isn't a programming issue, rather a server/networking problem. I think you'd be better off to ask this question in Server Fault, Stack Overflow is specifically for specific coding problems. (You can pretty much just copy and paste your question over there, maybe just leave out the macro-deletion part and if that's still an issue when the connection is figured out, it can be addressed easier then.) — ashleedawg

ashleedawg ashleedawg · Accepted Answer · 2018-06-15T02:54:15

I'm curious as to why you're using ODBC to connect Excel to another Excel file, as opposed as to linking the data directly.

Intermittent connection problems point to a network issue. I would suggest removing and re-creating the ODBC connection, but I'd first want to confirm the value of strFileName as referred to in the connection string. Is it an actual server on your internal network, or an external source? (ie, is it a UNC path?)

An intermittent Unable to open registry key error likely sounds worse than it is; when having connection problems, Windows & Excel will easily get confused as to what the real problem is.

It sounds like your setup has been in place, possibly un-maintained, for a long time. When is the last time the client and server were rebooted? (Should be nightly.) Defrag, disk cleanup, etc?

As for the issue of the macro "getting deleted" when you close an error dialog, I'd need to see proof of that. I think you're misunderstanding something, unless you're re-writing the macro from scratch each time this happens..

ODBC Driver has become erratic when making connection and processing SQL query

1 Answers