Excel crashes when attempting to open adodb connection

Question

I have searched for an answer and just cant find it anywhere.

I have an Excel file that is using an ADODB connection to read and write to a closed workbook. It all runs fine on my laptop, using a locally stored file from which to read from and write to. (Office Professional Plus 2016, Win32).

But when I copy the file to a network, Excel crashes when attempting to open the connection and file.

The network has Office Professional Plus 2010 32-bit version, and its on a Remote Desktop.

The code I use is as follows

Sub GetData()

    'To get data from closed Store Master File
    Dim strSQL As String ' used to execute a request
    Dim strSfile    As String ' the name of the source file
    Dim strServer   As String ' the drive where the source file is located
    Dim strpath     As String ' the path on the drive where the file is located
    Dim sFile       As String ' concat of server/path/file
    Dim constr      As String ' the connection string
    Dim cnn         As New ADODB.Connection ' the connection
    
    'Set connection to the Store Master file
    strServer = "P:\"
    strpath = Sheets("Control").Range("A2").Value
    strSfile = Sheets("Control").Range("A5").Value
    sFile = strServer & strpath & strSfile
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sFile & "';Extended Properties=""Excel 12.0;HDR=YES;"";"
    
    'Open connection
    cnn.Open constr '  the problem line
    
    '.... other lines of code
    
    cnn.Close
End Sub

On attempting to debug the problem, this cnn.Open constr line of code is where Excel crashes, so no VBA error data is available.

I returned the result of the sfile variable to the immediate window, copied that and attempted to open the excel file. That worked, so I know its not a problem with the file name and path.

In fact, I have some vba code that will open the file, and that works just fine.

The problem, as stated, lays with opening an ADODB connection to that closed file. Code works fine locally (files stored locally).

Appears the only error information I have is:

Problem Event name: APPCRASH Application Name: EXCEL.EXE Application Version: 14.0.7256.5000 Fault Module: mso.dll Fault Module Version: 14.0.7257.5000 Exception Code: c00000005 Exception Offset: 00bb441f OS Version: 6.1.7601.2.1.0.16.7 Locale ID: 3081

Additional information about the problem: LCID: 1033 skulcid: 1033

Any help/ guidance/suggestions? would be much appreciated.

Answer 1

For those of you who have come across a similar/same issue, I was successful by changing the connection string to Jet, and changing the source file from a .xlsx to a .xls file. Everything then worked. Not an ideal situation. There were tabs in the original source file that are not compatible with the .xls format (too many rows on one tab), so I just copied the tab I needed into the .xls format file. I will endeavour to have a newer version of Excel installed so I can revert to my original code.

Answer 2

Sorry to bring bad news but it could be caused by accessing non allocated memory.
I searched for "adodb Exception Code: c00000005" and found this.
Hopefully there is a later version of the .dll available.