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".
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.
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.
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.
Porting Access97 data to PostgreSQL
...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.
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