Help with debugging code for Access 2003

[ALaurie10]

Hi have code below that enables a user to browse for data and import spreadsheet data. I keep receiving error message 3134 syntax error for "INSERT INTO" statement. Could someone please help me out? see bold below

Thanks

Private Sub btnImportData_Click()
 
Dim dlgPickFiles As Office.FileDialog
Dim strFileName As String
 
Set dlgPickFiles = Application.FileDialog(msoFileDialogFilePicker)
 
With dlgPickFiles
     .AllowMultiSelect = False
     With .Filters
        .Clear
        .Add "Excel files", "*.xls"
    End With
      .Show
      strFileName = .SelectedItems(1)
      End With
 
    Set dlgPickFiles = Nothing
 
    With DoCmd
        .TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
            "tblTempLink", strFileName, False, "Data Output!A2:C2"
        .RunSQL "INSERT INTO TBL Import_Excel (Sponsor, Owner, Project_Name) " & _
            "SELECT tblTempLink.Sponsor, tblTempLink.Owner, tblTempLink.Project_Name " & _
            "FROM tblTempLink;"
        .DeleteObject acTable, "tblTempLink"
    End With
 
End Sub

[nico5038]

Try:

INSERT INTO [TBL Import_Excel] (Sponsor, Owner, Project_Name)

The [ and ] are mandatory when you have a space in the name, guess you now know why I don't use spaces in (table/field) names :-)

Nic;o)

[ALaurie10]

Thanks Nico,
but I am still getting that syntax error message. I even tried enclosing my field with brackets. I am totally lost.

[nico5038]

In a case like this I place a breakpoint (by clicking in the left ruler besides the assign statement.
Then trigger the code to run and when at the assign statement take the stringing part and type in the immediate window at the bottom a question mark followed by this code. Then by prssing [Enter] you get the entire build string.
Now copy/paste this string in a new query and try to execute that in the query editor. Now the cursor will be positioned on the error part.

Nic;o)

[FishVal]

Hi have code below that enables a user to browse for data and import spreadsheet data. I keep receiving error message 3134 syntax error for "INSERT INTO" statement. Could someone please help me out? see bold below

Thanks

Private Sub btnImportData_Click()
 
Dim dlgPickFiles As Office.FileDialog
Dim strFileName As String
 
Set dlgPickFiles = Application.FileDialog(msoFileDialogFilePicker)
 
With dlgPickFiles
     .AllowMultiSelect = False
     With .Filters
        .Clear
        .Add "Excel files", "*.xls"
    End With
      .Show
      strFileName = .SelectedItems(1)
      End With
 
    Set dlgPickFiles = Nothing
 
    With DoCmd
        .TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
            "tblTempLink", strFileName, False, "Data Output!A2:C2"
        .RunSQL "INSERT INTO TBL Import_Excel (Sponsor, Owner, Project_Name) " & _
            "SELECT tblTempLink.Sponsor, tblTempLink.Owner, tblTempLink.Project_Name " & _
            "FROM tblTempLink;"
        .DeleteObject acTable, "tblTempLink"
    End With
 
End Sub

Hi, there. Your [tblTempLink] has default field names F1, F2, F3 as soon as you've linked it to XL file with HasFieldNames:=False.

Kind regards,
Fish