Results 1 to 3 of 3
  1. #1
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85

    Syntax Error with DoCmd.RunSQL

    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...

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Because Filenam is a string, it needs to be in quotes. Using single quotes, it would look like this:

    DoCmd.RunSQL ("UPDATE tempData SET fileName = '" & filnam & "' ;")

    HTH

    John

  3. #3
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    That's twice today John, I owe you a beer, or at least my paycheck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DoCmd.OpenForm Syntax Error
    By alsoto in forum Forms
    Replies: 3
    Last Post: 02-29-2012, 01:14 PM
  2. my first DoCmd.RunSQL
    By broecher in forum Programming
    Replies: 4
    Last Post: 11-05-2010, 09:35 PM
  3. docmd.runsql update
    By Mitch_Aus in forum Programming
    Replies: 2
    Last Post: 10-05-2010, 09:45 AM
  4. Problem with DoCmd.RunSQL
    By Peter01 in forum Programming
    Replies: 2
    Last Post: 09-10-2009, 07:11 PM
  5. docmd.runsql not working
    By Rameez in forum Programming
    Replies: 3
    Last Post: 08-07-2009, 10:07 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums