Merging 70 individual dbs into one - without creating 70 queries!!!!

[bilboo969]

Hi all - new here - hope to get some help

Situation: I have a bunch of individual databases (approx 70) - each holding one table called "Meter" with some GPS reads and additional information about the GPS point.

Am looking for solution to create one big database file holding all GPS reads.

One idea i have is linking all tables into a new database and run a create table query and then an append query.

Is there a way to somehow have the one append query run automatically on all existing linked tables (remember approx 70 tables so i really want to avoid of creating 70 append queries )

To make it more complicated: data sets in the original individual databases could be modified or new data could be added. From what I know linked tables are updated but is there a way of keeping the my big, newly created (appended) database up2date automatically as well?

Hope it all makes sense?
Thanks a lot in advance
Regards from Ireland

 

[raskew]

Hi -

Gotta ask...Why would you possibly want to create new databases, each one holding one (apparently identical) table?

Having said that, how are your databases named?

Bob

 

[bilboo969]

Hi -

Gotta ask...Why would you possibly want to create new databases, each one holding one (apparently identical) table?

Having said that, how are your databases named?

Bob

First of all thanks for taking the time having al ook at my problem.

There is a misunderstanding - well maybe it is my explanation . I want to create a database which has one table holding all information of the other 70 Smaller databases. Each smaller database(table) holds its own set of data (GPS reads for different locations / areas) within one table - so the information within each table is unique the only identical thing is the actual table name - which is "Meter".

The Databases are all called different according to the location / area of GPS reads. These databases are automatically created during the export process from our GPS software Trimble Office Pathfinder.

Regards & tnx again

 

[DCrake]

Simple Software Solutions

Hi

I suppose you will know the names of all these smaller databases?

Step 1:
Create a macro that imports the table from your first smaller database into the main table in the front end mdb.

Step 2:
Test this and make sure you get the desired results.

Step 3:
Click on Tools> Macros > Convert Macro to Visual Basic

Step 4:
Design the resulting code in the newly created module.

Step 5:
Copy and paste this line below each new line to create a row of imports. Remember to change the location of each mdb in accordance to the location and name of the mdb.

Step 6:
Place the line
DoCmd.SetWarnings False
DoCmd.SetWarnings True

before and after the code (See Example)

DoCmd.SetWarnings False
    DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\Temp\Temp1.mdb", acTable, "MyTableName", "MainTableName", False
    DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\Temp\Temp2.mdb", acTable, "MyTableName", "MainTableName", False
    DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\Temp\Temp3.mdb", acTable, "MyTableName", "MainTableName", False

DoCmd.SetWarnings True

Step 7:
Give the function a meaningful name
Create a form with a button on that calls the function

Hope this exlplains it all

CodeMaster:

 

[bilboo969]

Hi

I suppose you will know the names of all these smaller databases?

Step 1:
Create a macro that imports the table from your first smaller database into the main table in the front end mdb.

CodeMaster:

Sorry but am struggling already with Step 1 am new to all that access stuff if that counts as an excuse

I cannot find an import function to load data from first smaller database into front end mdb?

Please advice / help
Tnx a mill

 

[bilboo969]

Please forget previous post - think i got it: TransferDatabase - Import - correct?

The start of looks good: imports nicely the first small database into a new table but if i run this macro twice or more it imports additional databases / datasets it adds them as new tables - i need one large table to hold all data.

Is there a way of appending the imported data to an existing table?

Here is what i came up with:

Step 1: I created a Marco that links 2 of the small tables into a new database - locations, name of database and table are fixed - would be great to be able to point it just to a directory and it would link in automatically all tables of all existing database in that directory. So i do not have to create 70x "TransferDatabase" entries in the macro

Step 2: I created a "Create Table Query" onto first linked table which creates a new Table called "AllMeter"

Step3: I created an "Append Query" onto the second linked table appends the data to the in the previous step created "AllMeter" table - here again the location and name of databases & tables are fixed - would be again great to be able to point it just to a directory again and it would append all tables of all existing databases in that directory

And just to mention again the fact that some of the records might get updated - within the small original tables or new records might get entered / added.

Is what i want possible Am i asking too for much

Tnx so much again

 

[DCrake]

Simple Software Solutions

The logic you are using is correct I neglected to state that your first transeferdatabase instead of import.

Prior to step 1 if you go to your master tabel and delete all the contents from it then use the append feature instead of create then it will work. Your suggestion of enumerating through a folder is doable but this involves the hands on approach to VBA.

First you would have to define the folder and check for its existance. Then you would have to loop through all the files in the folder to detemine what type of file(s) are in there (Mdb's only required). If it encounters a mdb type file add this file to an array.

Then use a For X = 1 to Y Next to loop through all the mdbs in the array.

Each time it would have to open the mdb and check to see if the specified table exists in the mdb (An rouge mdb might suddenly appear in there that does not contain the desired table).

Once it has validated it then you would pass the mdb name to variable and run the DoCmd.TranserDatabase command line

I suggest you get it working in long hand until you are familiar and confident of what you arre doing, then progress to the next level.

CodeMaster:

 

[Uncle Gizmo]

You may find the example downloadable from here useful.