Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297

    Unanswered: Difference between two copies of a database

    Is there any way to determine the difference between two access databases? Say, I have a database that I copied. The copy has been "stagnant" since it was created so it is a copy of the original database at a certain point in time. Now the original database is still being used and updated. Is there any way to determine the new records inserted into the original or differences between the two databases?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Are you talking just data? Have the table structures changed at all? Do your tables have autonumber keys?

    It's relatively simple to compare to identically structured tables to find out which records of one table don't appear in another. there are quite a few variables that may complicate this process with old versions of the same database.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    I believe it is a relatively new database. And the table structure is exactly the same. The copy is just missing about a week's worth of work. How can I compare the two databses?

    Thanks.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    by using a left join statement with the "IS NULL" comparison in your where clause. What you join on depends on the table structure.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    So I'd have to copy all of the tables from one database to the other database? And you're saying to do something like:
    Code:
    SELECT t1.*
    FROM table1 t1 
    LEFT JOIN table2 t2 ON t1.ID=t2.ID
    WHERE t2.ID IS NULL
    Shouldn't it be an outer join?
    Doess access support MINUS or EXCEPT?
    Sorry about all of these questions, but I'm used to using MySQL, MSSQL, Sybase, etc...and the simplicity of Access does nothing but confuse me.

    Thanks again for all your help.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    rather than import the data into a db, use a link rather than import

    access as some query wizards, one of which may help 'find unmatched'

    a potetnail issue is going to be if you want to do a column by column comparison

    if you want to import data from one source into antoher then you need to consider if there are any duplicated autoneuymber columns with different data, and if so how you are going to resolve thise issues (is one record 'correct', or do you need to merge the data but give it a new key value)

    If you go down this road then depending on complexity of your datamodel any inserts may need to be handled by a VBA fucntion (to ensure that the new autonumber value is correctly replicated.

    You can do it using queries (if you add the old (dulicated) column in your tables as a new (temporary column)


    HTH

  7. #7
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    OK, maybe I'm going about this the wrong way...here's my situation.

    I had to migrate an Access database to a MySQL database (well, multiple databases). I made copies of the Access database and used these copies to generate the data for the MySQL databases. I migrated an access database to a MySQL database using the MySQL Migration Toolkit. Unfortunately, the toolkit is not 100% foolproof or very fast. But it gets the job done well. So I'm looking for a quick way to get the data that has been added to the live access database since I made the copies and performed the migration. I was planning on running some queries to find the differences between the tables and then export that data to a csv file and load it into MySQL. I do not want to reimport the whole thing because time is going to be an issue and some tables have 100k or 1mil+ records in them. The full migration previously took about 5 hours. I need this to be done in less than 2.

    Any ideas?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    delete the data from the MS Access tables (or new copy thereof) that already exists in the MySQL systems

    - simple comparision of autonumber keys vakues should get you most of the way there

    -consider using dbtools or some other tool to do a data import into MySQL

    depends on how much data you have topump accross but 5 hours seems a heck of a lot of time

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •