Replicating Databases

[DanG]

Hello,

At work we have Citrix which is like a shared desktop on a network. Our company will most likely be getting rid of it soon and we have many Access applications that we share.

I have heard about replicating, but never used it and would like to know your thoughts on it. We currently have two main database that are linked together that are my immediate concern. I would invision having a master database and then having the users send thier copies to my to replicate their data into the master and replicate any database changes into theirs and send it back to them.

Is this kind of how it works?

Thank you!

 

[madEG]

You can go multiple routes, but some things to consider would be how to handle replication conflicts. Whereas in a single db instance, if I try to change a record that you are changing, we are prompted to handle the situation right then...

But, when the transaction is committed differently in different areas, reconciling the differences will need some thought. You'll need to have a policy in place for what data "wins" when one person has committed the data as blue, and another as green (or whatever).

Are your workstations on a network? (I imagine they are if you were using citrix) Maybe it's time to consider having a single dbms engine as the backend (e.g. sql server) that everyone can look to when working from their front end apps. Depending on how many records you want to store, what type of org you are, how much admin tools you would want, you may be able to get away with using SQL Server Express --> Low cost and not that hard to work with given a little time.

You could even go the route of having/separating out the data tables from the application and have all client mdb's linked to the master data mdb... That works fine, but you'll need to manage mapped drives to allow for the linked tables to "see" where the data that you are sharing is located.

just some thoughts...

More: http://www.blueclaw-db.com/broad_interest/database-replication.htm

 

[dfenton]

Whoever is getting rid of your terminal server is a complete idiot. Try to convince the powers that be that it's a foolish decision. It doesn't have to be Citrix, which is more expensive and complex than plain-vanilla Windows Terminal Server.

If there's no hope there (as there so often is with IT morons), then convince them to wait until Access 2010 is out and you can then use Sharepoint 2010 to share your databases.

I'm a long-time user of Jet replication, but I have been recommending against it for a long time wherever Windows Terminal Server is available, and the users don't need to work disconnected.

A2010 with Sharepoint 2010 adds important new aspects of referential integrity and other functionality that can now more than adequately replace Jet replication (previous versions lacked too many crucial features to be able to do that). It also supports disconnected editing.

If they won't implement Sharepoint 2010, then they are really, really not competent. Somebody will end up spending a lot of time, energy and money implementing indirect replication in order to solve a set of problems that could be very reliably solved with much less expensive, easy-to-use and reliable tools.

 

[DanG]

Thank you guys!

I work for a very large company, so as they plan to get rid of something they are less than receptive regarding adding new things.

We do have a shared network, but the isssue is speed. If I use the shared drive using the database is unacceptably slow (but it does work). Maybe there is a way to speed it up?

 

[DanG]

Thank you for the food for thought guys!

I started thinking of different approaches and one thought occured to me. Since I do have a shared network (but very slow)...what if I did not give the dataentry form a control source, that would eliminate any query being run as the form starts up. Then I could (by some way) have the users enter the data into fields (with no control source) and then have a save button on the bottom of the from that takes the fields data and append them to the table(s).

That's as far as my thought goes, it would be a pain but maybe a thing to consider. I had read about this method on this forum years ago (I think from Pat H.) and it seemed very interesting.

Does anyone know of any good threads on this approach here on the forum or if it is worth considering or not?

Thanks again!

 

[dfenton]

You can speed up an Access app over a slow connection only so much. Using unbound forms will be faster but will require a huge amount of code (you lose all the form-based events, even Me.Dirty no longer works). But you'll still have slowness retrieving the record and saving it -- there is simply no getting around that.

You need your boss advocating for a sensible solution to your problem. Rewriting with unbound forms is not a sensible solution. The ones I've suggested (keeping Citrix, using Sharepoint) are sensible solutions, and somebody in IT needs to have sense talked into them. If that requires escalating up the chain of command, then that's what needs to happen.

Absent that, look for a new job, because it's going to be holy hell if they don't provide a proper solution to replace Citrix.

 

[DanG]

Thanks again for the input.

You pretty much confirmed everything I was thinking on the subject.

I have played around with replicating, but am a bit hesitant as I can see it wold be a pain to manage and have noticed a few quirks.

Thank you

 

[dfenton]

Installing, configuring and maintaining Jet indirect replication requires a lot of care and attention. It's similar to user-level security in terms of complexity of concepts, but much more complicated to implement (without Replication Manager, there are no wizards to walk you through it). It's a great technology, but as far as I am concerned, 75% of its use scenarios were obsoleted when MS made Terminal Server a standard part of its Windows server versions. At that point, serving multiple remote locations was much easier with Terminal Server than with Jet replication. The only case where I consider it to have remained compelling is for laptop users who need to edit when disconnected from the Internet, and most of those don't need to synch from the field (they can wait until they get back to the office).

Thus, the number of cases where Jet indirect replication is desirable are very small. With Access 2010 and Sharepoint 2010, those use cases will vanish entirely.