Update Access table with weekly Excel exports (1 Viewer)

[tommyslash]

Hi forum members!

I am new to Access, have searched the web for the last couple of hours and don't seem to get anywhere.
Basically I have an inventory system that is neglected by IT. To answer certain questions I import parts of
that system in my own Access DB to forge away with my queries.
Thanks to this forum I've covered quiet a lot of ground in the past few days with left joins and cross tables ;-)
however I am a newb and for the acutal problem I might even have to tackle with VBA from what I can guess so far.

So far I've only needed data from the last snapshot / export of that system, but as I venture on, I need to
have access to historical data as well.

Here is what I want to achieve:
I have a folder where I put the weekly xls exports: yyyy-mm-dd-inventory.xls of the base inventory of neglected system.
I want to import and merge all excel files into one single Access table from that folder.

Items that don't show up in a later export anymore should remain in that Access table.
Items that still show up just update all fields, aside from the id.
New items are simply appended.

Optional:
an additional column to save the date of the last export file where respective item has been seen.

Could you please point me in a direction where to start?
Any suggestions highly appreciated.

 

[theDBguy]

Hi. Welcome to AWF! Do the records from Excel have primary keys?

 

[tommyslash]

Hi, I use the serial numbers of the respective items as primary key.

 

[theDBguy]

Hi, I use the serial numbers of the respective items as primary key.

Okay, so you might do this in steps. Let's say you have your table in Access where you want to permanently keep the data. You can then import the Excel file into a temporary table and then execute an UPDATE query to update the permanent table using the serial number. You can then run an APPEND query for those serial numbers without a match into the permanent table.

 

[tommyslash]

ok.. i figured from available query ribbons that i could do update / append when i have small amount of tables.
i already have a half years worth of excel files and need an automated / scripted solution. where i'd loop over the files in the directory and digest them in order.