Comparing two versions of an Access DB

[Kasper Larsen]

Hi group, first of all I ask for forgiveness if this question should have
been asked in another newsgroup. Please let me know of a more appropriate
place to post such questions, thanks.

I have been looking for a tool to compare two versions of the same database
( sort of a WinDiff for access). I found a Total Access Detective from
www.fmsinc.com but this product does not have a trial version available, so
I have no idea whether it a good buy or not. I would like to know what you
guys do when comparing versions ( homegrown tools, using 3rd party tools )
??

TIA

Kasper Bo Larsen

 

[Douglas J. Steele]

While there may not be a trial version, according to
http://www.fmsinc.com/products/detective/tad2000/convince.html , FMS offers
a 30 day money back guarantee.

To be honest, I've not used that particular FMS tool, but I know that their
products are top-notch quality. It strikes me that it would take you far
longer than $200 worth of time to write your own equivalent tool.

 

[Kasper Larsen]

Thanks for your answer. This endorsement of FMS is importent as I really
don't like to spend $200 without knowning what I get.
Which tool / trick do you use when comparing values in the same table in
diffent mdb's ?

Kasper

 

[Douglas J. Steele]

I seldom need to.

However, what I'll sometimes do is create a new database, then link from the
new database to the table(s) in the other two databases (giving each one
names such as "Database1Table1" and "Database2Table2")

I'll then create a Union query that compares the tables:

SELECT Database1Table1.Field1,
Database1Table1.Field2,
....,
"In Database1 Not In Database2" As Comment
FROM Database1Table1
LEFT JOIN Database2Table1
ON Database1Table1.Field1 = Database2Table1.Field1
WHERE Database2Table1.Field1 IS NULL
UNION
SELECT Database2Table1.Field1,
Database2Table1.Field2,
....,
"In Database2 Not In Database1" As Comment
FROM Database2Table1
LEFT JOIN Database1Table1
ON Database1Table1.Field1 = Database2Table1.Field1
WHERE Database2Table1.Field1 IS NULL

This assumes Field1 is the primary key in the tables.

This can be extended to show when rows with the same primary key exists in
the two tables, but other values are different:

SELECT Database1Table1.Field1,
Database1Table1.Field2,
....,
"Field 2 is different" As Comment,
Database2Table1.Field2 As Difference
FROM Database1Table1
INNER JOIN Database2Table1
ON Database1Table1.Field1 = Database2Table1.Field1
WHERE (Database1Table1.Field2 <> Database2Table1.Field2)
OR (Database1Table1.Field2 IS NULL AND Database2Table1.Field2 IS NOT NULL)
OR (Database1Table1.Field2 IS NOT NULL AND Database2Table1.Field2 IS NULL)
UNION
SELECT Database1Table1.Field1,
Database1Table1.Field2,
....,
"Field 3 is different" As Comment,
Database2Table1.Field3 As Difference
FROM Database1Table1
INNER JOIN Database2Table1
ON Database1Table1.Field1 = Database2Table1.Field1
WHERE Database1Table1.Field3 <> Database2Table1.Field3
OR (Database1Table1.Field3 IS NULL AND Database2Table1.Field3 IS NOT NULL)
OR (Database1Table1.Field3 IS NOT NULL AND Database2Table1.Field3 IS NULL)

etc.

As you can see, it's tedious, and time consuming (although you can write VBA
code to generate the SQL for you)

 

[Tom Wickerath]

You can use the Find Unmatched Query Wizard. I'd start by importing one of the tables
into the other database. This should give you two tables with similar names. The
imported table will have a suffix "1". For example, if you have tblCustomers in both
databases, the imported table will be named tblCustomers1.

To use the query wizard, select Queries in the database window and click on the New
button. Select Find Unmatched Query Wizard. Run through the wizard two times. The first
time, select the original table in the first screen of the wizard and the imported table
in the next screen. Reverse the order of selection the second time you run the wizard.

Tom

_____________________________________


Thanks for your answer. This endorsement of FMS is importent as I really
don't like to spend $200 without knowning what I get.
Which tool / trick do you use when comparing values in the same table in
diffent mdb's ?

Kasper
_____________________________________

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> skrev i en
meddelelse
While there may not be a trial version, according to
http://www.fmsinc.com/products/detective/tad2000/convince.html , FMS offers
a 30 day money back guarantee.

To be honest, I've not used that particular FMS tool, but I know that their products are
top-notch quality. It strikes me that it would take you far longer than $200 worth of time
to write your own equivalent tool.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)
_____________________________________


Hi group, first of all I ask for forgiveness if this question should have been asked in
another newsgroup. Please let me know of a more
appropriate place to post such questions, thanks.

I have been looking for a tool to compare two versions of the same database ( sort of a
WinDiff for access). I found a Total Access Detective from www.fmsinc.com but this product
does not have a trial version available, so I have no idea whether it a good buy or not. I
would like to know what you guys do when comparing versions ( homegrown tools, using 3rd
party tools ) ??

TIA

Kasper Bo Larsen

 

[Scott McDaniel]

I own it and use it regularly and recommend it if it will do what you need.
It can compare all objects in different databases, and can compare two
objects in the same database (along with lots of other stuff). There's other
stuff out there (I even own some of them), but Detective is by far the most
complete. Only complaint I've ever had with the FMS stuff was the report
handling (reports generated by the program, NOT the reports in your
database) ... difficult to deal with, NOT intuitive at all, and quite often
not filterable ... and it's such a simple matter to do in Access. Oh well,
still recommended.

 

[Arvin Meyer]

Thanks for your answer. This endorsement of FMS is importent as I really
don't like to spend $200 without knowning what I get.
Which tool / trick do you use when comparing values in the same table in
diffent mdb's ?

Kapser,

I own the Total Access Developer Tools from FMS, which includes the
Detective product. Their tools are a bit pricey, but not when you consider
how much time they save. I use the Code Tools and Sourcebook often. I highly
recommend their tools.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Danny J. Lesandrini]

This might help, but it's not mine. David Rich wrote this code
and it was listed at his site as a free Access utility.

Check out his site, Geotech Computer Systems, at
http://www.geotech.com/

http://www.amazecreations.com/datafast/GetFile.aspx?file=comparedbs.zip

 

[George Nicholson]

I see you've already received recommendations for a FMS product, which I
certainly won't argue with as I swear by their Access Analyzer. One day out
of the box, it paid for itself. One of the best investments I ever made.
(If only there was a similar xReferencing tool for Excel projects...)

I've never used their comparison product. If you are looking for an
alternative solution you might look at CompareWiz at www.SoftwareAddIns.com.
I have a 97 version that I upgraded to 2000 but haven't had a need to
upgrade to XP. When I have used it, it provided exactly what I needed, but
I've only used it for bare-bones comparisons of months old mdb files with
similar date time stamps, trying to refresh my memory as to which is truly
the "later" version, and why.

It may be my memory playing tricks on me but it seems to have more than
doubled in price over the years ($69 1st time and upgrades for not much
less). If I recall, they do offer a Demo version, but it has significant
restraints.

In any case, shop & compare.

 

[Kasper Larsen]

I have not been able to figure out which part of geotech you are refering
to, could you elaborate. please.
I get a 404 on your site.

Kasper

 

[Kasper Larsen]

Thanks for the hint. I will have a look at the CompareWiz.

Kasper

I see you've already received recommendations for a FMS product, which I
certainly won't argue with as I swear by their Access Analyzer. One day out
of the box, it paid for itself. One of the best investments I ever made.
(If only there was a similar xReferencing tool for Excel projects...)

I've never used their comparison product. If you are looking for an
alternative solution you might look at CompareWiz at

www.SoftwareAddIns.com.

 

[Danny J. Lesandrini]

You waited too long to come looking for it. I always get hacked
on New Years Eve, so I shut off the site for the evening. It's
back up now. Give it a look.