VBA creating/updating arbitrary number of access tables

Question

I'm currently interning at a company, and my employer has asked me to create a database of transmissions, charges, credits, etc. for a new market that they're in. I created a little java program that downloads about 130 CSVs with data, some empty, some not. The program then deletes the empty ones.

They do most of their work in Access, which I'm learning quickly, but it's not my forte. I've written a few VBA programs/scripts for their sales people and for automation, and now I want to add code to this Access database that I'm making to programatically update it.

Problem is, the only way I can see to make/update these tables with the data I am downloading is to individually construct all 130 of them, and individually construct SELECT and INSERT queries for each individual report. However, because a table may or may not have data for a given date range, I would need to construct ALL the tables and queries.

Coming from a programming background, I would love it for there to be a high level of abstraction where I could do something like this

For each file f in Directory d
    Get report title
    If there is a table with the same report title
         Insert any unique values into that table
    If there isn't a table with that report title
         Create it, and then insert unique values into that table
End

However, it's not really looking like I will be able to do something like this.

I am considering another method, where I

1. Write the reports out to a new csv file that only contains the line items (no headers)
2. Set up linked tables to these reports
3. Create a bunch of canned imports with the Wizard

I only have a couple more weeks left with this company, so I'm torn between finding a solution to this, or being pragmatic and just setting up logic for a core set of the reports that should always have data in them. Any help/suggestions would be appreciated.

Answer 1

You can most definately do what you high level summary states Tom, these are the area's you need:

FileSystemObject (This can loop through files in a folder and also give you the filename)

TableDefs (Can extract the table names from the database)

I would recommend loading a file in to a temp table using FileSystemObject, you can then use the TableDefs property to confirm if the relevant table exists. If so then you can use the SQL that Remou has provided to insert only rows that are not already in there. If your table doesn't exist then you could simply use the following SQL to create your new table with the data:

SELECT * INTO NewTableName FROM TempTableName

There's not quite enough for me to go on to write out the procedure but those two properties should take you in the right direction, post again if you get stuck with them.

Best of luck : )