i have a security application that stores its data in a access database.now i'm required to make a realtime synchronization (replication) between that access database and a new database in sql server 2005. these two database are the same. any suggestion?!
A:
Do you need to keep the access database or could you switch to Sql Express, or even forgo the local db and only use the remote SQL server.
GrayWizardx
2009-12-20 08:15:22
Now see, that sounds like a comment on the question, not an answer. Am I missing something here?
astander
2009-12-20 08:19:04
the application is running and fills the access database with new data. so i need it to be there. i need a way to transfer new records to sql server database.
muhremehr
2009-12-20 08:21:04
+1
A:
Mostly, I would suggest you use a windows service to periodically check the MS Access db, and attempt to synchronize it with the Sql database.
This will allow you to remove the Human factor, and have this task run periodically to sync the dbs.
Have a look at
Also
astander
2009-12-20 08:27:02
yes, windows service is a good idea, but before making a windows service i need to know how to extract newly added rows to tables and send them to sql server database.
muhremehr
2009-12-20 09:56:12
i prepare a tiny c# application. problem is how to transfer only newly added rows from access database to sql server database. (preferably i'm told to not use a custom coded application.)
muhremehr
2009-12-23 05:03:33
A:
i don't know how to do it usig a windows server or not. i need exact technical answer.
muhremehr
2009-12-20 09:31:48
+2
A:
SQL server has built-in replication functionality that you get for free, so you don't need to worry about copying rows & tracking changes. There are several types of SQL replication that are used for different situations, such as merge replication, snapshot replication, and transactional replication. This last one, transactional replication sounds like what you want. Merge replication is used when you have users that might disconnect, go away and return later to synchronize (like remote users). Transactional replication is used where the subscribers and publisher are reliably connected. Snapshot replication generates a new snapshot each time synchronization occurs, and doesn't think about changes to the data. Read the MSDN documentation and find which of these types is appropriate for your situation.
Using these replication methods will require that you set up your tables in a SQL server or express instance - you can use that to synchronize with your SQL server and keep everything else Access as the front end. I think you want to follow astander's suggestion and use a windows service to trigger synchronization. However you can set up the Windows Synchronization Manager to automatically try to synchronize at startup, shutdown, when the computer is idle, etc. If you need finer control over triggering the synchronization then perhapse use a Windows app or service as astander suggested.
Dale Halliwell
2009-12-20 21:55:54
i think sql server replication is only between sql server and rdbms like itself or mysql and others,, an not between sql server and access.
muhremehr
2009-12-21 14:52:54
SQL Server 2000 could do heterogeneous replication with Jet 4, but that feature was removed in SQL Server 2005.
David-W-Fenton
2009-12-22 01:02:38
What I am suggesting is Sql server replicating to a local Sql server or express instance, and linking to that instance with Access
Dale Halliwell
2009-12-22 02:00:56
the exact point is how the first sql server can grab new rows from access databases? it should be real time as i told.
muhremehr
2009-12-22 06:33:55