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?
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.
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:
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.
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)
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.