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".
This is the dumbest question ever.
One thing you could do is drop Access and use MySQL or keep Access to feed the data but have frequent exports into MySQL which are in turn queried by all the people around the world, it would work if most of them only need to take a look and not change things.
Maybe even my company can help you with this.
http://managedaffiliateprograms.com/contact.php
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.
Stop fighting it. Just get a real database. Why are you so hell bent on using Access? What you are describing is far beyond the scope of what anyone ever imagined when designing Access. Why are you trying to simulate the features of a real database, when they already exist, and they're good too... I'm seriously puzzled.
I used to bulls-eye womp-rats in my pants
but I speak out of my own experience.
:)
:)
:)
First, as someone previously mentioned, Access is not meant for multiple access. I'm surprised you managed to support >50 access databases for ~50 users. Local access alone creates multitude of problems, not to mention global access. I remember the days when they've to yell for access an Access database for update, kinda like manual locking mechanism.
Second, some people above mistook your usage of database is a pure RDBMS alone. Access itself is an application which embedded its programs and database into one big file. I fully understand the problem of porting Access to other means like MYSQL+Java. To minimize the impact of porting existing Access database data and applications, I recommend implement an extra applcation locking to each Access file when somebody request an update. You can also implement a single sign-on system for all ~50 Access database, this way you can have more justification for the changes. (this is much better than the manual 'yell-locking' I mentioned
Third, do as they said, port to MySQL+Java. It worths the trouble.
Hey, not sure if this is your problem, it probably isn't but try Tools->Options->Advanced->Default_Open_Mode->Share d. Works for me in an environment with ten or so people all using the one database at once.
That's assuming you are tied into an Access system, although I've found MS Access to be more reliable as a database than MS SQL. For example, in MS SQL you can retrieve a date record and it will be in Australian date format, put exactly the same data back into the database and it will be treated as US date format. Also this bug really shits me.
If your IT department is as anal retentive about MS as mine is, then you can't just use a real database like mySQL so unfortunately it looks like you're stuck with Access. But I don't find it all that bad, in fact I prefer using Access databases with a web frontend to any other system because you can treat the database as a file (unlike mySQL, MSSQL, oracle etc).
I am artificially intelligent.
Of course, the best solution is to step up to a real database. Failing that, you could try some of the following ideas:
If the database is small, you could have multiple copies of it, and sync the changes later. If it's really small, you could automatically make a copy for each user.
You could have a broker program (a middle tier) that is the only one which accesses the database. The clients would talk to the broker, not the database. This could be done easily with any of the middle tier technologies.
You could cache data to the client and reduce the number of accesses to the database.
There is not enough information in the original post. If your database is large or if your changes are time critical (e.g. users must see changes in real-time) then your only option is to upsize.
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
Like some previous posters have said, you are pushing the limits of an Access MDB.
MS SQL-server is the obvious recommendation, mainly for "compatibility" reasons. In fact, I've counted several such recommendations already. It's a "real" database, and a reasonably easy upgrade path (depending on how the code was written...).
That said, it's cheaper (i.e. probably free for your purposes) for you to use MSDE - which is really a limited version of the SQL Server engine. I think the only major limits are that the maximum db size 2GB, and the management tools are not included (you'll have to live without, or get a license to them some other way, such as getting a copy of Office Developer).
Having said that, perhaps you really don't want to touch the code... there are many ways to optimize an Access MDB. I don't think anyone has suggested making sure Samba isn't causing a bottleneck -- I recall reading about updates or settings that dramatically affected filesystem databases like Access MDBs.
Trying to use Access for 50 databases with a multitude of concurrent users the world over is simply the wrong tool for the job.
Get a proper database and your problems will solve themselves.
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.
Porting Access97 data to PostgreSQL
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.
Seriously, I want to know. Because if it's MY bank, our relationship is over.
I mean, Access - come ON!
...enterprise application? A number of people have already mentioned that Access is unsuitable for that, so I'll just add my voice in agreement and move on.
A couple of options that I see, particularly if you're primarily an MS house and/or don't want to rewrite your front end:
1) Get the backend into something else, like MS SQL Server. You can keep your forms and reports virtually unchanged. If you go that route, I recommend this book highly: Microsoft Access Developer's Guide to SQL Server
2) Post your question over here: comp.databases.ms-access. There are a lot of professionals in that newsgroup who are generally more than happy to tackle questions like this and have a tremendous amount of experience and expertise behind them.
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.
Please tell me what bank you are working at so I can be sure I never put my money in an account there.
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
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!
..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!
You could try to webify it. Lots of things talk to Access over the web; even from a Unix web server. Look at ODCBSocketServer+Perl for doing it from Unix; ASP, ColdFusion, WinPerl, etc. for doing it from Windows.
Of course, everyone else is right, you really, really shouldn't use Access for anything. At all. Ever.
Best of luck.Power corrupts. PowerPoint corrupts absolutely. E. Tufte
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,
A number of others have mentioned porting the back end from Access's built in JET database using file sharing semantics to another SQL database. MySQL has been mentioned, and the combination of an Access front end with MySQL servering tables via ODBC is a much more reliable, especially with multiple users.
It is possible to write Visual Basic for Access code that opens a database, looks at the tables, and generates SQL DDL and DML statements. Having done this, here is a list of gotcha's, or Things discovered as life goes on...
SQL Naming and Other Standards Checks
Check all tables against the following list while in Design view:
1. SQL names may not contain spaces. Access names may.
2. Access allows the use of some SQL reserved words, like INDEX, for table and column names. SQL obviously does not. These names must be changed to an SQL identifier (not a reserved word).
3. Set the Required property to Yes for all primary key columns.
4. Set the Indexed property to No for all columns that are members of a multi-column primary key.
More details on these and other topics are presented below.
Fields vs. Indexes
The Jet database engine splits fields and indexes into two separate hierarchies. One cannot tell when inspecting fields if they are part of an index or not. Instead one must navigate from the field level back up to the table level and then down to the index level.
Primary Keys and NULL Values
Access does not mandate table primary key columns to be NOT NULL. One can use a column in a primary key even if the Required property is No. What Access does instead is disallow NULL values in the corresponding index. So during data entry, an INSERT or UPDATE with a NULL primary key is rejected by the DAO at index update time, which precedes column update time.
MySQL will not allow this. It (and other database servers) mandate that no part of the PRIMARY KEY may contain a NULL value. In Preview, verify NOT NULL is specified for all columns in the CREATE TABLE statement when a subsequent ALTER TABLE ADD PRIMARY KEY statement is generated.
Primary Key Indexes
Access automatically generates an index for each column in a multi-column primary key. If indexing is also specified for such a column in the table design form, Access generates two different indexes on the same column.
To eliminate redundant indexes, do not index a column that belongs to a mult-column primary key.
Autonumber Data Type Support
Access does not allow setting the required property on an autonumber field. Set NOT NULL in an SQL DDL statement when both of the following conditions are true:
1. The column has the dbAutoIncrField attribute.
2. The primary key contains exactly just this column.
Access allows autonumber fields that are NOT keys. MySQL does not.
--Jason Kazarian
access2mysql@leftbrainedgeeks.com
Awesome! I have no idea why someone modded me "Overrated", but thanks man. I appreciate it. My post certainly was that!
Not All Who Wander Are Lost