Slashdot Mirror

← Back to Stories (view on slashdot.org)

Sharing MS-Access Databases, Efficiently?

Posted by Cliff on from the doing-the-improbable dept.

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

2 of 98 comments (clear)

  1. Somthing wrong here.. by zulux · · Score: 5, Informative

    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.

    --

    Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.

  2. Re:*ditch* Access, sorta by eakerin · · Score: 5, Informative

    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.