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?"
Seriously. Port the data itself to MySQL, PostGres, MSSQL7 (If you're a bank, you can likely afford it. ;) ) or something like that.
If you have custom front-ends built in Access, you don't have to abandon them -- using ODBC, link the tables from the database server to the forms/reports/queries you're using now in the Access database. It may take a little bit of doing, but I think you'll find it'll work much better.
Ed R.Zahurak
You know, oblivion keeps looking better every day.
Don't use Access.
No, seriously. It's not made for multi-user access. Use SQL Server, which is easy enough for Microsofties to translate over to (SQL Server 2000's table design now looks almost exactly like Access'). Or use MySQL or PostGres, if you don't want to shell out bucks. Boom. No more multi-user issues.
If you've got forms or reports or what have you in Access, translate all the data to some other database anyways, and then use linked tables. You'll save yourself so much heartache. If this database has updateable data, you may have to worry about concurrency issues, but it'll be piddly compared to "every user but one is locked out".
Fucking breathtaking, innit. Made all the worse by being prefixed with "codewizard asks".
I write a blog now, you should be afraid.
Several things: .MDB files over slow links, you should have no trouble with at all. Be *SURE* that you've split your Access database in two .MDB parts - the front (graphical, reports) and the back end (data). Link the tables in the frot-end to the data--end.
.MDB to be modified if you have code that doese things like this_form.width = 400. Access tries to get write access on the scewiest of things, so you don't want the data part of the database to suffer just because Access it rtying to get write access on a form.
.MDB files.
.MDB files) has to read large chunks of data over the network in order to run it's queries. If you do a select * from customers where customer_city = "redmond" Access will read the entire customer table over the network. Yuk.
Unless your users at accesing the
Also - have the front end copied over to the users hard drive - this limit network usage and will cause their local copy of the
Turn on oplocks on your Samba config for the data
Get a real database: Access (with its
If you give the same query to PostgreSQL, MySQl, or DB2 - only the query is sent to the server, and only the relevet rows are returned. A much lower bandwidth requirement - you can reasonable expect to run a properly designed database over a 56K modem conection with good results.
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
In this case, "codewizard" is referring to the new Microsoft Access Code Wizard. Apparently, someone at the bank clicked on the "debug" button in the error message dialog they got when their Access database was locked. This, of course, launched the "codewizard" which promptly generated and submitted this Ask Slashdot post.
It could happen... right?
Seriously, I want to know. Because if it's MY bank, our relationship is over.
I mean, Access - come ON!
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.
Okay, first thing first. This is running off of SAMBA. You need to upgrade these MDBs to SQL Server (standard). Don't argue, just do it. If your boss doesn't agree just point him to this slashdot posting and he'll get it.
Next, you have to convert the Access file to an ADP (Access Data Project). When you create an ADP you can tell it to what database to connect to. This is GREAT when calling the 'connection' property, because now you don't have to create a connection to the SQL Server database each and everytime you want to do something. You just do this:
Dim ADOrst as New ADODB.Recordset
ADOrst.Open "sql statement", CurrentProject.Connection
THAT'S IT! The biggest headache will be to convert DAO to ADO. It's worth it. Take a look at this link: http://www.msofficemag.net/features/2000/01/vba200 001sf_f/vba200001sf_f.asp. It will help. There are other sites, use Google, that will show you how you can use your DAO connection code and make it look like ADO, so it gives you time to actually do the manual recoding will keeping your users happy and connected. This is a short-term band-aid. You are still going to have to re-code to ADO, but is not that difficult.
**BENEFITS OF ADP**: You will have the power to accesss views, stored procedures, tables, as if they were local. I was a bit scared when I couldn't use my queries in access, but I converted them to either views, but 85% of them were converted into stored procedures. They are a lot better, because know I just setup my stored procedures to accept variables, sql server does the grunt work for data crunching, and then my recordsets were returned back very quickly. Here is an example:
dim rst as new ADODB.recordset
rst.open "exec proc_stored_procedure_name " & variable1, CurrentProject.Connection
If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
Do While rst.EOF = False
{do something with the records}
Loop
End If
You can use your stored procedures over and over again for different parts of the program without having to recreate the wheel everytime. I know you probably use queries or tables as lookup sources for drop-down boxes. Create a View or Stored Procedure and then set the controls "Row Source" property to the view or stored procedure. To do it programatically do this:
cntrlDropDown.RowSource "proc_storedproc_or_view"
That's it. You can also pass variables to the stored procedure and return the recordset to your control. In access I had a seperate query for each drop down box for hierarchy data, now I just have one stored procedure and pass it a variable to tell it what listing on the heirarchy I need.
One major benefit of stored procedures is that they are cached and optimized on SQL Server without having the database having to optimize it every time it runs. It keeps the plan already and saves time there.
Next, convert DAO to ADO. If you have something like this for DAO:
Dim db As DAO.Database
Dim rst as recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("sql statement")
Then you should change it to this:
Dim rst As ADODB.RecordSet
rst.Open "sql statement", CurrentProject.Connection
If you also have a recordset object, you just need to modify it like above. You can do away with the database object if you use the CurrentProject object. You no longer have to close the database object either since it was never created, but you still need to close the recordset object and set it to nothing, like so:
rst.close
Set rst = Nothing
Everything should still work fine. One more note about using ADO. If you use the AbsolutePosition property, in DAO the value starts with 0 for the first record, in ADO it start with 1 for the first record. I have to learn that the hard way.
I know ADPs are fairly new,