在VBA中将数据库中的值插入到本地表

Sub InsertDataFromDatabase()

    ' 创建连接对象
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' 设置连接字符串，根据实际情况修改数据库路径和名称
    Dim connStr As String
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\database.accdb;"
    
    ' 打开数据库连接
    conn.Open connStr
    
    ' 创建记录集对象
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 设置SQL查询语句，根据实际情况修改表名和字段名
    Dim sql As String
    sql = "SELECT * FROM TableName;"
    
    ' 执行查询
    rs.Open sql, conn
    
    ' 创建本地表对象
    Dim localTable As Object
    Set localTable = CurrentDb.TableDefs("LocalTableName")
    
    ' 清空本地表数据
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE FROM LocalTableName;"
    DoCmd.SetWarnings True
    
    ' 将查询结果插入到本地表
    Do Until rs.EOF
        localTable.AddNew
        localTable.Fields("FieldName1").Value = rs.Fields("ColumnName1").Value
        localTable.Fields("FieldName2").Value = rs.Fields("ColumnName2").Value
        ' 继续添加其他字段...
        localTable.Update
        rs.MoveNext
    Loop
    
    ' 关闭记录集和连接
    rs.Close
    conn.Close
    
    ' 释放对象
    Set rs = Nothing
    Set conn = Nothing

End Sub