CODEWITHSUNDEEP
excelvbams-access
user2341069
user2341069

Reputation: 389

VBA - provider cannot be found with accdb format

With the below code i get the following error when ran provider cannot be found, The below code is copied and edited from the net, It previously used .mdb files but I tried to change it to .accdb because thats the format I need it in. I'm trying to make a macro that when ran copies certain cells into a database, adding to it.

I get this error 

 run-time error "3706"
 Provider cannot be found it may not be properly installed

-

Const TARGET_DB = "testdb.accdb"

Sub AlterOneRecord()
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim lngRow As Long
   Dim lngID As String
   Dim j As Long
   Dim sSQL As String

   'determine the ID of the current record and define the SQL statement
   lngRow = ActiveCell.Row
   lngID = Cells(lngRow, 1).Value

   sSQL = "SELECT * FROM tblPopulation WHERE PopID = " & lngID

   Set cnn = New ADODB.Connection
   MyConn = ThisWorkbook.path & Application.PathSeparator & TARGET_DB

   With cnn
     .Provider = "Provider=Microsoft.ACE.OLEDB.12.0;"
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=sSQL, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

   'Load contents of modified record from Excel to Access.
   'do not load the ID again.
   For j = 2 To 7
      rst(Cells(1, j).Value) = Cells(lngRow, j).Value
   Next j
   rst.Update

   ' Close the connection
   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

Is there an easier way to do this? or should I try and fix this?

Upvotes: 2

Views: 12081

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

What you are missing is complete connection string to your data base file.

(More about connection string)

I give you a rough idea which usually works with my code:

remove this line in your code:

.Provider = "Provider=Microsoft.ACE.OLEDB.12.0;"

instead use this one: 

.ConnectionString= "Provider=Microsoft.ACE.OLEDB.12.0;"

or you could use this one instead:

.Provider = "Microsoft.ACE.OLEDB.12.0"

For further information you could see this w3schools website.

Upvotes: 4

Related Questions