Run-time error 3706 - Provider not found

[BarryStevenson]

Hi Guys,
I am building an application for Excel 2003 which pulls data from an Access 2007 database using an ADO connection. I have successfully tested this using Excel 2007 in 2003 compatibility mode using :
strPathName = ThisWorkbook.Path
MyConn = strPathName & "\Test.accdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With

When I try running this using Excel 2003, I get the following error message:
Run-time error 3706
Provider cannot be found. It may not be properly installed.

I checked the VBA Project references, it references Microsoft ActiveX Data Objects 2.8 Library.
Does anyone have any ideas?

 

[Jan Erik]

Hi,

I've exactly the same problem. Have you found any solution yet?

Regards
Jan Erik

 

[Trevor G]

Hi Barry and welcome to the Forum. Hi Jan to.

Are you trying to draw data into the excel if so then you could look at using the workbook.opendatabase method, you don't need to then consider the ADO or DAO options. Take a look at this simple snippet of code to see if it helps. Forgot to say that they Help file will give you an example which pulls in data from a query Highlight the OpenDatabase and press F1

Sub connectDB()
'This method is connecting to a database without referencing to ADO or DAO
Workbooks.OpenDatabase "C:\Access VBA Practice\02Contac.mdb"
 
End Sub

 

[Jan Erik]

Hi Trevor,

Thanks for your tip - it will be useful when I shall read the complete table into Excel. But in this case I need to use some SQL to filter the data. So I guess I have to use ADO or DAO here..?

Regards
Jan Erik

 

[Trevor G]

OK try this out I have tested this and it works fine to open a database if you need to run other things then let me know and I will see if I can assist.

Sub dbconnect1()
Dim acApp As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\Access VBA Practice\02Contac.mdb")
acApp.Visible = True
Set acApp = Nothing

End Sub

 

[Jan Erik]

Thanks again. In the mean time I've discovered that the problem occured just in MS Excel 2010 64byte version. In 2003 and 2007 it works fine. But I appreciate your kind help anyway

Jan Erik