Hi, I am just getting started with Access and struggling!
My fundamental problem is that I can not work out how to import data from a CSV file (without a primary key) to a Table (with a primary key) and update fields where there has been a change and create new rows where needed.
I am trying to create an index of documents and files on a hard drive. I have created a baseline Index Table and use the Primary Key of this Table as the file reference in other Tables of the database.
The majority of these files will not change. However, overtime some will be moved, modified or deleted. New files will also be added.
The raw data of any update to the Table Index is captured in a CSV file generated by Powershell. However, there is not a unique identifier that is consistent from one update to the next. (e.g. There maybe duplicate filenames in different folders)
How can I update any changed fields in the Index Table whilst preserving the primary key for unchanged and modified files and append any new files?
I have tried creating a "merged" table of current & update. I can then identify duplicates and new, but am left with struggling to update the modified files and identifying the deleted files.
I am hoping that there is a way of updating fields in the Index Table based upon multiple field criteria in the imported update table (i.e. if "Field1, Field 3 and Field x" are the same in both Tables THEN "update row" in Index Table).
My problem is that I can't work out how to define such a query (if it is possible?).
Easy steps please :