Combine Multiple Access Files into One and Append Tables

[KLTurner]

Hello,

I have 240 separate Access files (.accdb) that I need to combine into one master database. Each of the 240 Access files contains forestry data from a single forestry plot that was collected on a handheld GPS and then exported into an Access file via Trimble Pathfinder Office, hence the reason these files are not already combined. I've attached one blank file so you can see the structure.

Each Access file is identical, containing the same four tables (Plot Center, Small Plot, Tree, and Woody Debris). I have added a "Plot ID" field to each table in each of the 240 Access files and populated it with the unique forestry plot number (e.g. 21-13) so that when combined, it will be clear which data is associated with which plot. In the end, I would like the user to be able to search for individual plot data via query by "Plot ID", or use the entire database to conduct analyses (e.g. calculate average tree height, etc.).

I have read about merging or combining Access files by using the Import Wizard and then using an Append Query to append tables, however, with 240 files to work with and four tables per file, I imagine this will take a very long time! I am hoping to find someone who has a script or code I could use to streamline the process. I have never worked with scripts in Access, but have used them in R and FileMaker Pro.

Thanks in advance!
KelleyForestry_Database_Structure.accdb

[ssanfu]

I have 240 separate Access files (.accdb) that I need to combine into one master database.

Each Access file is identical, containing the same four tables (Plot Center, Small Plot, Tree, and Woody Debris).

I not sure what you want: Should the "master database" end up with one table containing all of the data or 4 tables?



Put all 240 dBs in a folder. The code is pretty straight forward...
Use the file system object to select/open the folder
Get the first dB name
loop until no more dBs
Open the dB
Import the data
Close the db
Get the next dB name
Loop

[KLTurner]

Thanks Steve,

I would like the structure of the Master database to be the same as each individual database with four tables.

Kelley

[ssanfu]

First off, there is an error in the code.

I am being kicked off this computer - I have A2000 on my laptop, so I will have to work on this later. (might be a couple of days)

Any chance of getting a few records in each table for testing purposes? Just one sample dB...

Interesting problem. I've thought of this before, but never attempted it.

[KLTurner]

Thanks for delving into this. I've attached the database with some data for you to experiment with.

Kelley

[ssanfu]

Kelly,

Major apologies for taking so long... I have been working on this as time permitted ( add excuses here ),

I couldn't update the tables using an append query like I wanted, do I had to brute force it.

There are no changes made to the source dB files.
I added a field to each of the 4 tables to be able to know which records came from which dB file.

The field names leave a lot to be desired. Because of the special characters in the field names, the coding was a little more difficult.


So, create a folder (preferably on you computer) and put all of the 240 accdb files in the folder. Remember the folder name
The master dB should be in a trusted location on your computer.

[KLTurner]

Steve,

No need to apologize for the delays, I have been busy with the holidays myself. I just did a test run of the Master you sent with a handful of files and it is AMAZING! Thank you so much!

I only had one minor problem. Some of my dB files do not have Woody Debris or Small Plot tables associated with them because these features were not present in the forestry plot. I'm sorry I didn't let you know this detail before. When I try to import these dB files, I get a Run-time error '3708' stating that: "The Microsoft Office Access database engine cannot find the input table or query 'Woody Debris' (or Small Plot). Make sure it exists and that its name is spelled correctly." Would this be an easy fix to your code? I don't want you to have to do anymore than you already have and if it is a hassle just let me know. I can work around it by adding the tables into each dB, even if they don't have data.

Again, thank you so much for your help! I will be spending the later part of the week importing all of my files and will let you know how it goes!

Happy New Year!
Kelley

[ssanfu]

I think there might be a way to check to see if the tables exist..... but I will have to do it next year...

[ssanfu]

OK, try this version. I didn't know if this is a one time project, so I didn't add any error handling (most of the time I am lazy... I mean I conserve energy).

I added the text boxes to let me know what is happening. Hope it gives you enough info as to progress...


OK, so I was bored...

[KLTurner]

Hi Steve,

Its PERFECT! I just did a test run and everything worked beautifully! The text boxes are very helpful, thank you for adding them.

This is a one time project, so once the last few inspections on our forestry plots come through I will add the rest of the plot dBs into the Master database you created.

Thank you so much for taking the time to put this together, you are a lifesaver!

Happy New Year!

Kelley

[ssanfu]

You are welcome. It was a fun project....