Monthly Archiving # Of Records In Tables

[Bcanfield83]

Hi all,



I'm looking for some guidance on the best way to proceed here. I have a database that imports a series of flat files (mostly .xlsx or .csv) on a monthly basis. There's actually a button on the main form that, once clicked, goes out and imports about 10 files that are saved to varying locations (shared drives/folders) on our network. Each of these files is imported into a different table in the database. There's a separate procedure for each one, but they are all located within the same module. This process is run on the 1st of each month (or next closest date if the 1st falls on a weekend) - and only once per month.
What I'm looking to do is incorporate an archive process - though I don't actually need to archive all of the records being imported. Rather, I just need to create an archive that shows the number of records imported into those 10 tables each month.

For the archive table, my plan is to create a table with:

• ID (Autonumber)
• File Name (this represents the name of each of those 10 files being imported)
• Count (this would be the total # of records that were imported for the listed file)
• Month Number
• Year

Now as far as the process of actually archiving that data each month.. I could create 10 separate 'Append' queries that would run after each respective file is imported. Each query would simply append the count of records into the archive table for that particular file (after importing the data for it) and then also insert the current month number and year.
This seems like it would work just fine, but I'm wondering if there's a more sophisticated and/or efficient approach to doing this then running 10 separate append queries each month. Is there a fairly straightforward way to create a standalone function in VBA (e.g.: perhaps tacked onto the same module that's used to import the files) as opposed to creating a bunch of queries?

[June7]

Yes, VBA could sequentially run 10 separate SQL INSERT actions. Yes, this could be done in the same module that accomplishes the imports.