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?"
This is the dumbest question ever.
MSE is optimised for up to 5 concurrent connections, after that it starts slowing down *a lot*.
/is/ free...
Having said that, if you manage your connections properly in the front end you shouldn't have too many problems and it
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.
Karma: Chevy Kavalierma.
Ok ok. Let's back up here.
First, hopefully you're using just the "client" version of Access, which doesn't allow editing of forms/reports/etc., just viewing and executing.
Second, you shouldn't be having your users all access the files at the same time on the same share. It's just asking for trouble (especially since I don't know how compatable a Samba share is with Access's sharing methods.) You'd be better off keeping a local copy on each client's computer. You could do this using a logon script, or if you're savvy enough, could even code it inside your Access application itself (on startup, check it's own version against the one on the server, if not up to date, warn the user, etc.)
A common mistake that people make when working with Access is to try to use it as anything more than a "front end". Sure, it might be 'easy' to code in, but it's pretty damn sloppy and inefficient. You're better off making nice looking forms which call pass through (re: stored procedures) queries on the server, which in turn handle the logic and data processing for you. (You are using Oracle or MSSQL, right? God I hope you're not using Access's tables to store data..) Linked tables are the _worst_ way to do things.
Also, as many others have said on here, you're better off translating your Access database into a web interface...
Not All Who Wander Are Lost
Linked tables are the worst way to do things in Access.
Access has this nice "feature" where it sometimes decides that the ODBC data source can't do the proper filtering (especially when you're using Access's query editor, which has it's own functions for strings and dates) and this causes Access to grab the ENTIRE table and do filtering on the client's computer (running Access.) How insanely stupid is that? Don't even get me started on the amount of locking it does, or the amount of network traffic it generates.
You should be using pass through queries and stored procedures as often as you can be. That's the only proper way to code in Access. If you can't do that, you should be making a web interface or something else.
Not All Who Wander Are Lost
..Don't even get me started on the amount of locking it does, or the amount of network traffic it generates.
Remember that he stores all those access 'databases' in a samba share. Rigth now, moving and locking entire tables ocurs for every god damed query, network traffic can't get worse than that... well maybe moving the entire database for every query could be a little worse.
16,777,216 comments ought to be enough for any forum!
Word of caution:
If you use MSDE, please be sure to set an "sa" password. And don't forget to install SQL Server patches....
It has been my experience (with thousands of customers using MSDE) that it is generally maintained poorly - when there is no UI, people tend to forget about it. I'd guess that the propagation of the recent SQL Server worms were due, in good part, to MSDE.
Other than that, it's a good way to go.