How to keep SQL DB and Access DB in sync

Ben2k
11-15-2004, 03:26 AM
Hi,

I have a project which i'm trying to do for a client.

It starts with an access database on the local client machine, plenty of fields but the main ones are DealerID, VehicleID, Make, Model

There is also an SQL server on the internet which drives a website, it has the same fields, DealerID, VehicleID, Make, Model.

I need to write an application that reads the local access file and updates the SQL server. What is the best way to do this?,

At first I thought delete everything from SQL server then upload everything from Access but that doesn't seem like the best way, Really I need to delete everything from SQL that isnt in Access and then insert everything from Access that isn't in SQL, also it needs to be able to update for example when a record is simply modified in access.

I havn't really done much database work, I hope someone can point me in the right direction.

Best Regards
Ben

KnooKie
11-15-2004, 06:37 AM
Id you're using SQL Server 2K then you might want to look into its DTS capability. Might be a big learning curve if you have little db exposure.

Also look into Linked Servers which may be your wy forward

These may help a bit.......

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=35873&SearchTerms=synchronise

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9480&SearchTerms=synchronise

MKoslof
11-17-2004, 06:04 PM
In addition, if you want to use a client side Access application as a "window" to the SQL Server database, you can use an ADP application (if using Access 2000 or higher). But, if the sole purpose is to take data from the Access database and send it to the SQL Server database (with no future integration), the DTS is the best way to go.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum