ADO Connection to Excel : Excel 2010

[Stephen_James]

I recently upgraded my computer to a 64 bit computer, however, Excel (2010) on my computer is 32 bit. I have the following ADO connection that worked fine on my old computer, but now gives me a 3706 run time error:


conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & strDir1 & "';" & _
"Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"


I have the reference to MS ActiveX Data Objects 6.1 library checked, so that is not the issue.

Does anybody know how to fix this? I tried Google for over an hour and couldn't find a specific fix. I believe it is due to the conflict between my 64 bit OS and my 32 bit Office Application, but that is just a hunch.

 

[LockeGarmin]

Instead of the OLEDB driver you might be able to use the Excel driver instead.

Sub TestConnectionString()

Dim pCN As Object
Dim Path As String


  Path = "C:\Users\Locke\Desktop\Book1.xlsx"
  
  Set pCN = CreateObject("ADODB.Connection")
  Call pCN.Open("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & Path)
  Call pCN.Close
  
  MsgBox "Successfully Connected"
  
End Sub

You might also try downloading and installing the 64 bit version of the Access Database Engine.

Access Denied

 

[LockeGarmin]

I'm not sure why that link says "Access Denied". When I click on it it redirects me fine to Microsoft's website. Hopefully it does the same for you. If not just try putting the following into a search engine and it should be the first result:

Microsoft Access Database Engine 2010 Redistributable site:microsoft.com

 

[Stephen_James]

After some clicking around/modifying the report, I was able to get this different method to work. Thanks for the information.