Compare data in two separate Access databases?

[Minx]

I have just "inherited" two copies of what is supposed to be the same Access
database. I SERIOUSLY doubt that they are actually identical because there is
a notable size difference between them. I need to find some way to report the
differences between them so that I can start slogging towards combining them
into one, correct MDB.

I don't know anything about Access and I have no extra money to spend on
this project...I think I may be doomed.
Help me, Obi-Wan Kenobi - you're my only hope!

 

[Jerry Whittle]

Hi,

Size might not matter at all. Try doing a compact and repair of each
database file and see if they get closer in size.

Below are two queries. Run both in each database then paste the results into
an Excel spreadsheet side-by-side. You could compare differences.

To run them open a new query in design mode. Do not select a table. Next go
to Design view and paste in the query.

The following will give you the names of all the forms, reports, etc. It
doesn't say if any are changed; just that they have the same name.

SELECT IIf([MSysObjects].[Type]=1,'TABLE',
IIf([MSysObjects].[Type]=5,'QUERY',
IIf([MSysObjects].[Type]=-32768,'FORM',
IIf([MSysObjects].[Type]=-32764,'REPORT',
IIf([MSysObjects].[Type]=-32766,'MACRO',
IIf([MSysObjects].[Type]=-32761,'MODULE',
IIf([MSysObjects].[Type]=-32756,'PAGE',
IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))) AS ObjectType,
MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*"
And (MSysObjects.Name) Not Like "Msys*")
AND ((MSysObjects.Type) Not In (2,3)))
ORDER BY IIf([MSysObjects].[Type]=1,'TABLE',
IIf([MSysObjects].[Type]=5,'QUERY',
IIf([MSysObjects].[Type]=-32768,'FORM',
IIf([MSysObjects].[Type]=-32764,'REPORT',
IIf([MSysObjects].[Type]=-32766,'MACRO',
IIf([MSysObjects].[Type]=-32761,'MODULE',
IIf([MSysObjects].[Type]=-32756,'PAGE',
IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))),
MSysObjects.Name;


The query below will tell you the number of records in each table. It can
take a few minutes to run. If the record counts are different, the databases
are probably different.

SELECT MSysObjects.Name, CLng(DCount("*",[name])) AS NumRecords
FROM MSysObjects
WHERE (((MSysObjects.Type)=1))
ORDER BY CLng(DCount("*",[name])) DESC;

 

[Dale Fye]

FMS puts out a product (Total Access Detective)
http://www.fmsinc.com/reviews/tadt/sa0996.htm

That will identify difference in code and in data. It might seem a bit
pricey ($199), but compare that to your salary, and figure out how many hours
you would have to work to try to actually identify the differences between
two databases (code and or data), it is money well spent. Plus, this give
you a tool you can use over and over again (or at least as long as you stick
with the same Office Suite).

HTH
Dale