When I link to a SQL Server table in MS Access via ODBC the local Access database does not actually duplicate the data. When I replicate a SQL Server table to another SQL Server database does it make two copies of the data, one in each database, and then keep them in sync?
Greg
if you setup replication, yes there are then multiple copies. that;s the reason for replication actually. Keeping in sync depends on the type of replication (snapshot, peer-to-peer, transactional, merge
If you mean having a linked server and accessing the data with a four part name then the data is only on the original database. If however you can do replication which as @gbritton says will create a copy.
Yes.
In the first instance Access is using a "Pass Through" query, so the query is actually passed to SQL, SQL executes it and returns the results, which Access then passes on to the APP.
In a replicated database if you SELECT something that comes from the database you are connected to. If you UPDATE something that that change is passed to all the other replicated servers, and each one updates its local table. (This usually uses a "Two Phase Commit" - the first phase is "Everybody ready?" and the second phase "Now do it" - if one of the servers disconnects between the two phases the transaction is still guaranteed to proceed - I have never understood how that is possible and it seems to me to be a Dark Art!!)
There are numerous variations-on-theme, so what I have described is very "broad brush" and it doesn't have to happen that way. For example if you are working on a Laptop, which has a local "replicated" copy of the database, you can do a quote for a client whilst you are in their office, with NO connection to your central database, and then when you get back to the office your Laptop Database will replicate to the Master Database. In this instance the replication has to allow for Collisions (someone else already created that record ...) or the product you quoted for no longer being in the Product table on the master database!!
Excellent. Thank you all. This transactional replication.
Greg