none
I am getting "Run time error 3131 syntax error in FROM clause when i am trying to delete records in Access table based on data in another table

    Question

  • Hi People

    Actually i am importing multiple xl files in temporary table called "TEMP". There is another table is same access db called "Main" both tables have same fields neary 20 including 1 field super department. I want when this xl files are imported in TEMP table than super department values should be matched with the same in main table and than if there are any rows having common super department (ID) than this rows should be deleted from main table and than data from TEMP table should be copied to main table. value in super department field starts with letter and numbers for example SD123-COTTON. I am using following VBA sql in access module.

    End Sub

    I am getting "Run time error 3131 syntax error in FROM clause"

    • Edited by zaveri cc Tuesday, November 16, 2010 7:15 PM
    Saturday, November 06, 2010 8:55 PM

Answers

  • strsql = "DELETE FROM Main " _
    & "WHERE [super department] in (SELECT temp.[super department] " _
    & "FROM temp INNER JOIN Main ON temp.[super department]=Main.[super department] " _
    & "WHERE temp.season=Main.season)"
    

    Hi, try this.

    And about your second question, I think you should start a new thread. If I'm not mistaken you've already done it.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Edited by VanderghastModerator Tuesday, November 09, 2010 1:12 PM A double quote was apparently missing
    • Marked as answer by Bruce Song Friday, November 12, 2010 5:06 AM
    Sunday, November 07, 2010 6:35 PM

All replies

  • Hi zaveri,

    delete all extra commas. 

    strsql = "Delete from Main " _
    & "where [super department] in (select temp.[super department] " _
    & "from temp inner join Main on temp.[super department]=Main.[super department])"
    


    Andrey V Artemyev | Saint-Petersburg, Russia
    Saturday, November 06, 2010 9:20 PM
  • Hi Andery,

    Thanks for help, your suggesiton works bang on target. One more thing if i want to add one more column "season" with super department than what changes should i make to above code. I mean now 2 columns(super dept & season) from both the table should be compared and than matching rows should be deleted from main table

    Sunday, November 07, 2010 1:07 AM
  • Hi Andrey,
    If you get chance can you please resolve this issue also. i have a sub routine defined in access module through which i am importing 1 xl file at a time in access table by creating open file dialog box and using couple of messagebox. I am calling this routine by creating a button in Excel and when i click that button first msgbox asking "whether you want to import all files or 1 file at a time" appers and when i choose 1 file than that msg box does not unloaded and even small access screen also appears and whole xl file looks ugly. can we unload msgbox when it is answered and how to uload this access screen? Here si the code. The code works fine i just want to ulodad msgbox when they are aswered and unolad file diaolg box also and unload small access screen
    • Edited by zaveri cc Tuesday, November 16, 2010 7:15 PM
    Sunday, November 07, 2010 1:15 AM
  • strsql = "DELETE FROM Main " _
    & "WHERE [super department] in (SELECT temp.[super department] " _
    & "FROM temp INNER JOIN Main ON temp.[super department]=Main.[super department] " _
    & "WHERE temp.season=Main.season)"
    

    Hi, try this.

    And about your second question, I think you should start a new thread. If I'm not mistaken you've already done it.


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Edited by VanderghastModerator Tuesday, November 09, 2010 1:12 PM A double quote was apparently missing
    • Marked as answer by Bruce Song Friday, November 12, 2010 5:06 AM
    Sunday, November 07, 2010 6:35 PM