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

*ditch* Access, sorta

[TrebleJunkie]

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.

Re:*ditch* Access, sorta

[eakerin]

I have to agree with this, where I work we had many Access databases spring up over a few years (ie, not developed by IT) , and people started complaining to us when they started breaking.

For some of them we just fix the corruption, and move on with life, but for the ones that were used more often/break more often, we converted them to an Access Front End (and therefore no code changes) and a MS SQL 2000 Backend. (please no flames, we are currently a MS Shop, and that's something I'm already trying to fix. :)

This Solution works well for this kind of system, no data curruption problems, and we don't spend 3 months re-writing the whole thing with no gain but stability.

Now on to the Helpfull hints if you attempt this:
1. Using DSNs in ODBC is a pain, Write some code to automatically create the DSN when the database loads,if it's not there already. so that users just have to open the database and it works, same as before.
2. if your using MS SQL 7/2K, use NT security for user access, it will simplify life a lot, that way you won't have to create a SQL user for everybody
3. it looks like your using a nix derivitave already, just try postgres on your existing server (or if you can, setup a new one), and test the heck out if it. I can't provide any insight into problems, cause I don't have any access-postgres databases running right now.
4. If you have access to MSSQL 7/2K, even of you don't plan on putting the data into a MS Database, DTS Import/export wizard will be your best friend in this endeavor. It will make life VERY easy to transfer the data from an access DB to ANY other ODBC Datasource (so pretty much everything, including flat files) You basically say, copy data from here, to here, and these are the tables I want. Hit go, wait a while, and when it's done you have all of your data tables nicely transfered to the new server.

Going this route you get the best of both worlds, the Stability you need, and the short developement cycle everybody wants.

Hopefully, if you end up going this route, some of this information will help.

By the time I finish, this will be redundant...

[Violet Null]

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

Somthing wrong here..

[zulux]

Several things:
Unless your users at accesing the .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.

Also - have the front end copied over to the users hard drive - this limit network usage and will cause their local copy of the .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.

Turn on oplocks on your Samba config for the data .MDB files.

Get a real database: Access (with its .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.

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.

keep acces forms, use SQL Server for data

[spongman]

Use the Access Upsizing Wizard and test your front-end on MSDE before forking out for SQL licences.

• HOW TO: Use the Microsoft Access Upsizing Wizard
• Access 2000 and SQL Server 2000 Readiness Update
• Using the Microsoft Access 2000 Upsizing Tools with SQL Server 7.0 or MSDE
• Using Microsoft Access 2002 with MSDE 2000
• Choosing and Using MSDE 2000 as the Database Engine for Your Application
• Microsoft SQL Server: Microsoft Access 2000 Data Engine Options

Now You're talking my cup of soup!! Let's do it!

[boy_afraid]

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,