access批量导入数据库

Sub ImportDataFromCSV()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim fso As Scripting.FileSystemObject
    Dim ts As Scripting.TextStream
    Dim line As String
    Dim values() As String
    Dim i As Integer
    
    ' 打开数据库
    Set db = CurrentDb
    
    ' 创建一个新的表
    Set tdf = db.CreateTableDef("NewTable")
    tdf.Fields.Append tdf.CreateField("Field1", dbText)
    tdf.Fields.Append tdf.CreateField("Field2", dbInteger)
    db.TableDefs.Append tdf
    
    ' 打开记录集
    Set rs = db.OpenRecordset("NewTable")
    
    ' 打开 CSV 文件
    Set fso = New Scripting.FileSystemObject
    Set ts = fso.OpenTextFile("C:\path\to\your\file.csv", ForReading)
    
    ' 逐行读取 CSV 文件并插入数据
    Do While Not ts.AtEndOfStream
        line = ts.ReadLine
        values = Split(line, ",")
        rs.AddNew
        For i = 0 To UBound(values)
            rs.Fields(i).Value = values(i)
        Next i
        rs.Update
    Loop
    
    ' 关闭文件和记录集
    ts.Close
    rs.Close
    
    ' 清理对象
    Set ts = Nothing
    Set fso = Nothing
    Set rs = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Sub