0

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!

dsauce
  • 592
  • 2
  • 14
  • 36
  • in case there's a struggle with this, I don't mind getting office 2010 provided office 2010 32 bit and office 2013 64 bit can co-exist. can they? – dsauce Oct 22 '14 at 19:52

1 Answers1

4

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

Community
  • 1
  • 1
Tom
  • 461
  • 1
  • 3
  • 12
  • i see. let me try this and get back. thanks very much! – dsauce Oct 23 '14 at 08:41
  • this worked absolutely fine. many thanks! I only have one small question. I don't remember if installed 32 or 64 bit version of this engine. Can you confirm which one to install if I have 64 bit OS and 64 bit Office? my guess would be 64 bit. thanks again! – dsauce Oct 23 '14 at 11:32
  • 1
    Since you have a 64 bit OS, you can install either the 32 or 64 bit version of the driver. – Tom Oct 23 '14 at 13:45