CODEWITHSUNDEEP
excelvbaadodboledbconnection
Fabrizio
Fabrizio

Reputation: 662

Run time error '3706': Provider cannot be found - Excel VBA "Provider=Microsoft.Jet.OLEDB.4.0"

I saw that this error isn't new, but I can't find the solution.

I have one xls file that use one sheet like as db and with ADODB i get the recordsets that I need.

The code is Very simple and work right for each pc(5) that I tested, with WIN7, WIN10, 32 or 64 bit.

But I've on PC, it's customer Pc, that get me this error: Run time error '3706': Provider cannot be found, I has checked the WIN version, the office version, they are the same like other PC, WIN10 64 Bit, MS Office 32Bit

There are more control that I've to do to resolve this problem?!?! thanks for any suggestions fabrizio

My xls file have 2 sheet, 1th named "dati" with two columns (Anno, Pezzi), 2th named "test" empty, this is the code:

Sub testConn()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1


Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

Set rs = New ADODB.Recordset
    #If Win64 Then
        cn.Open "Provider=Microsoft.Jet.OLEDB.12.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 8.0;HDR=Yes;"";"
    #Else
         cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 8.0;HDR=Yes;"";"
    #End If

strsql = "SELECT anno, Sum(Pezzi)as Tpz from [dati$] group by anno"

rs.Open strsql, cn, adOpenStatic, adLockReadOnly, adCmdUnspecified
rs.MoveFirst
With Worksheets("test")
    .Cells.ClearContents
    .Range("A1") = "Anno"
    .Range("B1") = "T.Pz"
    .Range("A2").CopyFromRecordset rs
    .Activate
    .Select
End With
End Sub

these references was added into file:

Microsoft ActiveX Data Objects 6.1 Library
Microsoft ActiveX Data Recordset 2.8 Library

Upvotes: 0

Views: 6819

Answers (1)

Anabas
Anabas

Reputation: 356

This works, there are some small details you use not suitable. Version 12, driver is ace not jet,and Extended Properties also is Excel 12.0

And no need to add library.

Sub testConn()
Dim cn As Object
Dim rs As Object
Dim strsql As String
Dim connString


Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

    If Application.Version < 12 Then
        connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 8.0;HDR=Yes;"";"
    Else
        connString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 12.0;HDR=Yes;"";"
    End If

cn.Open connString

strsql = "SELECT anno, Sum(Pezzi) as Tpz from [dati$] group by anno"

Set rs = cn.Execute(strsql)

With Worksheets("test")
    .Cells.ClearContents
    .Range("A1") = "Anno"
    .Range("B1") = "T.Pz"
    .Range("A2").CopyFromRecordset rs
    .Activate
    .Select
End With
End Sub

Upvotes: 1

Related Questions