使用Excel VBA将表从SQL导入Access

Sub ImportTableFromSQLToAccess()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim strConn As String
    Dim strAccessDBPath As String
    Dim strTableName As String
    
    ' 设置SQL连接字符串
    strConn = "Provider=SQLOLEDB;Data Source=SQLServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password;"
    
    ' 设置Access数据库路径和表名
    strAccessDBPath = "C:\Path\To\Access\Database.accdb"
    strTableName = "TableName"
    
    ' 创建连接对象
    Set conn = CreateObject("ADODB.Connection")
    
    ' 打开数据库连接
    conn.Open strConn
    
    ' 构建SQL查询语句
    strSQL = "SELECT * FROM YourSQLTable;"
    
    ' 创建记录集对象
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 执行SQL查询
    rs.Open strSQL, conn
    
    ' 创建Access连接字符串
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strAccessDBPath
    
    ' 将记录集导入Access表
    rs.Save strTableName, strConn, 2 ' 2表示将记录追加到目标表
    
    ' 关闭记录集和连接
    rs.Close
    conn.Close
    
    ' 释放对象
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "表已成功导入Access数据库。"
End Sub