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

MatthewNYC

New Member
Joined
Feb 19, 2017
Messages
18
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.


My BIG concern is that I am developing a commercial Excel spreadsheet and now I'm wondering what problems my users will experience with the software depending on their version of Windows, Microsoft Office, and whether both (or one) are 32 or 64 bit.


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


Matthew
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.

not a problem

I have
MS Excel 2013 (15.0.4641.1000) 32 bit
MS Access 2013 (15.0.4641.1000) 32 bit
MS Windows 7 64 bit

and have no problems doing what you're trying (of course both my Excel and Access are 2013)

I have the References checked
Visual Basic for Applications
Microsoft Excel 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Object Library

I suggest checking your anti-virus software
https://www.mrexcel.com/forum/micro...anceprotect-createobject-no-longer-works.html
 
Last edited:
Upvote 0
Hi James,

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 15.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)

I don't understand why Microsoft makes something that should be so simple, so difficult.

Matthew
 
Upvote 0
THis doesn't look like it would work:

Code:
SQL = "SELECT * FROM settings"
Set rs = objConn.Execute(SQL)

Did you post all of your code?
 
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top