将Excel数据导入Access时发生SQL错误

Question

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

基地有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:UsersUsuárioDesktopDatabasePerfildePerda.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

Answer 1

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

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

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

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

如果多次运行此操作，则需要删除以前的链接表。

问候，