CODEWITHSUNDEEP
vbaexcelms-access
dsauce
dsauce

Reputation: 612

Working with OLEDB connection on Office 2013 64 bit

I know there are a number of threads related to this. However, none of them seemed to solve my problem. I am putting down my exact problem here. Can someone help me with this?

Environment - Windows 7 64 bit, Office 2013 64 bit

Automation - A macro in VBA in Excel that will import some data to an .mdb file by connecting to it using the below mentioned driver

Issue - Runtime error 3706, provider cannot be found. it may not be properly installed

Attempt 1 - I checked the error code online and most of the threads pointed to syntax errors and way of connecting. I tried implementing the suggestions but there was no impact

Attempt 2 - I then found that this is because it was trying to use old driver with 64 bit office. Therefore, I then tried installing the most popular suggestion - http://www.microsoft.com/en-us/download/details.aspx?id=13255 This also didn't work

Code - 

database_path = Application.ActiveWorkbook.path & "\" & "mydb.mdb"
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & database_path
End With
cn.Open

Any solution is welcome as long as it works on all - office 2007, 2010 and 2013.

Thanks!

Upvotes: 0

Views: 17658

Answers (1)

Tom
Tom

Reputation: 461

If you downloaded and installed the driver from the link you have in your question, you will notice that the "Install Instructions" has a section called "To use this download:". #2 under that section states that you need to change the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0” instead of "Microsoft.Jet.OLEDB.4.0".

I ran your modified code with the replaced argument in Excel 32-bit and 64-bit and it worked:

Set cn = CreateObject("ADODB.Connection")
    With cn
       .Provider = "Microsoft.ACE.OLEDB.12.0"
       .ConnectionString = "Data Source=" & database_path & ";"

    cn.Open
End With

The ACE driver you downloaded can be used in Office 2007, 2010 and 2013 according to this article:

Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb

Upvotes: 4

Related Questions