I had to do this from an information supplier for awhile.
If there is only one excel spreadsheet and it is updated (and you have to use an excel spreadsheet) then just link your access database to the spreadsheet and treat it as a linked table. You will have to save it where the access file can always see it. If there are multiple users, then put it on a network drive that everyone has mapped the same.
When you want to update it, just replace it with the new one with the updates while the database is closed.
If it has a different name each time, then just make sure you rename it the same as the linked table name.
And make sure you don't mess around adding new fields to the spreadsheet.
If you get different files from each supplier, then just link to all of them seperately instead of trying to consolidate and or convert them each time.
If you want to keep this info in the access database, then just delete the table, and reimport the new one. As long as the field names and values are the same, access won't mind. It will even add its own PK for you each time. I saved the table structure as a template table, and then would copy and paste the template table with the name of the old table, and then reimport the data from the new file.
I had to update several tables each month, and refer to older data, so I wrote buttons that would delete the current month's tables, copy, paste and rename template tables, then import the data for the month that was wanted. I did this all from a network folder containing only excel files.
mmcdonal
|
