VBA - 使用 accdb 格式找不到提供程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18206396/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - provider cannot be found with accdb format
提问by user2341069
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.
使用下面的代码，当找不到运行的提供程序时，我收到以下错误，下面的代码是从网上复制和编辑的，它以前使用过 .mdb 文件，但我尝试将其更改为 .accdb，因为这是我需要的格式. 我正在尝试制作一个宏，在运行时将某些单元格复制到数据库中，并添加到数据库中。
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?
有没有更简单的方法来做到这一点？或者我应该尝试解决这个问题？
回答by Kazimierz Jawor
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;"
.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.
有关更多信息，您可以查看此 w3schools 网站。
