How to use a Dialog Box to append tables from different databases into another

[shaztastic]

I would like to have a form with a button that will enable me to select multiple database files, which all contain one table (identical structures different data) and copy the data into the database which contains the form.

This is probably really simple but so am I



Any help is very much appreciated

[JeroenMioch]

Let me know if i understand you correct; You have multiple tables (in the same DataBase) and you want to view them through the same form, at the click of a button ?

[shaztastic]

I have multiple databases which each contain one table.
I want to be able to copy the data from these multiple database tables into another (master) database table by using a button on a form which will open a file selection dialog box.

[rpeare]

I think it's multiple databases and he's trying to get all the data in those remote databases to appear in his master database to which my question would be... why. Are all the databases on the same network or are you trying to do some sort of data synchronization? Why would you have multiple copies of the same database when your intent was to get all of that data into the same data set for examination? What you're asking is possible but extremely bad practice. Is there a specific reason you are doing things this way?

[shaztastic]

I use AutoCAD Electrical to produce a project cable connection report. Each AutoCAD project will output the data into a database table. Each project may have to output multiple reports depending on the size/complexity of the project.
I have a master database which contains the report setup.
At the moment I copy and paste the tables into the master database and then run the report to print the overall project, I was looking for a way to automate this a bit more.

[rpeare]

if the source databases you're importing from do not change names/locations you can link the tables you're interested in into your collation database and create a UNION query of all the tables. Then you don't have any duplicate data at all, assuming of course that each database is on your network (which I assume they are since you're cut and pasting data)

You're just reporting on data that's in x many different tables so let's say the table is named tblData in each one of your source database files and you have 5 source tables.

When you link them into your reporting database link them with identifiable names like:

tblData_Workstation1
tblData_Workstation2
tblData_Workstation3
tblData_Workstation4
tblData_Workstation4

Then your union query would be

SELECT * FROM tblData_Workstation1
UNION ALL
SELECT * FROM tblData_Workstation2
UNION ALL
SELECT * FROM tblData_Workstation3
etc....


then you can base your reporting on the union query instead of actually copying the data.

[shaztastic]

They change names and locations.

[rpeare]

So the structure of the table is always the same regardless of project. But the database that actually stores the data may change folder location or file name based on what the AutoCAD project is called?

That's a tough one

Do you have something like a Projects folder, then each Project has a subfolder, and each subfolder has a database in it?

If you do does each drafter name the project the same thing (the part they are working on?) or do they work independently on separate projects?.

You could theoretically be importing hundreds and hundreds of databases if this is how AutoCAD is giving you the data.

[shaztastic]

So the structure of the table is always the same regardless of project. But the database that actually stores the data may change folder location or file name based on what the AutoCAD project is called?
Yes thats correct.

That's a tough one

Do you have something like a Projects folder, then each Project has a subfolder, and each subfolder has a database in it?.
Almost:
At the moment we have Projects folder, then a subfolder with the Master DB, and then another subfolder with all the AutoCAD created DB's.
But they could all go in the same folder if it makes life easier.

If you do does each drafter name the project the same thing (the part they are working on?) We try to keep a similar structure but the name changes with the project or do they work independently on separate projects?.
Independently

You could theoretically be importing hundreds and hundreds of databases if this is how AutoCAD is giving you the data.

I've seen a max of 5 so far, i don't think we'd ever get many more than that.

Just thought that it might be an easy thing to automate obviously i was wrong!!
Not sure if this will help but we can also output the data from AutoCAD in the following formats:
ASCII, .xls, .xml, CSV
If its too much of a toughie we will have to carry on copying and pasting!

[rpeare]

It all can be done you just have to know the scope of your project.

For instance, you can scan a directory tree to look for any database file it can find, then list those items in an access object (a table, for instance, in the master database) then allow the user to select which databases they wanted to import. As long as the table name is always the same within each database that shouldn't be much trouble.

MasterDB.zip

The zip file contains a master database, a folder with lots of subfolders and databases in SOME of those folders.

From there use the form to do your import.

[shaztastic]

You sir are a genius!

Very many thanks