Importing Excel sheets into a historized database

[AccessAggrieved]

Hi there,

I am pretty new to Access, VBA and the like, and i need your help.

I need to implement an import function to import data from various excel sheets. I would be able to delete the old content und import the new data via DoCmd.TransferSpreadsheet and SQL, but in this case all changes need to be tracked, so that we can tell what the data in a record was at a given time. So deleting is no option; instead i use a kind of "soft-delete" by setting validFrom- and validTo-Dates for each record.

The import then needs to behave like this (these are all cases i can think of; if i forgot something, please tell me):
- all records in the exceldocument need to be compared to the dtata in the DB
- are DB-record and Excel-record equal, then do nothing
- is a record in the Excelfile, but not in the DB, then create a new DB record
- are the DB-record and the corresponding Excel-record not equal, then soft-delete the old DB-record and create a new one with the given Excel-data
- is a record in the DB but not in the Excelfile, then soft-delete the DB-record

here is an easy example:
DB:
User1 | Data1
User2 | Data2
User3 | Data3

Excel:
User1 | Data1
User2 | Data9
User4 | Data4

DB after import:
User1 | Data1
User2 | Data2 validTo set
User2 | Data9
User3 | Data3 validTo set
User4 | Data4

User1 is unchanged.
User2 has changed, so the old DB-record is invaildated and a new one has been created.
User3 does not exist anymore, so the DB-record has been invalidated.
User4 is new, so a new DB-record has been created.

I have no idea how to implement this behaviour, so please give me a hint.
Thanks in advance

 

[pwbrown]

I would import the excel file into a new "dummy" table.
Run a update query to compare both tables records and change validTo on the original table where the records aren't the same.
Then run an append query to move new records to the original table.
Then delete the remaining records or do whatever you want with them.

 

[AccessAggrieved]

Hello and thanks for your response.
I twisted my mind, but thanks to you i realized that this problem is actually not that hard. Here is what i'm gonna do:

I will import the excel file as linked table.
An update query will set the validTo-date for all records that have the same key values and at least one different value compared to the records in the exel table.
An insert into statement will then append all records from the excel table, whose key values are not in the valid subset od the DB-data.

I can not just delete the remaining records, because they also contain records that did not change and shall still be valid. Instead i need an update query that sets the validTo-date for all records in the DB table, that are not in the present in the excel table (just comparing the key values).
Instead of writing a new update query for that, i can just enhance the above statement.

So i will implement two statements:
an update query to set all records invalid that are not in the excel table or that have identical key values but different secondary values from those excel records.
And an insert query to insert new records and changed records from the excel table into the db table.

If i see it correctly, this gives me everything that i need for my import function.