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
0
edit delete
are you sure?
asked Mang Li

2 answers

Are you on a 64-bit machine? Try install the 32-bit drivers of the Database Engine 2010 Redistributable.

Look here. http://www.connectionstrings.com/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

0
edit delete
are you sure?
answered Max Wikström

frnd u takes wrong refference for ace.12.0 connecion string
first unselect that refference & select refference of
'Microsoft ActiveX data Object 6.0 Library'
&
microsoft DAO 3.5 REFFERENCE THEN TRY & RETRIVE VALUE

ORS.OPEN "TABLE1",DBCON,ADCMDDYNAMIC,ADLOCKBATCHOPTIMISTIC
MSGBOX RS.FIELD(0).VALUE

DINESH KANSE.....FRM INDIA.........
IF ITS USEFUL THEN MAIL ME @ DINESH_KANSE@REDIFFMAIL.COM

0
edit delete
are you sure?
answered DINESH KANSE

Post an answer but please log in first

Post Answer
  1. Please log in to post answer

Similar Questions

Tagsfor this question

Forumall questions