Question

How to fix the Run Time ERROR 3706 provider cannot be found? in ACCESS 2007

Answer 1

Here my Code is :
Private Sub btnupdate_Click()

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''Access database

strFile = "C:\Users\Documents\MDC Tool_Backup\MDC Tool_Backup.accdb;"
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & strFile & ";"

''Late binding, so no reference is needed

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

cn.Open strCon

'Rough idea
intCATEGORY = Sheet1.[A1]

strSQL = "UPDATE [1 SCORECARD ALL SCOPE ERRORS BY CAT] SET CATEGORY = [1 SCORECARD ALL SCOPE ERRORS BY CAT].CATEGORY "


rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Answer 2

At what point does it fail?

Answer 3

"cn.Open strCon " this point to fail
when i run the above code it shows the Run time error 3706 in access

Answer 4

The correct provider is "Microsoft.ACE.OLEDB.12.0". If still not works, try installing new from here. http://download.microsoft.com/download/f/d/8/fd8c20d8-e38a-48b6-8691-542403b91da1/AccessDatabaseEngine.exe

Answer 5

Thanks for your suggestion "Neeraj"

Answer 6

and one more question asking to you
How to write a vba code for access table from access to excel sheet OR
How to write a vba code for export data from access to excel

Answer 7

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database

'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "C:\Users\Ramesh\Desktop\Final copy MDC\ValidationReportNew\ValidationReport.xls"
strWorksheet = "WorkSheet1"
strDB = "C:\Users\Ramesh\Desktop\Final copy MDC\MDC Tool_Backup.accdb"
strTable = "[1 SCORECARD ALL SCOPE ERRORS BY CAT]"

Set objDB = OpenDatabase(strDB)

'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile

objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE = " & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing

This is code for the above question but it is failed and shows a run-time error 3126 invalid bracketing.

Answer 8

objDB.Execute _ "SELECT * INTO [Excel 8.0;DATABASE = " & strExcelFile & _ "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"

this is the failed condition.

Answer 9

I actually don't know about this, i told answer because i'm familiar with VB[.Net] and Databases, but i think the error is/may be due to
strTable = "[1 SCORECARD ALL SCOPE ERRORS BY CAT]"
then this ...& "[" & strTable & "]" will be
[[1 SCORECARD ALL SCOPE ERRORS BY CAT]]
So, try code removing "[" and "]".
Multiple brackets.

Answer 10

i am trying to your suggestion of Multiple brackets.
i will contact again

Answer 11

Hi Neeraj
It is not working multiple bracketing and again shows run time error of 3078

Answer 12

Some problem with SQL syntax, table not found or incorrect syntax, try writing query in string only not using variables, i.e. to check what's wrong.

Answer 13

ok and Thanks for quick response