I'm trying to take a SQL database (I think it's SQL Server 7) and an Access97 database and compare them on a regular schedule automatically.

The Access database has information in it that is preliminary information. The final information is entered into the SQL database at a later date. However, the Access database contains fields the SQL database does not and those have to stay intact, and vice versa. What I need to be able to do is compare the records in the SQL database against those in the Access database, and if a preliminary record has been finalized in the SQL database update the Access fields with the correct information. There are certain key fields in both databases that will be the same, and unique, so a comparison for equality is possible. Ideally, this would happen on a regular schedule, without any human action.

Is there an easy way to do this?

Thanks, Adam Grimm

-- Anonymous, June 25, 1999

Answers

Adam,

Reading the description of your situation it is a little unclear whether both the SQL Server and Access databases need to be updated. But, if only the Access database needs to be updated, you could (as a scheduled task) bcp the SQL Server data out, read it into an extra Access database and apply the updates.

If the SQL Server database needs to be updated as well, I would write a tool (in VB, PowerBuilder, or whatever) that uses ODBC to look at both databases, compare them, and apply the updates. The operation of this tool could be scheduled.

This sounds like a bit of work. You might also consider eliminating the Access database and just connecting your existing front end to the SQL Server database instead.

Hope this helps,

Eric

-- Anonymous, June 29, 1999