Help INSERT INTO Statement, Using VB in Access 2003 Module

[ALaurie10]

Hi
I have code below that enables a user to browse for an excel file, import the spreadsheet data, and dump it into a table . I keep receiving 3134 syntax error for "INSERT INTO" statement. Could someone please help me out? Also when I copy and paste this code into a module in a new database, I get an error message for the "Dim dlgPickFiles As Office.FileDial og" statement. I didn't get this error message in the old database, and don't understand why the code for browsing for data runs in the old database and not in the new database?

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

[kadghar]

Hi
(...) message for the "Dim dlgPickFiles As Office.FileDial og" statement. I didn't get this error message in the old database, and don't understand why the code for browsing for data runs in the old database and not in the new database?

Thanks

Some VBA versions dont allow you to declare some kind of objects, if you're not using the name, then just put the file dialog in the with:

With Application.Fil eDialog(msoFile DialogFilePicke r)
.filters(...)
.selection(...)
end with

That'll do

[ALaurie10]

What about the syntax error for the "INSERT INTO" statement?

[Killer42]

Is it possible you misspelled a field name? Or perhaps the import is not setting the field names correctly.