Slashdot Mirror
Sharing MS-Access Databases, Efficiently?
codewizard asks: "Ours is a bank and we have a bunch of MS-Access databases(>50) which are being used by around 50 users around the globe on a daily basis. The set of databases are stored on a SAMBA share and each user accesses from the mapped drive.
As expected, sharing conflicts arise and multiple users are unable to access at the same time. So, we proposed having multiple folders on SAMBA each of which would have all the databases and the users logon script would determine where their mapped drive points to. This led to synchronisation issues (when a change is required in one of the master databases, we need to manually synchronise all other folders) and increase in storage size in SAMBA. Anyone have any other ideas on how you would have gone about sharing these MS-Access databases?"
Despite this, it can be made to run reasonably efficiently and reliably if you know how. I have a mate (Hi, Jeremy!) who does this regularly, and other things like rewrite the GDA (no shit, he did this) on his iPaq to not do stupidly pointless transforms, and to use integer arthmetic. The result is an eye-opening iPaq that displays maps in realtime instead of at plotter speeds.
Nevertheless, for your average gonzo it's too much work. And Microsoft products are a dead end anyway. As practically everyone else is saying, whack up an ODBC interface and hide whatever you like behind it by way of a real database. If you can make it web-based, that's an additional layer of useful abstraction that allows to to hotswap even more technologies.
No doubt I'll get a /. lameness filter message about too many syllables when I press this button... (-:
Got time? Spend some of it coding or testing
Get rid of Access as a database server. It is ok to use Access as a frontend to almost any "real" database server. If you have a samba server, you may want to put a PostgreSQL or MySQL server on that machine. Install ODBC sources for the server on the clients, and connect Access through ODBC to the server. In Access, separate program code and database, delete the database part, and distribute the *.mdb "Program" via the samba server. Your users can still use the user interface they are used to, but there will be no more file sharing conflicts.
Denken hilft.
Create a website to access the Access databases through. Use ODBC/JDBC connections to the DB instead of using local Access forms and reports. Beyond that you can also setup any database behind the scenes with it.
The company I work for deals with CU's for archival. Since I've been here we've used Access and MSSQL as our back end and IIS/asp/msjava combination. We are moving from that to a database independent and real java/servlet environment.
One thing to remember is that if you create a website to access the database data through you will need to fork out real money to either a developer to build the site and maintain it, or assign that as someone's job. Over all it is a good idea since you then can be on any type of machine and access/update the data instead of a machine that requires Access on it.
Norris/Palin 2012
Fact: We deserve leaders who can kick your ass and field dress your carcass.
I've seen a few posts saying that Access isn't designed for multi-user databases. It would be more acccurate to say it is not optimized for that function, but is fully capable of it.
You must carefully construct the Access database specifically for shared access; it doesn't do that by default. If your average user created the database, then it is NOT configured for multi-user access.
I created a small Access database that is used by around 40-50 people and the MDB file is shared from a NetWare server for security. You have to setup an Access "workgroup" and "join" that workgroup from each machine and have people login to the database. It does allow for some access control and does do record-level locking.
In short, I think you're having data access conflicts because your databases are not configured for multi-user access.
Ouch! The truth hurts!