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?"

Comments

Re:If price AND ease of migration matter...

[ickle_matt]

MSE is optimised for up to 5 concurrent connections, after that it starts slowing down *a lot*.

Having said that, if you manage your connections properly in the front end you shouldn't have too many problems and it /is/ free...

Re:Oracle

[greenhide]

Actually, Access *does* support transactions. I'm not sure how well it does it on the client level, but I know for a fact that it does through the ODBC interface, because I've used it that way very successfully in the past.

I do want to comment, a little, on the huge number of people who have bad-mouthed Access in this thread.

I am not a lover of Microsoft, and we are moving our entire office over to Linux/OS, blah blah blah.

HOWEVER, I am currently moving sites over to using Postgres (yes, MySQL *is* faster, but that's because you can't do as much with it). One thing I've noticed--it is much, much slower than Microsoft Acccess over ODBC. Now, it's possible that if I was to do load testing, it would beat MS Access handily (in fact, I've tested this and it's true). However, for your run-of-the mill complex select query, MS Access handily outperforms Postgres on speed, with equally complex queries.

Everyone consistently says how using MS Access is inherently worse than using a database server. I'm sure, in cases of heavy load, it is. But if you have only 50 users accessing a database (and it's doubtful that they would all access the same database at the same time), Access will actually respond fairly well.

I'd like to add that when I was working on porting a website from using Access to using MS SQL Server, I noticed an instant drop in speed and response. We don't host that site, so I don't know what the setup is for their SQL Server machine, but queries took often 2-10x as long to execute. We switched over to avoid problems that came up a few times during the month when too many people were accessing the database. Now I get server disconnect errors all the time--a few times per week.

So for all those who've suggested switching to SQL Server as a more stable solution--<Bronx cheer>.

Oh, and also:

I wonder if they're even normalized?

This is, sorry, an arrogant and stupid assumption. The quality of data organization is dependent entirely on the designer of the database, *not* on the type of database used. It is just as easy to make a crappy flat database in Oracle as it is in Access.