How do you update a value for a table when no join exist
Posted on 2011-09-12
Step 1 - Via MS Access VBA I am looping thru a file directory grabbing all the file names (all MS Excel files) and file paths found and placing them (file names and file paths) in a table call File-Names
Step 2- Still within the loop I then import the MS Excel file into a table called Excel-Import-Data, based on the File Name and File Path found in Step 1.
My quandry is when I import the Excel file into my table called Excel-Import-Data I want to update a field call File Name. However, since there is no common field/join between the tables Excel-Import-Data and File-Names I am unable tu update the field called File Name.
However, since I am looping thru my directory one file at a time while subsequently doing the import there must be a way to update the field called File Name within the table Excel-Import-Data ?
Since in the MS Access VBA I am using the docmd command.... to append and update records that may be my shortcoming..Probably there is a way in using a SQL command where I could achieve my goal, but all the Google searches I did on trying to find out on how to achieve this update required the use of a JOIN statement ?