使用 excel vba 将数据从 excel 2010 导出到 Access table 2010 时出现运行时错误无法识别的数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10620860/
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
Run time error Unrecognized database while exporting data from excel 2010 to Access table 2010 using excel vba
提问by Nemo
I have a table in Access 2010 db with same column names as the column names in excel sheet. I have to delete Access table data content before pumping data from my macro enalbed excel 2010 sheet into it.For now I m trying to see/test if I could pump my excel data to empty table in Access. Once I get this working, I could get 'delete content before dumping excel data' working.
我在 Access 2010 db 中有一个表，其列名与 Excel 表中的列名相同。在将数据从我的宏启用 excel 2010 表中泵入它之前，我必须删除 Access 表数据内容。现在我正在尝试查看/测试是否可以将我的 excel 数据泵入 Access 中的空表。一旦我开始工作，我就可以“在转储excel数据之前删除内容”工作。
Here's my excel vba macro code:
这是我的 excel vba 宏代码：
Sub ADOFromExcelToAccess()
'exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "t_certification_051512", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Role") = Range("A" & r).Value
.Fields("Geo Rank") = Range("B" & r).Value
.Fields("Geo") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
结束子
I added Tools--> References and selecting Microsoft ActiveX Data Objects 6.0 Object Library.
我添加了 Tools--> References 并选择了 Microsoft ActiveX Data Objects 6.0 Object Library。
I m getting Run-Time error' -2147467259(80004005)': Unrecognized database format 'C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb
我收到运行时错误'-2147467259(80004005)'：无法识别的数据库格式'C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb
Any reasons why? And how do I fix it? Thanks.
有什么原因吗？我该如何解决？谢谢。
回答by Steve
You are connecting to an .accdb database file. It's a Access 2007/2010 format.
The Microsoft.Jet.OLEDB.4.0provider has been built for mdb files from Access 2003 era.
I don't think you can connect with that provider (It fails to recognize the file format).
您正在连接到 .accdb 数据库文件。这是一种 Access 2007/2010 格式。
该Microsoft.Jet.OLEDB.4.0提供程序是为 Access 2003 时代的 mdb 文件构建的。
我认为您无法与该提供商建立联系（它无法识别文件格式）。
Try to change you connection string to use
尝试更改您要使用的连接字符串
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb;"
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb;"
