VBA将Excel电子表格逐行导入Access

Sub ImportToAccess()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim dbPath As String
    Dim tableName As String
    Dim fieldNames As String
    Dim fieldValues As String
    Dim i As Integer
    
    '设置数据库路径和表名
    dbPath = "C:\path\to\your\access\database.accdb"
    tableName = "YourTableName"
    
    '创建数据库连接
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    
    '创建新表
    conn.Execute "CREATE TABLE " & tableName & " (Field1 TEXT, Field2 TEXT)"
    
    '循环遍历Excel表格中的数据
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        fieldNames = "Field1, Field2"
        fieldValues = "'" & Cells(i, 1).Value & "', '" & Cells(i, 2).Value & "'"
        
        '将数据插入到Access表格中
        conn.Execute "INSERT INTO " & tableName & " (" & fieldNames & ") VALUES (" & fieldValues & ")"
    Next i
    
    '关闭数据库连接
    conn.Close
    Set conn = Nothing
End Sub