Replication between SQL Server and MS Access
-
Hello,
I want to share / synchronize / replicate data using Microsoft SQL Server and Microsoft Access.
Description of problem/situation:
I have a SQL Server located in our head quarters and several others in other plants. Everybody needs to synchronize with the main db and every user must be able to update/add/change data in this db. I was thinking to use ‘linked tables’ to establish the link but than I heard about some users who would like to travel with their laptop. That’s why I would like to set up a replication.
I started reading but after 5 days I still haven’t find the right way… I’m getting desperate!
This is what I tried:
- configure publishing, subscribers and distribution (no real problems)
- create and manage publications using ‘merge publication’ and ‘heterogeneous data sources’ (no problems).
- create new subscription: I really don’t know how to point to the Access database (installed on client. I also tried to link with an Access db on the server but I also get errors). I already tried several set ups but still can’t get this thing working. I read somewhere on the internet that I need to configure a linked or remote server.
I also tried a variant using MSDE as subscriber (installed on each client) and than using MS Access to update/add/change the data.
And what will happen with the field ‘GUID’ when I add a row using MS Access?
Any help would be appreciated!
Bruno
- configure publishing, subscribers and distribution (no real problems)
-
Bruno, where ru? I wana give u kiss ( I mean hints here).
we have same situation with our corporate head office and all braches are using Access Application. What we got solution to all is use Replication with SQL Server to other branches. some branches need to send and receive all data from head office and some need only there own data, but head office need all branches data either its own or comes from other small branches.
Things which we are doing:
1- Migrate access Data tables to SQL Server (all clients need to use ODBC Linked tables option in Access and connect to branch - Their relevant SQL Server - I mean local branch server)
2- Configure Replication from Corporate SQL Server with all others branch's SQL Servers - (hint- Merge and if need filter used row filteration :: this is in our case when branches neeed only their own data and don't like to get more data headache as a data trafic on the Internet :pinch
3- that's it
If you need futher details feel free to come again. i will like to give u everything
-
Hi,
thanks for your reply. I managed to set up a replication between several SQL Servers but I also need a replication between a (local) SQL Server and the users laptops! There are a lot users who want to continue working with the db offline. I managed to arrange a PUSH subscription but I really need a PULL subscription. I read on the internet that I should try using Microsoft Replication Manager 4 to configure such a pull subscription but it's not available for download...
Any ideas?
Bye
Bruno
-
I'm not sure about the Microsoft's replication Manager 4 for Access can work for u or not. But Wat i best know about myself incase i get this task i use DTS and run it upon request or schedule it. use FTP and copy Data files on server, open those files and transfer data in some temp tables then run Stored procedures to check data and updates, make some strategy/logic to see new or updated records or overrite all data depending upon the business requirements and merge in One main server, which is accessible from internet to your clients and other corporate users. Let me know if there is any other solution for this- There is no one perfect. We learn by assignments, task and previous work experince and mistakes to make things better than before.
Shamshad Ali.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply