使用VBA将数据导入Access

Sub ImportDataToAccess()
    Dim db As Object
    Dim rs As Object
    Dim strSQL As String
    Dim rng As Range
    Dim cell As Range
    
    ' 设置数据库路径和表名
    Dim dbPath As String
    Dim tableName As String
    dbPath = "C:\Path\To\Your\Database.accdb"
    tableName = "YourTableName"
    
    ' 打开数据库连接
    Set db = CreateObject("Access.Application")
    db.OpenCurrentDatabase dbPath
    
    ' 清空目标表中的数据
    strSQL = "DELETE FROM " & tableName
    db.CurrentDb.Execute strSQL
    
    ' 获取要导入的数据范围
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:B10") ' 修改为你的数据范围
    
    ' 遍历数据范围，逐行插入数据
    For Each cell In rng
        strSQL = "INSERT INTO " & tableName & " (Field1, Field2) VALUES ('" & cell.Value & "', '" & cell.Offset(0, 1).Value & "')"
        db.CurrentDb.Execute strSQL
    Next cell
    
    ' 关闭数据库连接
    db.CloseCurrentDatabase
    Set db = Nothing
    
    MsgBox "数据导入完成！"
End Sub