Updating a database

[peekay]

I have an application where I have to compare two databases with similar tables and fields - the one being an older one. How can I update the old database so it contains the subsequent new, edited and deleted records of the updated database without comparing the databases record for record or even table for table.

PK Odendaal
pko@mweb.co.za

 

[scking]

While logged in to the old database delete the old tables and import the new tables. This would be a problem if you have relationships established and referential integrity but based on the spare info it is an alternative.

Steve King Professional growth follows a healthy professional curiosity

 

[petermeachem]

Can't you just copy the whole file?

 

[peekay]

Thanks for the help. I omitted to say that the old database may have been updated by another user in the meantime and these new records which does not appear in the new database must also be retained. Secondly it is an Access 97 database.

PK Odendaal
pko@mweb.co.za

 

[BoxHead]

I'm sure their are very simple and elegant ways to get this done. Just in case you don't hear from anyone elegant...

If your field names and primary keys would match up, Copy the Data from one table and Paste Append into the corresponding table in the other database. Any duplicate key violations will be dumped into a Paste Error Table.

If your Fields don't actually match up from one DB to the other, export the data from both DBs into Excel (one worksheet for each DB) and play with the columns/fields until the two worksheets match up with the database you want to populate. Copy and paste the data from both into a third worksheet.

Again, if the Primary keys match up, you can Copy and Paste Append back into access.

If you don't have a primary key or your primary keys don't match up between DBs, you'll have to find another field or fields to compare. (This is where it gets really ugly - in a fun, manipulative sort of way)

Let's say you want to eliminate records that have common "DATE" and common "LAST_NAME" fields. Let's also say that "DATE" is in ColumnA and "LAST_NAME" is in ColumnB.

Sort the worksheet first by "DATE", then by "LAST_NAME". Any duplicate records will be placed in consecutive rows.

Go to an empty column to the right of your data (let's say ColumnR) and in R2 type the function

= if(A2 = A1, if (B2 = B1, "Duplicate", "&quot,"&quot

This compares the DATE and LAST_NAME fields from each record with the same fields from the previous record and identifies the duplicates.

You can scroll through and delete each of the rows that is marked as a duplicate or if there are a lot of duplicates, you can use function statements in Columns S, T, U, etc.

Example: in cell S1 type:

= if(R1 = "",A1,"&quot

This will check to see if the row is marked as a duplicate. If it is not, it will transfer the DATE from ColumnA to ColumnS. If the Row is marked as a duplicate, the cell in ColumnS will be left null.

Work your way to the right:

in Cell T1 type: = if(R1 = "",B1,"&quot
in Cell U1 type: = if(R1 = "",C1,"&quot
in Cell V1 type: = if(R1 = "",D1,"&quot
in Cell W1 type: = if(R1 = "",E1,"&quot

Then Fill Down to the end of your data. The values to the right of ColumnR will not contain any duplicates.