I'm working on some code to allow users to import excell files without using the import tool, stupid I know.
Code:
Option Compare Database
Private Sub Command0_Click()
MsgBox (" Be careful, this import will overwrite any data stored in the tempData table.")
DoCmd.RunSQL ("Drop TABLE tempData;")
Dim fd As FileDialog
Dim objfl As Variant
Dim filnam As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.ButtonName = "Select"
.AllowMultiSelect = False
.Title = "Choose Transactions file to import"
.InitialView = msoFileDialogViewDetails
.Show
For Each objfl In .SelectedItems
filnam = objfl
Next objfl
On Error GoTo 0
End With
MsgBox (filnam)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tempData", filnam, True
DoCmd.RunSQL ("ALTER TABLE tempData ADD fileName string, importDate date;")
DoCmd.RunSQL ("UPDATE tempData SET importDate = NOW() ;")
DoCmd.RunSQL ("UPDATE tempData SET fileName =" & filnam & " ;")
Set fd = Nothing
End Sub
However I continue to get a Syntax error on this line
Code:
DoCmd.RunSQL ("UPDATE tempData SET fileName =" & filnam & " ;")
I can't seem to figure out what the problem is, the Msgbox provides the correct filename, and the RunSQL line above works fine...