Replication

[MazeWorX]

This is an area of access that I have VERY limited knowledge and I was hoping one of you out there could answer a few questions for me.

Is a replicated db unique? in other words do I have to create a new replicated db for each indiviual user?

Can a filter for a partial replica be passed programtically with vba?

Thanks inadvance M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MakeItSo]

Is a replicated db unique? in other words do I have to create a new replicated db for each indiviual user?

Think about what you replicate: you replicate data / data structure.
If the db is a backend-db, i.e. one single db all users log into, the replicating this one db surely suffices.
If you want to replicate user settings then some INI file or similar at the user side would be appropriate.

How do your users work with that db? Do you have frontend DBs with forms/reports and with the backend tables linked?

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MazeWorX]

Background:

I created a db used to plan and predict productivity. It was initially designed to be a stand alone tool with a split FE and BE which imported data from a repository Drive however it was well received ... alittle too well and I was asked to pilot it to several remote sites. Of course the next step was to be able to share data between users. There are approx 140 remote sites with 3-5 users at each site. Sites are unique and only require 'their' data to use the tool. Initially I need to use a partial replica of the design master to supply each site with filtered data. In order to share data it is normalized at the server level then syncronized. now before you say it, I am well aware that this is probably not the optimal solution for what I want to do however all i have available is a file server to host the Master. Like too many companies out there I have to show the value of the tool before I can get the required tools to build an enterprize solution. I would like to leave it as a standalone with its own FE and BE for performance using replication to share data accross a WAN. So to answer your questions It is a split db. Users log into the FE using a username password function I wrote. So what I really need to know is can I replicate the BE then copy the replica for distribution or do I have to replicate a unique copy for each user. I also want to know if it is possible to apply filters for the partial replica based on a users setup . Currently the user, during setup selects the locations to filter imported data. This info is stored in a table on their BE can I use that info as a filter for my partial replicas.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MajP]

You have to make a replica each disconnected site. You should never make a copy of a replica. However, if you are suggesting 140 sites in your replica set then you are in for a lot of pain. Although I believe the theoretical limit is 255 replicas, I believe you are well beyond a usable limit. I would give you a week before you corrupt the database and it becomes unuseable. You might be able to demonstrate the buisness model over several sites and have it work well with 10-30 sites. But I would not consider going beyond 30. However, the real issue is not the number of replicas, but how often and how much data is synchronized. If you are talking about 140 users synchronizing on a daily basis, I would give you a day. I may be wrong and I would like to get someone elses opinion, but I find replication to be very finicky. It takes a lot of fine tuning to avoid corruption.

 

[MazeWorX]

140 remote sites or probably less as some do not have access to the Network but most do. The data will be one way from the master to the remote sites some daily some weekly. This data is used to buld plans which will be saved to another db. Of course the problem is trying to connect so many sites over a WAN using access. Potentially I could break them down into thier regions significantly reducing the traffic to a single db. One region may contain 16- 20 remote sites enough to demonstrate the business model efficiently

Side tracking a little from your post MajP I have figured out how to create the partial replicas from code so I have answered my original posting Having said that I would welcome any further input you may have on replication

Thanks M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[lameid]

It sounds like you are using replicas to simply filter data.

I have never used Replication for two reasons...

First, increased corruption problems.
Secondly, conflict resolution methods cause me to anticipate problems.

I do not see you synchronizing your replicas to incorporate data changes so the second should not be an issue and the first may be diminished. However books I have read suggest writing your own replication system because Access's has so many problems.

So I would suggest you forgoe replication and simply have code that dumps out the data each site needs and push or pull the file for them to report against. That is probably the same thing you are doing now with out the replicas.

This sounds like a distributed reporting solution... I am guessing SQL Server using SQL Reporting Services to automate sending reports via e-mail or deliverable on the internet may be a more appropriate overall solution. I mention this as you may already have the SQL Server. I am not sure but the report e-mail feature may be an Enterpise edition feature.

 

[MazeWorX]

The tool has been designed to plan productivity for tomorrow using yesterday’s data. With the tool you create plans and need to share them with other levels of management there is also other data that needs to be edited and shared. The tool will eventually replace a spreadsheet tool that each Terminal uses for budgeting the following year. Performance data is used to budget wages etc.

I have created a replicated version of the tool but have drilled the data files down to 6 divisions dividing the number of users per replicated data BE. I have also installed and started testing this week. My intention is to bring online a single division until I either have everyone using it functionally or I break it giving me my max number of users per replica which may mean potentially cutting the divisions in half as an additional safeguard

I wish it were as you say a reporting only solution life would be so much easier. I did however as a side note pickup The Developers Hand Book Enterprise edition and the Desktop edition ... don’t know how I survived without them

Thanks for the input … food for thought … At the higher Management levels reporting is probably all they will need

M


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[lespaul]

sounds more like you want to create a data warehouse. You want to take the data from each site (where each site only needs to see its own) and combine it into a single database as a source for management....

Leslie

 

[lameid]

I agree with Leslie.

I would probably add a table to track the exporting of data and use it's PK to flag records sent to the warehouse.

Then you could push back a report of all sends received to reconcile data is being imported.

 

[boblarson]

For Replication, a good resource is here:

http://dfenton.com/DFA/Replication/index.php?title=Main_Page

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com