vba - 没有使用 ADO 将数据从 Excel 导入到 Access

问题描述

我在 Excel 中有一个可以写入 Excel 工作表的表单。在下面的 VBA 中，我已请求单元格更新 Access 数据库。

上传数据没有错误，但是当我转到我的访问表时，没有数据存在。

访问表：（ 点击放大）

Sub Export_Data()
    Dim cnn As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim dbPath, x As Long, i As Long, nextrow As Long

    On Error GoTo errHandler: 'add error handling

    'Variables for file path and last row of data
    dbPath = Sheet19.Range("I3").Value
    nextrow = Cells(Rows.Count, 1).End(xlUp).Row
    Set cnn = New ADODB.Connection 'Initialise the collection class variable

    If Sheet18.Range("A2").Value = "" Then  'Check for data
        MsgBox " Add the data that you want to send to MS Access"
        Exit Sub
    End If

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    Set rst = New ADODB.Recordset 'assign memory to the recordset 

    rst.Open Source:="SELECT * FROM [ARF Data Log]", ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic
    Options = adCmdOpenTable

    'you now have the recordset object; add the values to it
    For x = 2 To nextrow
        rst.AddNew
            For i = 1 To 29
                rst(Cells(1, i).Value) = Cells(x, i).Value
            Next i
        rst.Update
    Next x

    rst.Close         'close the recordset
    cnn.Close         'close the connection
    Set rst = Nothing 'clear memory
    Set cnn = Nothing

    'communicate with the user
    MsgBox " The data has been successfully sent to the access database"
    Application.ScreenUpdating = True  'Update the sheet
    Sheet19.Range("h7").Value = Sheet19.Range("h8").Value + 1 'show the next ID
    Sheet18.Range("A2:ac1000").ClearContents  'Clear the data
    On Error GoTo 0
    Exit Sub
errHandler:

    Set rst = Nothing  'clear memory
    Set cnn = Nothing
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Export_Data"
End Sub

标签： vbaexcelms-accessado

您需要指定要更新的字段。这可以使用!或使用来完成.Fields。如果不指定，则可以使用列的索引。

- 和!

Sub DataPopulation()

    Dim myConn As New ADODB.Connection
    Dim DBS As ADODB.Recordset

    Set myConn = CurrentProject.Connection
    Set DBS = New ADODB.Recordset

    DBS.Open "SomeDB", myConn, adOpenKeyset, adLockOptimistic

    DBS.AddNew
    DBS!StudentNumber = 1
    DBS!StudentName = "SomeName"
    DBS!Grade = 10

    DBS.AddNew
    DBS!StudentNumber = 2
    DBS!StudentName = "SomeFamilyName"
    DBS!Grade = 10

    DBS.Update
    DBS.Close

    Set DBS = Nothing
    Set myConn = Nothing

End Sub

- 有.Fields：

Do While Len(Range("A" & r).Formula) > 0
    With rs
        .AddNew
        .Fields("Commodity #") = Range("A" & r).Value
        .Update
    End With
    r = r + 1   
Loop

- 带索引：如果使用字段的数字索引，则它们从 1 开始到字段的计数。rst(i)如果您至少有i列，则在您的情况下应该没问题。在下面的示例中，有 3 列可用：

For tblRow = 1 To 10
    DBS.AddNew
    For tblCol = 1 To 3
        DBS(tblCol) = "Row: " & tblRow & " Col: " & tblCol
    Next
Next

vba - 没有使用 ADO 将数据从 Excel 导入到 Access

问题描述

解决方案

推荐阅读