A fork in the River (or Rectifying two databases) (1 Viewer)

[jsanders]

Well as the old saying goes a fool is soon relieved of the bourdon of cash.

I have managed to create a debacle sure to leave me with greyer and less hair.

Ok, I built a rather large database. Unfortunately we didn’t know a tenth of what we know now, about what should be in it. Not a big deal except.

Two of them were installed. Both have been collecting data. One of them has been modified many times front end and back end. The back end is the problem.

There are 80 tables and I didn’t document changes as I went along. There are more table and many changes within them.

The back end has been modified to accommodate changes to the front.

Does any one know an easy way to reconcile these two back ends.

My first thought was to go through it table by table and seek out the changes. Wow! Just the thought of that, makes me dizzy.

The second thought was to make a copy of the most resent one, delete the data, and copy all of the data from the second one into it. I’m leaning toward that unless someone knows a better way.

Thanks Guys,

A fool in need.

 

[WayneRyan]

J,

Need a lot more info here ...

If you just want to reconcile table structures:

1) Traverse the TableDefs collection of each database

2) For each table, in each database, enter the TableName/Fieldname

3) Then you can join the two "new" tables to ascertain which fields
have been added/deleted.

If you want to make the evolved databases identical, then, you're
gonna have to make the structures identical, THEN move the appropriate
data between the two.

If the second case is true, then you will face conflicts such as both
DBs updating the same record, both DBs inserting the same record
with different values, etc.

Need more info.

Wayne

 

[jsanders]

Hey Wayne,

It’s been a while since you came to my rescue, thank you.

No the data will not be the same in both databases.

J,

Need a lot more info here ...

If you just want to reconcile table structures:

1) Traverse the TableDefs collection of each database

2) For each table, in each database, enter the TableName/Fieldname

3) Then you can join the two "new" tables to ascertain which fields
have been added/deleted.


Wayne

How do I do that?

 

[WayneRyan]

J,

It's the weekend, so no elegant solution.

Make a new, empty database.
Add this table.

tblFieldNames
=============
ID - AutoNumber
TableName
FieldDB1
FieldDB2

Then import all of Database1 tables.
Then import all of Database2 tables. (They'll have a "1" at the end).

Then run this code:

Dim tdf As DAO.tabledef
Dim fld As DAO.Field

For Each tdf In CurrentDb.TableDefs
   For Each fld In tdf.Fields
      If Right(tdf.Name, 1) <> 1 Then
         DoCmd.RunSQL "Insert Into tblFieldNames(TableName, FieldDB1, FieldDB2) " & _
                      "Values ('" & tdf.Name & "', '" & fld.Name & "','');"
      Else
         If DCount("[FieldDB1]", _
                   "tblFieldNames", _
                   "TableName = '" & Left(tdf.Name, Len(tdf.Name) - 1) & "' And " & _
                   "FieldDB1 = '" & fld.Name & "'") > 0 Then
            DoCmd.RunSQL "Update tblFieldNames " & _
                         "Set FieldDB2 =  '" & fld.Name & "' " & _
                         "Where TableName = '" & Left(tdf.Name, Len(tdf.Name) - 1) & "' And " & _
                         "      FieldDB1  = '" & fld.Name & "'"
         Else
            DoCmd.RunSQL "Insert Into tblFieldNames(TableName, FieldDB1, FieldDB2) " & _
                         "Values ('" & Left(tdf.Name, Len(tdf.Name) - 1) & "', '', '" & fld.Name & "');"
         End If
      End If
      Next fld
   Next tdf

Your new table will have all the info you need. As anything other than a
one-time shot, I'd programmatically link to the target tables, but this should
get you started.

Wayne

 

[jsanders]

This is absolutely amazing.

Was this something you learned ,or did you intuitively create it out of nothing?

 

[WayneRyan]

J,

No, I knew the TableDefs/FieldDefs.

As a one-time effort, that was just the easiest way.

Not very sophisticated, but I think that it'll give you what
you need.

See ya,
Wayne