Excel VBA ADO导入600万行CSV到Access报错求助

最终可用SQL语法 #

createSQL = "SELECT * INTO newlyСreatedTableName FROM [fileName.csv] IN 'folderPath' [""]Text;HDR=YES;FMT=Delimited[""]"

问题描述 #

需将含600万行数据的CSV文件，通过Excel中运行的VBA ADO代码导入Access数据库，执行时触发错误号**-2147467259**，提示“参数错误”。

原有错误代码 #

Function getAccCN(ByVal dbFullPath As String) As Object
    Set getAccCN = CreateObject("ADODB.Connection")
    getAccCN.connectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & dbFullPath
End Function

Function createSQL() As String
    createSQL = "Select * INTO [" & _ 
                fileName & "] FROM [Data source = " & _
                repFile.ParentFolder.Path & _ 
                "; Extended Properties=""Text;HDR=Yes;FMT=Delimited""]." & _ 
                repFile.Name ' repFile 是通过Scripting.FileSystemObject获取的csv文件对象

    Debug.Print createSQL ' 输出内容如下:
     ' Select * INTO [Classification] FROM [Data source = \\av-fs01.av.local\profiles$\...\Project IQVIA\; Extended Properties="Text;HDR=Yes;FMT=Delimited"].Classification.csv
     ' *.accdb、表名与*.csv的基础名称均为"Classification"
End Function

Function uploadCSV() As Boolean
Dim CN As Object
    Set CN = getAccCN(repFile.ParentFolder.Path & "\" & baseFileName & ".accdb")
    CN.Open
    CN.Execute createSQL() ' 此处触发错误
    Exit Function
ErrHandler:
    Debug.Print Err.Number ' 返回 -2147467259 提示"Wrong argument."
       ' 错误说明：参数类型错误、超出范围或相互冲突
End Function

数据源信息 #

CSV表头包含Patient ID、Gender、Year of Birth、Year of Death等字段，首行对应为具体数据值。

内容的提问来源于stack exchange，提问作者Mik