Vba ошибка 3706 - Не ошибается лишь тот, кто ничего не делает!

I am trying to connect Visual Basic to MS Access using ADODB. But as I execute my code, it prompts: «Provider cannot be found.It may not be installed properly.» But when I check on my directory I’ve got my «msjetoledb40.dll» installed.

Here is my code:

Dim conn As ADODB.Connection, rec As ADODB.Recordset

    Sub sample()

    Set conn = New ADODB.Connection

    conn.Open ("Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:sample.mdb;Persist Security Info=false;")
End Sub

asked Nov 29, 2013 at 5:18

This would be better:

Sub sample()

  Dim conn As ADODB.Connection, rec As ADODB.Recordset
  Set conn = New ADODB.Connection
  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:sample.mdb;"
  conn.Open
End Sub

You missed a point.

Microsoft.Jet.OLEDB 4.0 => Microsoft.Jet.OLEDB.4.0

Ref: http://www.connectionstrings.com/.

answered Nov 29, 2013 at 7:26

jacouhjacouh

8,3605 gold badges31 silver badges43 bronze badges

Confirming the version of your MS Office on which the script is running. If you have installed MS Office 2013 or later, you should revise the connection string from:

Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:sample.mdb;Persist Security Info=false;

to:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:sample.mdb;Persist Security Info=false;

At least, this sovled my problem.

answered Nov 15, 2017 at 2:45

My solution:

Error 3706

cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Persist Security Info=False;"

Just Change 12.0 for 15.0

cs = "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" & sPath & ";Persist Security Info=False;"

and works, always you must to try change the version of the controller!.

msanford

11.7k10 gold badges65 silver badges93 bronze badges

answered Aug 16, 2017 at 18:58

Источник

Remove From My Forums

Question
I am trying to connect to an Oracle Database from Excel VBA and have Windows7 SP1 installed on my machine.

I am getting an error message when it tries to open the connection, I have also installed Oracle Client and SQL Developer and the same connection string works fine from SQL Developer but not VBA.

Earlier I was using provider as OraOLEDB.Oracle but I was getting RunTime Error -2147467259(80004005) Automation Unspecified error, then I removed . Oracle from it which led to 3706 error.

Could you please suggest me something here as now i have spent 2 days almost here with nothing working out, am I missing any driver or setting ?

PFB the snippet of code, also i have added MS Active X Dataobject 6.1 library and MS Active X Data Object recordset 6.0 library.

Conn_String = «Provider=OraOLEDB;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 123)(PORT = 123)) )(CONNECT_DATA =(SERVICE_NAME = abc)));User
Id=abc;Password=abc;»
    Set cn = New ADODB.Connection


    With cn
         .ConnectionString = Conn_String
         .CursorLocation = adUseClient
         .Open
    End With
    If cn.State = adStateOpen Then
        MsgBox («connected successfully.»)
    Else: MsgBox («Error in connection.»)
    End If

Answers

Hi,

>>I am getting an error message when it tries to open the connection, I have also installed Oracle Client and SQL Developer and the same connection string works fine from SQL Developer but not VBA.<<

According to your description, I assume the code has no compile error since it can connect to SQL Server successfully. So the issue may be more related to the connection string or your Oracle provider rather than
development of Excel or VBA. Please check whether the provider is correct in your connection string.

After researching, I find the Runtime Error 3706 means «Provider cannot be found».

Here is a KB article about this topic for your reference:
PRB: ERR «ADO Could Not Find The Specified Provider»

The MSDASQL OLE DB provider is either unavailable or is not registered. This can be caused by an incomplete upgrade or a bad install of the Microsoft Data Access Components (MDAC) components.

Here are two ways to resolve this problem:
- Register the Msdasql.dll using the following command:
  REGSVR32 «C:Program FilesCommon FilesSystemole dbMSDASQL.DLL»
- Reinstall the Microsoft Data Access Components used. The newest version of MDAC can be downloaded from the following Web site:
By the way, since this forum is for Office development, for more question about connect to Oracle, you could also consider posting in
Oracle official forum for more responses.

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Click
HERE to participate the survey.
- Edited by
  
  Friday, April 11, 2014 7:32 AM
- Marked as answer by
  Luna Zhang — MSFT
  Wednesday, April 16, 2014 7:40 AM
Thanks Luna for your reply, I uninstalled the Oracle client and installed it manually and it worked, Thanks.
- Edited by
  PoonamDubey
  Friday, April 11, 2014 11:38 AM
- Marked as answer by
  Luna Zhang — MSFT
  Wednesday, April 16, 2014 7:41 AM

Источник

Пользовательское соглашение

Политика конфиденциальности

Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.

Техническая поддержка сайта

ООО «Планета Эксел»

ИНН 7735603520

ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Источник

Remove From My Forums

Question
Hi all,

I recently (after a lot of sweat) Managed to install VB6 on Win8.

Fact is I get Runtime 3706 error (Provider not found, it may be not properly installed) when trying to connect to na accdb (Access 2010 database) vi aMicrosoft.Ace.oledb.12.0,

The amusing part is that Microsoft.Ace.oledb.12.0 is fully working and being recognized by Excel (VBA).

Since I have a bag full of some 5 years of médium-t0-big Works in VB6, and, being 70yrs old, have not the nerve, nor the skills to migrate all the bulk to VS 2010, please, help
- Moved by
  
  Monday, November 19, 2012 9:00 AM
  (From:Visual Basic General)

Источник

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

Источник

Question

Answers

Question