Windows 10 
Access 2016
Hi,
Looking for help with update query.
A bit of background:
I created a database with three tables that I have imported three tabs from an Excel spreadsheet.
I got it to import the files and then move the file to another folder. All works well!
Now, if one of the files that has already been imported was updated with new data I do not want to import the data instead I would like to update the record that matches the file name.
Not sure which is the best way to go about this but I would like the file to either update the data in the table such as if …THEN data updated else import the data.
I created a table which holds the file names and so I am not sure which is best to use.
Option 1: create a “if/then” condition and if it meets the criteria then update the data or if not then import the data into the table.
But not sure how to create this condition (WKS is an object). I did begin the condition with:
#
IF (WKS.name = “table1.file Name.values” = filename) THEN
strSQL = “UPDATE [table1[ SET [ID] = “” WHERE [File Name] = ‘ ” & filename & “ ‘;” (filename is a variable that holds the name of the Excel file)
#
Access did not seem to like this.
Option2:
Create and update statement but the problem is that I do not know the ID for the particular row since there are about 300 records in the spreadsheet.
I thought of using a select statement within the Update statement that I have listed below but I get a syntax error.
#
strSQL = “UPDATE [table1] SET (SELECT * FROM [table1] WHERE File Name = ‘ “ & filename & “ ‘) ;”
#
I have search for ideas but only get part answers. There is so many ways but not able to find anything close. Can someone please help?
Many thanks in advance!!
Reply With Quote
