将 Excel 数据导入 Access 时出现 SQL 错误

黛博拉·罗查

我正在使用 vba 在 access 中导入 excel，但出现以下错误。我相信错误出在 sql 中，是否与我声明列名的方式有关？

base 有 31 列

将所有列作为字符串放在访问中的基数中，只是没有的日期，只是为了测试这是否是错误

谁能告诉我错误是什么？

Code:

Option Explicit

Private Sub CommandButton1_Click()


Dim MDB  As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim FD   As ADODB.Field
Dim SQL   As String



Dim W  As Worksheet



Dim UltCel As Range



Dim Ln As Long
Dim Col As Integer





 Set W = Sheets("Plan1")
 Ln = 2
 Col = 1
 W.Select
 W.Range("A1").Select

 MDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Usuário\Desktop\DatabasePerfildePerda.mdb;Persist Security Info=False;"

 Set UltCel = W.Cells(W.Rows.Count, 1).End(xlUp)



 Do While Ln <= UltCel.Row

 SQL = "INSERT INTO tbdados3"
 SQL = SQL & "(Mes por Extenso, Secao Industrial, Tipo, by-pass, Sigla, Motivo, Classificacao, Responsavel pela Solucao, Area, Causa Raiz, Turno, Data Inicio, Hora Inicio, Data Fim, Hora Fim, Tempo, Taxa Alimetacao, Reducao Alimentacao, TAG, Componente, Ponto de Causa, Observacoes, Usuario, Unidade, Familia, Ano, Mês, Ano Mes, Dia, Inicio da ocorrencia, Final da Ocorrencia, Frequencia de Falha)"
 SQL = SQL & "values"
 SQL = SQL & "('" & W.Cells(Ln, Col).Value & "', "
 SQL = SQL & "'" & W.Cells(Ln, Col + 1).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 2).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 3).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 4).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 5).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 6).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 7).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 8).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 9).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 10).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 11).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 12).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 13).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 14).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 15).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 16).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 17).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 18).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 19).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 20).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 21).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 22).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 23).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 24).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 25).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 26).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 27).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 28).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 29).Value & "' , "
 SQL = SQL & "'" & W.Cells(Ln, Col + 30).Value & "' ) "


RS.Open SQL, MDB



Ln = Ln + 1
Col = 1
Application.StatusBar = Ln
DoEvents

 Loop

 MDB.Close







Set W = Nothing
Set MDB = Nothing
Set RS = Nothing
Set FD = Nothing
Set UltCel = Nothing



MsgBox "Processo Concluido"








End Sub

苹果核

每当您在 Access 中处理 SQL 中的数据时，经验法则是任何文本数据都用单引号括起来，任何日期数据都用散列括起来，而数字数据不需要用任何东西括起来。您可能希望Debug.Print SQL在构建代码后在代码中放入 a以在即时窗口中查看您尝试执行的内容。

此外，字段名称中包含空格会导致问题，因此您应该重命名不带空格的字段，或者将字段名称括在方括号中。

可能更好的选择是链接到 Excel 文件（使用 TransferSpreadsheet），然后将数据插入表中：

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "tblTemp", "J:\Downloads\test.xlsx", True
CurrentDb.Execute "INSERT INTO Table3 ( F1, F2, F3 ) " _
    & " SELECT Customer, Product, Order " _
    & " FROM tblTemp;"

如果您多次运行，则需要删除之前的链接表。

问候，

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。