Access and Replication
May 3, 2010 8:21 PM   Subscribe

Sharing an Access database among several small groups using replication. Doable? Am I on the right track?

I'm developing a relatively small project using MS Access 2003 (database+forms). I know that Access doesn't play well with many concurrent users, so my intent is to use the replication feature.

The database will be used to read, modify, and create information about several products. Since there are different groups responsible for managing different products, I would like to have one master database and several replicas, which will be used by each group. Using the Access replication feature, each replicated database will be filtered by the product group that the replica is intended for. All .mdb files will be kept on network shared drives. For example, group A will receive a replica to keep on their network shared drive with only product A available; same thing for group B. I will have the master, and will -- from time to time -- synchronize my master with all the replicas so that mine has everyone's data but theirs continues to only have their data. I might also be making changes on the master side.

Each group would have a maximum of about 3-4 concurrent users on their replica.

I am stuck with MS Access for various reasons, so please don't suggest switching to something else. The expected database size would be about a dozen tables with up to a few thousand records each (a couple maybe reaching into tens of thousands, but not more).

So, is what I'm proposing reasonable? Am I completely in left field on this one? Will the database easily get corrupted? Will replicating the database ease the problems regarding the number of concurrent users? Is there a better solution?
posted by Simon Barclay to Computers & Internet (20 answers total)
 
Oh dear god, this sounds like a mess. Why can you not use pg, and views that limit access to 'A', 'B', etc. data?
posted by orthogonality at 8:37 PM on May 3, 2010


Best answer: Depending on how the users use the database, this might not be a problem that needs solving. I've found that Access can handle larger numbers of users without any hiccups.
posted by Pants! at 8:39 PM on May 3, 2010


Response by poster: To clarify a little: The replicas that each group receives don't need to be filtered -- I just figured that it would make them a little lighter and therefore less likely to have problems.

orthogonality: What do you mean "pg"?
posted by Simon Barclay at 8:49 PM on May 3, 2010


Go with what Pants! and orthogonality said. The usage patterns you are describing will be fine for concurrent use without much issue. The problems inherent in trying to replicate and sync multiple copies far outweigh any upside.

The only real issue you may come across is records being locked when two people try to access the same data at the same time. Replication would actually make that problem worse because both people would successfully make their change and now you have two copies with different wrong data.
posted by Babblesort at 8:51 PM on May 3, 2010


Response by poster: Thanks for the answers so far! A couple more things before I go to bed:

My understanding is that Access breaks down once you pass about 10 users on a shared .mdb over a network -- it supposedly becomes slow and can easily be corrupted. Is this not the case? I've also read that you can split the database into a file that contains only the database, and another that contains forms, etc. Is that a more appropriate solution in this case?

I've never done this type of thing before, so I want to make sure that I don't get to the point where people start using it and then I realize that it's impossible to share nicely.

(As I implied earlier: Assume anything that requires expending money is not available; if it were, I wouldn't be here. So no consultants, and no alternative solution.)
posted by Simon Barclay at 8:58 PM on May 3, 2010


Response by poster: correction: "... no alternaive solution software."
posted by Simon Barclay at 8:59 PM on May 3, 2010


Best answer: I've seen that number 10 out there too, but have never personally run into a problem with it.

In terms of splitting the databases into a "forms" database and a "table" database, I'd recommend this - you can have a local forms database for each user on their computer, and the tables database on the network drive. This is how I deployed databases for 20-50 concurrent users when I used do do this several years ago.

You might want to talk to your IT department to make sure the network drive is backed up at least nightly. If it isn't, make it so, even it that means it's someone's job to copy it manually.
posted by Pants! at 9:17 PM on May 3, 2010


pg = postgresql. It's free and downloadable here. And it's a great database.

Mysql is also free. It's better than Access, more user-friendly but less stable and featureful than pg.

If you're married to Access forms, use Access for the frontend forms, and use odbc to make pg the backend.
posted by orthogonality at 9:21 PM on May 3, 2010 [4 favorites]


Best answer: I didn't have any problems after I split a simple DB into a shared data file (with just tables) and an app file (with everything else) and copied the app file out to 20 or so users. I understand there's another way, using mde files which are safer and faster for users than mdb but I didn't learn how to do that. I intended that the tables be able to be imported into something else (postgres or mysql or mssql) but the whole system got superceded before it could come to that.

Anyway, check this out:

http://www.dummies.com/how-to/content/securing-your-access-2003-database-as-an-mde-file.html
posted by wobh at 9:24 PM on May 3, 2010


If you're frightened by needing to deal with open source databases, and you're going to be using Access as a front end anyway, Microsoft's SQL Server might be a more natural fit. The Express editions are free to deploy and should comfortably handle anything you might contemplate using a .mdb for.

On behalf of your users, and the next person who will get to maintain this thing, I beg you: please, please, please don't do the replicated .mdb thing. Please! Backup will be a nightmare, users will make unauthorized copies of the .mdb that you don't know about, there will be horrible issues with generating unique primary keys, and it will never be clear which .mdb is authoritative in case of conflicts. Please, please don't do it that way. I speak as one who had to waste far, far too many hours cleaning up after a commercial product that Just Grew from an end user's Access app, and used replicated .mdb files. It was a pain in the arse from soup to nuts.
posted by flabdablet at 9:37 PM on May 3, 2010


If there can be independent updates of a given piece of data I would not do what you are proposing, then again I wouldn't use Access for any shared data.

This page http://office.microsoft.com/en-us/access/HP052408601033.aspx suggests 'replication' for road warrior scenarios.

First things first, is the data partitioned, is this one or many databases? I.e. is there (updateable) data which is to be given to the different groups or is each group operating on its own data?

In the former case you have a potential nightmare on your hands with merging independent updates and all I can say is that this is what real database management systems are for. In the latter case your master consists of a number of separate databases, each of which is given to at most one product group and 'merging' is a non issue.

Go through your data and decide what is read only, what is updateable by exactly one product group and what is updateable by more than product group. Anything in the last category is where your issues will be.

Have you considered some kind of browser interface to this database?
posted by epo at 3:46 AM on May 4, 2010


I, too, would recommend that you avoid replication.

If you're tied into a Microsoft workflow, try SQL Server Express, it's by Microsoft and free to use. You can import the tables from Access or even use the "Move Data to SQL Server" feature in Office 2007. Then you can give the forms out to whomever you need to. The database always lives on the server you installed on, so make sure that's a server that's always on and regularly backed up.
posted by advicepig at 7:39 AM on May 4, 2010


I was going to make much the same suggestion that orthogonality did; I'd probably use a real client-server database for the backend, and then build the frontend interface in Access, connecting via ODBC, if that's what the users are familiar with. There are a ton of tutorials and documentation on how to set this up. Everything you are thinking about doing with replicas you can do via the database's security settings, and it keeps all the data in one place.

You could also do SQL Server if you want, but I really don't think it's necessary; MySQL or Postgres are both pretty easy to set up and install. And it would be transparent to the users either way. But if it's a total Microsoft shop and they're just absolutely, 100% unwilling to do anything that's not from MS, then I'd use it. It's not really a bad database (anymore), my feeling is just that most people don't need to pay for it. There might be an argument for it if you're converting or migrating a lot of Access tables, since Microsoft gives you some pretty easy-to-use converter tools, but if you're starting from scratch I can't see much reason not to go with the free one.

I've never seen the replication feature of Access used properly. That's not to say that it can't be used properly, just that I've never actually seen it done well. (Although I have seen people do some really awful things involving Citrix just to avoid using it, for whatever reasons.) The only argument I can come up with for replication instead of a more traditional frontend/backend DB app is if you needed offline access — e.g. people out in the field need to be able to make changes and then synchronize later on. But that would be a big can of worms unto itself and I urge you not to go there if you can possibly avoid it.

I'm not an Access hater, it's just that people have a tendency to 'stretch' it beyond where it comfortably goes. As a desktop database it's pretty nice (and a big improvement from abusing Excel as a database, which a lot of people do). And as a way of rapidly creating interfaces and for generating reports, it works quite well. But if you're going to set up a multiuser database you might as well do it right and use software that's designed expressly for that purpose on the backend. I'd much rather have software that's a little overkill for the job but you can grow into, rather than software that's a stretch, or just meets requirements, at the beginning and you're going to end up having to babysit or migrate away from in the future.
posted by Kadin2048 at 8:01 AM on May 4, 2010 [1 favorite]


Best answer: My understanding is that Access breaks down once you pass about 10 users on a shared .mdb over a network -- it supposedly becomes slow and can easily be corrupted. Is this not the case?

This was true for early versions but Access 2003 is good up to about 40 concurrent users. Much more than that and you should connect it to a proper SQL back end. The other limitation you should know about is that Access has a hard limit or either 1 or 2 GB (depending on version) once your database hits that size it just stops working.

I know a couple of people who tried using replication with Access and they both ran away screaming.
posted by Lanark at 11:34 AM on May 4, 2010


Response by poster: Wow, thanks for all the information. NO REPLICATION. Got it!

Regarding PostgreSQL, MySQL and MS SQL Server, I'm convinced that these are better choices, however these are all options that require resources (a server + maybe money) that we don't currently have available. If the database I'm developing catches on, maybe we can get some resources, but right now I'm doing this without any budget to speak of.

I realize now that the part in my question about filtering data led to some derails. I don't need to limit who sees what, I just thought it would be faster. If it doesn't help, then I don't need to do it.

So, to recap, I should:
1. Split the database into a front end and a back end file.
2. (optional) Compile the front end file to an MDE.
3. Distribute the front end / MDE.

I'm a little unclear on point #3: Would it still be helpful if I had the front end in the same location on a network shared drive as the back end, or would it only be worthwhile if the users each have a copy on their PC?

Thanks again everyone!
posted by Simon Barclay at 7:25 PM on May 4, 2010


these are all options that require resources (a server + maybe money) that we don't currently have available.

Running a database server for a given number of clients shouldn't stress the hosting hardware significantly more than having the same number of clients access the same database as a .mdb on a shared network drive. Can you not simply install PostgreSQL on the same machine you were intending to use to host the shared .mdb?
posted by flabdablet at 7:34 PM on May 4, 2010


Regarding PostgreSQL, MySQL and MS SQL Server, I'm convinced that these are better choices, however these are all options that require resources (a server + maybe money)

No, no, no. What flabdablet said. I have a netbook with an Atom processor. On it I run both mysql and pg. Now, no, I don't have more than a few users/sessions connected at a time, and they're local connections. But I was able to handle a database holding every voter in the State of Virginia. (Yeah, OK, it was slow.)

But you get a desktop, you download pg or mysql, and that's basically it. You get a permanent local address (modern routers can do this), and away you go.
posted by orthogonality at 10:44 PM on May 4, 2010


I also run mysql and postgresql on a laptop (a 6 year old Powerbook with 1Gb). If you want to get really fancy then install the database into a virtual machine and have your own database server.

The resources you need are time to learn how to install and manage the new system and also how to connect an access front end to some other database backend.
posted by epo at 2:32 AM on May 5, 2010


Would it still be helpful if I had the front end in the same location on a network shared drive as the back end
The advantage of separating data and code is that if anything goes wrong with the MDE file you can just delete it and replace with another copy.
Now if you have say 5 identical MDE files and one of them get corrupt, that will only affect 20% of the users until you replace that file.

It really makes no noticable difference if the MDE files are on a network drive or held locally on the PCs.
posted by Lanark at 11:10 AM on May 5, 2010


I would recommend that users have their own App/MDE files. When two users opened the same App file on the network share (which I named something like "db_app_COPY_ME.mdb") they experienced all kinds of lock-out problems. This would happen even when only one person was using the file, and the person before just hadn't closed it properly or something—I never quite figured out why that would happen, but it did. When I trained them to make their own local copies of the app file everything went much smoother for them.

One caveat I have about Postgres, Mysql, etc.: if you expect to stay and grow up in a windows environment make sure whatever your next DB can use Active Directory authentication. That way you can manage users and access permission with your ever-developing AD. I never figured out how to get the version of Postgres I was playing with to do it (I'm sure it's actually easy) and that delayed migration indefinitely until we moved on to something else.
posted by wobh at 9:30 PM on May 6, 2010


« Older Did I have a lame superpower?   |   Can I scrub a sea turtle too? Newer »
This thread is closed to new comments.