Why Does Excel 2013 Crash When VBA Creates Connection To Access 2016 Database?

Question

Hi,

I am running the following software:

Windows 7 (64 bit)
Excel 2013 (32 bit)
Access 2016 (32 bit)

When I use the following VBA code to connect Excel 2013 to my Access 2016 database, Excel immediately crashes:

---- begin ----
Dim objConn As ADODB.Connection
Dim strDBConnection As String

strDBConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Me\Documents\MyAccessDB.accdb;Persist Security Info=False;"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open strDBConnection
---- end ----

I then tried using LATE binding and unchecking the reference to "Microsoft ActiveX Data Objects 6.1 Library" in Excel 2013 and ran this code:

---- begin ----
Dim objConn As Object
Dim strDBConnection As String

strDBConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Me\Documents\MyAccessDB.accdb;Persist Security Info=False;"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open strDBConnection
---- end ----

In this case Excel does not crash but reports the following error:

Method 'Open' of object '_Connection' failed

I read some message board posts that recommended installing:

Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=54920

However, this did not solve the problem.

I even tried using just about every connection string for Access from ConnectionStrings.com but none of them work.

From what I've read in the message boards (but didn't understand completely) the problem may have something to do with the fact that my Windows 7 is 64 bit and the Excel and Acess I am using are 32 bit.

Is there any Access Guru out there that has this all figured out?

Matthew

Answer 1

Hi Matthew,

Just thinking out loud here but what caught my eye in your connection string is your use of the 12.0 driver.

Access 2013 uses the 15.0 library and 2016 uses the 16.0 library. I can't say for sure if ACE only has 12.0 but you might verify this part of the connection string, just in case it makes a difference.

Just my 2 cents...

Answer 2

Hi theDBguy,

Thank you for trying to help.

In Excel / VBE Editor / References, I unchecked the box for:

Microsoft Office 15.0 Object Library

Then, when I went to recheck it, I now saw it had changed to 16.0:

Microsoft Office 16.0 Object Library 

I'm assuming this is because I installed Access 2016.

The VBA code now makes the connection without raising any errors.

However, when I attempt to create a simple recordset object I get this error:

Method 'Open' of object '_Connection' failed.

Here is the code:

SQL = "SELECT * FROM settings"

Set rs = objConn.Execute(SQL)

So, now I am stuck trying to execute queries.

Matthew

Answer 3

Hi Matthew,

Can you post the complete code? Thanks.

Answer 4

Hello safeappsoftware,

>>The VBA code now makes the connection without raising any errors.

According to below error message, it seems the issue is still that the connection could not be opened. I would suggest you share more detail your code and try to debug line by line to confirm which line caused this error.

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi,

There doesn't seem to be a resolution to this and I have the same issue.  The code I am using is the following as a Class:

Option Explicit

'using MSO.dll version 16.0.4678.1000

'Private cnn As ADODB.Connection
 Private cnn As Object

 Public Sub OpenConnection(ByVal strDataSource As String)

    'Set cnn = New ADODB.Connection
     Set cnn = CreateObject("ADODB.Connection")

    With cnn
         .Provider = "Microsoft.ACE.OLEDB.16.0"
         .ConnectionString = "Data Source=" & strDataSource & ";" & "Extended Properties=Excel 15.0 Macro;"
         .Open
     End With

End Sub


 Public Sub CloseConnection()

    cnn.Close
     Set cnn = Nothing

End Sub

And the following as a module:

Option Explicit

Sub test()

On Error GoTo ErrHandler

    Dim DB As clsConnect

    Set DB = New clsConnect
     DB.OpenConnection (ThisWorkbook.FullName)

    Debug.Print "Success"

Exit Sub

ErrHandler:
     Debug.Print Err.Description

End Sub

You will notice that in the class there are two lines commented out, these are the early bind lines. If I uncomment these and comment the 2 late binding lines then Excel just crashes. If I run as is then I get the error:

Method 'Open' of object '_Connection' failed

After playing around a bit if I change these two lines from:

.Provider = "Microsoft.ACE.OLEDB.16.0"
.ConnectionString = "Data Source=" & strDataSource & ";" & "Extended Properties=Excel 15.0 Macro;"

To:

.Provider = "Microsoft.ACE.OLEDB.15.0"
.ConnectionString = "Data Source=" & strDataSource & ";" & "Extended Properties=Excel 12.0 Macro;"

Then I still see the same crach behaviour on early binding, but on late I see it error the first time and then connect successfully each and every time after.

You will also notice that I have referenced the version of MSO.dll that we use.  The reason for this is because we first saw this behaviour occur on 64bit Win7 machines that had that version of MSO.dll but not 32bit machines that had a different version. Subsequently (outside of our control) all PCs seem to have been updated now to that version of MSO.dll and all exhibit the issue.

We have a production work around, which of course is to trap the error the first time and loop back up to re-call it but this is firstly a proper hack and sloppy and secondly has to be applied in the module not the class meaning that we have to change it in many places and can't centralise the change (yes we could and will make the call to connect a function in the module centralising it that way, but that still requires a fiar amount of time investment to make the change everywhere).

Anyway, any help on this would be HUGELY appreciated, even if that help is just to say that by applying a KB fix or upgrading a dll it resolves it as then I have a case I can escalate with central IT etc.

Thanks

(edited to change the ref to MSADO15.dll to MSO.dll as I realised I was looking at the wrong dll! Still, the change in behaiour from working to failing did occur inline with the change of both MSADO15.dll version 6.1.7601.24023 and MSO.dll version 16.0.4678.1000 so i'm guessing there's a service pack or KB fix that central IT applied organisation wide that has caused this... possibly)

Answer 6

Ridiculously after spending easily 20 hours playing with different options spread over a good few weeks, I post a message here asking for help and about an hour later find a proper fix!

In the end it was all due to upgrades as suspected. I had unticked Microsoft ActiveX Data Objects 6.1 Library (MSADO15.dll) to remove it as a variable from my test case and still saw the exact same behaviour, hence me discounting it. However for whatever reason I re-added it and now the test code (and Prod app) work perfectly.

So the issue does appear to be related entirely to an upgrade/deployment from our central IT and the fix was simply to untick references, click OK and then go back in and retick them.  I think the issue was specifcally with the Microsoft ActiveX Data Objects 6.1 Library Ref but have unticked and re-ticked all just to be safe!

Hope this helps anyone else who may come across this thread.