Connect MS access database from MS excel - Run-time error '3706' Provider cannot be found.

Hi,
I am a new for VBA programer.

Basiclly I want connect MS access database from MS excel.
I use MS access 2010 and MS excel 2010 at Windows XP.

But I get an error say:

Run-time error '3706' Provider cannot be found. It may not be
properly installed.

at line: oConn.Open sConn

I checked the C:\Program Files\Common Files\Microsoft Shared\OFFCE14\ACEOLEDB.DLL
the file ACEOLEDB.DLL is there.
And I already added 'Microsoft ActiveX data Object 2.8 Library' refrence

Please tell me what is wrong.
Thanks a lot.
Mang

The following is my code:

Sub TestDB()
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sConn As String
Dim sSQL As String
Dim StrDBPath As String
StrDBPath = "C:\Program Files\Allstate I.T\New Business Report\BrovadaActivity.mdb"
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & StrDBPath & ";" & _
"Persist Security Info=False;"
' Open a connection.
Set oConn = New ADODB.Connection
oConn.Open sConn
' Make a query over the connection.
sSQL = "SELECT count(*) FROM [dbo_stats];"
Set oRs = New ADODB.Recordset
oRs.Open sSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText
MsgBox oRs.RecordCount

' Close the connection.
oConn.Close
Set oConn = Nothing
End Sub

2 answers