运行时错误使用excel vba将excel 2010中的数据导出到Access表2010时无法识别的数据库 [英] Run time error Unrecognized database while exporting data from excel 2010 to Access table 2010 using excel vba

问题描述

我在Access 2010数据库中有一个表，其列名与excel表中的列名称相同。我必须删除Access表数据内容，然后将数据从我的宏列入excel 2010表中。到现在我试图看/测试我是否可以将我的excel数据抽空到Access中的表。一旦我得到这个工作，我可以得到'删除内容，然后转储excel数据'工作。

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.

这是我的excel vba宏代码：

Here's my excel vba macro code:

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

我添加了工具 - >参考并选择Microsoft ActiveX数据对象6.0对象库

I added Tools--> References and selecting Microsoft ActiveX Data Objects 6.0 Object Library.

我得到运行时错误'-2147467259（80004005）'：
无法识别的数据库格式'C：\Users\shress2\\ \\ Documents\TSS_Certification\TSS_Certification.accdb

I m getting Run-Time error' -2147467259(80004005)': Unrecognized database format 'C:\Users\shress2\Documents\TSS_Certification\TSS_Certification.accdb

有什么原因吗？我该如何解决？谢谢。

Any reasons why? And how do I fix it? Thanks.

推荐答案

您正在连接到.accdb数据库文件。这是Access 2007/2010格式。

从Access 2003时代起，为mdb文件构建了 Microsoft.Jet.OLEDB.4.0 提供程序。 >
我不认为你可以连接该提供商（无法识别文件格式）。

You are connecting to an .accdb database file. It's a Access 2007/2010 format.
The Microsoft.Jet.OLEDB.4.0 provider 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).

尝试更改您的连接字符串以使用

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;

这篇关于运行时错误使用excel vba将excel 2010中的数据导出到Access表2010时无法识别的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！