INSERT TO syntax error

[carterlw12]

Importing into staging table using a form button and it's successful but inserting that into the actual table that I want the data to reside in is not working. I'm getting syntax error for my INSERT TO statement.

Private Sub cmdImport_Click()
Dim FSO As New FileSystemObject
Dim strSQL As String

[B]strSQL = "INSERT INTO ADP_Person_Import_tbl (GlobalID, RecordEffDate, BirthDate, HireDate, ReHireDate, ServiceDate, SeniorityDate, TermDate, ShortName, FirstName, LastName, MI, Supervisor, EmpStatus, StatusEffDate, FT/PT, Reg/Temp, HomePhone, WorkPhone, PersonalEmail, WorkEmail, Region, Country, Division, Location, Dept, Job, OfficerCode, Union, FLSAInd, ADPPayGroup, TipInd, SpecialGroup, BaseWageRate, BaseWageEffDate, WeeklyHours, ADPFile, PTOPrem, Language, Address, City, State, ZipCode, Country2, JobDept, FileName) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23, F24, F25, F26, F27, F28, F29, F30, F31, F32, F33, F34, F35, F36, F37, F38, F39, F40, F41, F42, F43, F44, F45, F46 FROM ADP_staging_tbl;"[/B]


If Nz(Me.txtFilepath, "") = "" Then
    MsgBox "No file has been selected. Please select a file."
    Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFilepath, "")) Then

    DoCmd.TransferText acImportDelim, , "ADP_staging_tbl", Me.txtFilepath, False

    CurrentDb.Execute "UPDATE ADP_staging_tbl SET F46 = '" & _
    Right(txtFilepath, 26) & "' WHERE F46 IS NULL", dbFailOnError
    
    

    [B]CurrentDb.Execute strSQL, dbFailOnError[/B]
    
    

    CurrentDb.Execute "DELETE FROM ADP_staging_tbl"
    
    

    MsgBox "Import Successful!"
txtFilepath.Value = ""
    Exit Sub
    



End If

End Sub

 

[cheekybuddha]

Hi,

Wrap your field names with illegal characters in square brackets:

strSQL = "INSERT INTO ADP_Person_Import_tbl (GlobalID, ... , [FT/PT], [Reg/Temp], ...

hth,

d

 

[carterlw12]

I tried that. Same error "3134 Syntax error in INSERT INTO statement".

 

[CJ_London]

Always helpful if you provide the error description - I suspect it is to do with you using non alphanumeric characters in your field names and also using reserved words

FT/PT, Reg/Temp, Union

As a patch, try putting square brackets round them, but better to change them

 

[carterlw12]

Ahhhhhhh it was the "union". I didn't realize that was "illegal". That worked!!! Thank you!