从FTP获取文件导入Access数据库时遇编译错误求助

嘿，我帮你揪出了编译错误的根源，顺便梳理下代码里其他可能让你踩坑的地方：

1. 触发编译错误的直接原因 #

你在拼接SQL语句时写了aryMyData()——这是语法错误！数组变量不能加空括号来引用，空括号是用来声明数组或者调用无参数过程的写法，这里明显是手误多写了，删掉它就能解决这个编译错误。

错误代码片段：

strSQL = strSQL & " VALUES(" & aryMyData(0) & ", " & aryMyData(1) & ", " & aryMyData(2) & ", " & aryMyData(3) & ", " & aryMyData(4) & ", " & aryMyData(5) & ", " & aryMyData(6) & ", #" & aryMyData(7) & "#, " & aryMyData(8) & ", " & aryMyData(9) & ", " & aryMyData(10) & ", " & aryMyData(11) & ", " & aryMyData() & ", " & aryMyData(12) & ");"

修正后：

strSQL = strSQL & " VALUES(" & aryMyData(0) & ", " & aryMyData(1) & ", " & aryMyData(2) & ", " & aryMyData(3) & ", " & aryMyData(4) & ", " & aryMyData(5) & ", " & aryMyData(6) & ", #" & aryMyData(7) & "#, " & aryMyData(8) & ", " & aryMyData(9) & ", " & aryMyData(10) & ", " & aryMyData(11) & ", " & aryMyData(12) & ");"

2. 代码里其他需要修复的问题 #

除了编译错误，你的代码还有几个逻辑和语法问题，不修复的话导入会失败：

• 字符串字段没加单引号：Access SQL里字符串类型的字段必须用单引号包裹，否则会把字符串当成变量/数字处理，导致语法错误。比如[Delivery Note]这类字段要写成'" & aryMyData(0) & "'
• 空行判断逻辑反了：你写的If aryMyData(0) = "" Then，意思是只有第一列是空的行才插入数据，这和导入有效数据的逻辑完全相反，应该改成If aryMyData(0) <> "" Then
• 避免空SQL执行：如果遇到空行，strSQL会是空值，调用DoCmd.RunSQL strSQL会直接报错，要把执行SQL的代码放到If块内部，只有生成有效SQL时才执行
• 数组索引越界风险：要确保每一行的CSV数据能分割出足够的元素（你用到了索引0到12），可以在Split后加判断：If UBound(aryMyData) >=12 Then再处理

3. 修正后的完整代码 #

Public Function ImportCSVFile()
    Dim strTextLine As String
    Dim aryMyData() As String
    Dim strSQL As String
    
    Open "C:\Users\Electronica\Documents\3000.csv" For Input As #1
    Do While Not EOF(1) ' Loop until end of file.
        Line Input #1, strTextLine ' Read line into variable.
        aryMyData = Split(strTextLine, ",") 'Split text into array by comma
        
        ' 先判断数组元素数量足够，且第一列不为空（过滤无效行）
        If UBound(aryMyData) >= 12 And aryMyData(0) <> "" Then
            ' 字符串字段用单引号包裹，日期字段保留#包裹
            strSQL = "INSERT INTO Tbl_Theo_Stock([Delivery Note], [Delivery Date], [Delivered QTY], [LABELNR], [SAP PO], [SAP POS], [OPEN QTY], [Wished Date], [Materialnr], [reference], [description], [vendor]) "
            strSQL = strSQL & " VALUES('" & aryMyData(0) & "', '" & aryMyData(1) & "', " & aryMyData(2) & ", '" & aryMyData(3) & "', '" & aryMyData(4) & "', " & aryMyData(5) & ", " & aryMyData(6) & ", #" & aryMyData(7) & "#, '" & aryMyData(8) & "', '" & aryMyData(9) & "', '" & aryMyData(10) & "', '" & aryMyData(11) & "', '" & aryMyData(12) & "');"
            
            Debug.Print strSQL
            DoCmd.RunSQL strSQL
        End If
    Loop
    Close #1
    Debug.Print "导入完成！"
End Function

4. 额外提示 #

• 如果CSV里的字段本身包含逗号或者单引号，Split方法会直接出错，这种情况建议使用专门的CSV解析逻辑，不过你说文件体积小，暂时可以先这么用
• 可以考虑用参数化查询代替字符串拼接，既能避免SQL注入风险，也能省去字符串转义的麻烦

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