用vba实现数据表结构的转换

Sub ConvertTableStructure()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    
    ' 创建数据库连接
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\database.accdb;"
    
    ' 获取源数据表结构
    strSQL = "SELECT * FROM SourceTable WHERE 1=0;"
    Set rs = conn.Execute(strSQL)
    
    ' 创建目标数据表
    strSQL = "CREATE TABLE TargetTable ("
    For i = 1 To rs.Fields.Count
        strSQL = strSQL & rs.Fields(i - 1).Name & " " & rs.Fields(i - 1).Type & ","
    Next i
    strSQL = Left(strSQL, Len(strSQL) - 1) & ");"
    conn.Execute strSQL
    
    ' 复制数据
    strSQL = "INSERT INTO TargetTable SELECT * FROM SourceTable;"
    conn.Execute strSQL
    
    ' 关闭数据库连接
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub