Merging Two Databases with over 200 tables

[Msaccessuser]

Hi, there, I'm having a really hard time with this
and been searching the internet for some time for a solution, but my problem is
kind of unique, so I hope you can help!

I have two databases needing to
be merged into one. Sounds simple, but there are over 200 tables that have the
same structure and name but unique data in those tables.

Obviously, I've
gotten as far as importing the tables into one of the databases, but the tables
that are imported in are automatically renamed with a "1" at the end of the
table. (EX: Table1)

I've read suggestions about doing a UNION Query and
have tried the following:
SELECT *


FROM Table
UNION SELECT *
FROM
Table1;

This UNION query does "work" per se, but it doesn't create a new
table to replace the old ones, and it doesn't keep the relationships that the
old tables have.

From that UNION query, I tried exporting it into Excel
and re-Importing it back in Access as a Table, but like I said, relationships
are not kept. And as you can imagine, creating relationships for over 200 tables
took a long time...

So what I need is a way to merge the two tables into
one table while keeping the relationship to the master table in tact.

Or
any other ideas about merging these two databases would be
helpful.

Thanks!

[June7]

You are using Autonumber as primary key fields?

You have a major nightmare on your hands.

It can be done but will be tedious.

See if this helps http://www.databasejournal.com/featu...-Databases.htm

[Msaccessuser]

Thank you June7 for your reply to my post.
The primary key fields are actually text, not Autonumber... does that make a huge difference in your suggestion? Or am I still faced with the same nightmare?

[June7]

Maybe worse. Depends if there is possibility of primary keys duplicated between the two db's. If there is NO POSSIBILITY of duplicate unique IDs, then should be very simple to just import the records. This is the design I use for a project that requires merging of data from database maintainted at a remote location.

An empty database is provided to project manager to enter data about that single project. The project identifier is key. Manager submits file at end of season, records are imported to the 'master' db. I do have code that verifies the project isn't already in the 'master'. Of course, doesn't involve 200 tables, only 4.

[John_G]

Hi -

This might not be so daunting as it may seem. If your 200 or so tables have the same name and same fields in the two databases, you can write VBA loop to create append queries from one table to another. Below is a little test sub I wrote - it uses make-table queries, and I stopped it at 5 interations but it runs fine, and creates 5 xxxtablename tables.

Sub listtables()
  Dim SQL As String
  Dim tbl As TableDef, TableName As String, P1 As Integer
  Dim OtherDB As Database, OtherPath As String

  OtherPath = "C:\MSA_DAT\stamps.mdb"
  Set OtherDB = OpenDatabase(OtherPath)
  P1 = 0
  For Each tbl In OtherDB.TableDefs
    P1 = P1 + 1
    TableName = tbl.Name
    SQL = "Select * into [xxx" & TableName & "] from C:\MSA_DAT\stamps.[" & TableName & "]"
    Debug.Print SQL
    CurrentDb.Execute SQL, dbFailOnError
    If P1 = 5 Then Exit Sub
  Next
End Sub

You would have to use an Insert SQL statement of course. Note that the For Each... loop includes system tables (MSys...), so you would need to deal with that and any other tables that you don't want to transfer. Also, as June7 has pointed out, you will have to be sure that the append won't create any Index or PK errors.
I have used the square brackets [] to allow for spaces in table names.

Other than that, it should work - but PLEASE make a back up first!!!

John

[Msaccessuser]

Hi, John,

Thank you so much for replying to my post! I would like to try to use this code in my practice Database that I'm using to merge the tables. Hopefully, I don't sound too ignorant, but I'm afraid I'm not too familiar with complicated code, so I'm having a hard-time figuring out where to insert my information into the code. I know to put my table names in the brackets, but other than that is there anything that I need to change to make it work for my Databases?

Thank you again for your response!

[June7]

The code is dynamically generic for use in any database. It assumes tablenames are the same in the dbs to be merged. The procedure will loop through the tables collection and execute on every table (if you remove the 5 iterations limit). You don't need to specify your tables. The issue might be that the code will execute on ALL tables, even those you might not want it to and possibly even system tables. The procedure could be modified to prompt for confirmation that the append should proceed. Means babysitting the procedure until done. No idea how long that would be - 10, 30, 60, 200 minutes?:

TableName = tbl.Name
If MsgBox("Merge table " & TableName & "?", vbOkCancel) = vbOk Then
CurrentDb.Execute "SELECT * INTO [xxx" & TableName & "] FROM C:\MSA_DAT\stamps.[" & TableName & "]", dbFailOnError
End If

This procedure alone will not maintain integrity of autonumber pk/fk relationship nor handle the possibility of duplicate pk values. You will have to 'prep' the tables first if those circumstances exist.