I have a database that gathers and coalates data from hundreds of individual databases. What I need to do is find a way to compare the data from one point in time to another. What I am going to do is initially run the coaliting database and name it Rev_A. Then at some point in the future we will run it again and Call it Rev_B. At that point I will have yet another database that will take the table from Rev_A and Rev_B and I need to compare all fields from each table and show the differences in some way.
Compare Data
- Thread starter Jimee
- Start date
- Local time
- Today, 12:05
- Joined
- Feb 28, 2001
- Messages
- 30,505
Will all the table names and all the fields be the same in each database each time?
- Local time
- Today, 12:05
- Joined
- Feb 28, 2001
- Messages
- 30,505
OK, the issue you have is probably one of how much VBA code can you tolerate?
In general, you have to somehow either import tables from the two databases and run a comparison query, or you have to open recordsets from each database, one table at a time for each, and compare records if there is a way to correlate them.
In order to identify the tables, you need to write code to step through the {database}.TableDefs collection on one database. For each .TableDefs
(where n = 1 to .TableDefs.Count) you have to get the name (with .TableDef.Name) to open the other table in the other database (with .TableDef(strName) ) and then run whatever comparison you need to run.
Since you don't mention how you would handle comparisons where the tables aren't of the same length, and you don't mention details of what you intend to compare, I can't go much beyond this point.
In general, you have to somehow either import tables from the two databases and run a comparison query, or you have to open recordsets from each database, one table at a time for each, and compare records if there is a way to correlate them.
In order to identify the tables, you need to write code to step through the {database}.TableDefs collection on one database. For each .TableDefs
(where n = 1 to .TableDefs.Count) you have to get the name (with .TableDef.Name) to open the other table in the other database (with .TableDef(strName) ) and then run whatever comparison you need to run.Since you don't mention how you would handle comparisons where the tables aren't of the same length, and you don't mention details of what you intend to compare, I can't go much beyond this point.
