将Excel工作表导出到访问表（.accdb） [英] Export Excel Worksheet to Access Table (.accdb)

问题描述

我的Excel中有一个宏绑定到我的一个工作表上的命令按钮。点击后，我试图将我的工作表FeedSamples中的数据导出到一个名为ImportedData的Access数据库表。

I have a macro in Excel tied to a command button on one of my worksheets. When clicked, I'm trying to have the data from my worksheet "FeedSamples" be exported into an Access Database Table called "ImportedData".

任何人都可以帮我吗？我从网上尝试过多个例子，没有运气。这是我现在所在，但继续收到运行时错误3343'：无法识别的数据库格式'filePath\FeedSampleResults.accdb

Can anyone assist me? I've tried multiple examples from the net with no luck. This is what I have right now but keep receiving "Run-time error '3343': Unrecognized database format 'filePath\FeedSampleResults.accdb

Dim db As Database
Dim rs As Recordset
Dim r As Long
Set db = OpenDatabase("filePath\FeedSampleResults.accdb")
Set rs = db.OpenRecordset("ImportedData", dbOpenTable)
r = 2
Do While Len(Worksheets("FeedSamples").Range("A" & r).Formula) > 0
    With rs
        .AddNew
        .Fields("REPTNO") = Worksheets("FeedSamples").Range("B" & r).value
        .Update
    End With
    r = r + 1
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

一旦我完成这个任务，我需要使用代码将Access Table导出到dBase文件中。

Once I get this accomplished, I need to code to have the Access Table export the Data into a dBase file.

推荐答案

这是使用ADO的代码，您需要在Data S中设置访问数据库的完整路径myC。

Here's the code using ADO. You need to set the full path of your access database in Data Source.

Sub ExcelToAccessAdo()

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, row As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
            "Data Source=filePath\FeedSampleResults.accdb;"

    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "ImportedData", cn, adOpenKeyset, adLockOptimistic, adCmdTable

    row = 3    ' the start row in the worksheet
    Do While Not IsEmpty(Worksheets("FeedSamples").Range("A" & row))

        With rs
            .AddNew    ' create a new record
            .Fields("REPTNO") = Worksheets("FeedSamples").Range("A" & row).Value
            .Update
        End With
        row = row + 1
    Loop

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

这篇关于将Excel工作表导出到访问表（.accdb）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！