Tool to compare two databases (one Access, one SQL Server)

[teddysnips]

I have upsized a client's application from Access BE/FE to Access FE/
SQL Server. All is fine with initial testing, and they want to run
the systems in parallel prior to going live. The MD, who is not
particularly technical, is more or less insisting that every day a
comparison is made of the two data repositories to see if they are out
of sync. There are about four users who will be expected to key the
data into the two systems side-by-side (the look and feel of the UI is
more or less unchanged, except for the Application Title which shows
the system).

Does anyone know of any good, or cheap, or better free tools that will
do a data compare on the two databases? There's no money left in the
pot to roll my own; I'm thinking something along the lines of
Redgate's SQL Compare.

Thanks

Edward

[Stefan Hoffmann]

hi,

te********@hotmail.com wrote:

Does anyone know of any good, or cheap, or better free tools that will
do a data compare on the two databases? There's no money left in the
pot to roll my own; I'm thinking something along the lines of
Redgate's SQL Compare.

This is possible. Create a linked server to your Access database. Create
a view for each table. You can setup a comparision of your tables on the
SQL Server against the views to the linked server.
mfG
--stefan <--

[Rich P]

Hi Edward,

I am just wondering -- is your system using ODBC to link the tables from
the sql server to the Access FE? Are there any relationships on your
tables?

The linked server will only work if you have to original mdb located on
the same computer as your sql server. I can show you procedures you can
use for making these comparisons easily. Just need to know the things
above.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Dariusz Dziewialtowski]

I wrote SQL Server Comparison Tool (SCT) - www.sql-server-tool.com -
which can compare data between tables of SQL Server. Couple of years
ago I quickly wrote version of the program which compares data between
data stored in Access and SQL Server. I temporarily called it CATS
(Compare Access to SQL Server), but I never published it.

Here is the link to beta version of the program:

http://www.sql-server-tool.com/setup_cats_02.exe

The program doesn't have the help file yet, but using CATS should be
really straightforward - select Access database as a Source, select
SQL Server database as a Target, select tables to be compared and
click "Compare".

The program is somewhat "rough" and last time it was used was couple
of years ago. I don't know if it would sell well when finished, so
for now other programs have priority. Please feel free to use CATS
and let me know what are your remarks.

Dariusz Dziewialtowski.
su*****@sql-server-tool.com

[Peter Doering]

te********@hotmail.com wrote:

>
Does anyone know of any good, or cheap, or better free tools that will
do a data compare on the two databases? There's no money left in the
pot to roll my own; I'm thinking something along the lines of
Redgate's SQL Compare.

http://www.dbconstructor.com/home/home.aspx

Not free, but good, handles a lot of platforms.

--
Peter Doering [MVP Access]

[The Frog]

Hi Edward,

I have been using a fantastic and free tool from www.pentaho.com
called KETTLE. It has recently been renamed Pentaho Data Integration
(PDI for short) and this is a full ETL tool. It is a drag and drop
environment that will give you basically any data integration and
comparison capabilities, including upserts from one data source to
another if you need to make updates.

The tool is by far the best I have come across to date, and I have
used this for years to migrate data, and compare data, between SQL
server 2k, MDB's, Oracle, SAP, Excel and .txt (csv) files. Its quick,
easy, drag-and-drop simple, and very very fast. You can even have it
scheduled if you want to and let it send you the results via email.
(Just stick the transformation / job into the windows scheduler as a
command). If the task is really huge you can even have it spread the
workload across multiple machines for parallel computing.

After using this I have not a clue why you wouldnt use it for just
about every data comparison and migration you could think of. Truly
world class, and totally free.

Cheers

The Frog