Catch up on stories from the past week (and beyond) at the Slashdot story archive

 


Forgot your password?
Close
typodupeerror
×
Data Storage Operating Systems Software Windows

Sharing MS-Access Databases, Efficiently? 98

Posted by Cliff 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?"
This discussion has been archived. No new comments can be posted.

Sharing MS-Access Databases, Efficiently? More

Sharing MS-Access Databases, Efficiently?

Comments Filter:

  • *ditch* Access, sorta (Score:5, Informative)

    by TrebleJunkie ( 208060 ) <ezahurakNO@SPAMatlanticbb.net> on Wednesday April 09, 2003 @11:56PM (#5698837) Homepage Journal
    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 (Score:5, Informative)

      by eakerin ( 633954 ) on Thursday April 10, 2003 @12:52AM (#5699068) Homepage
      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.
      • 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.
        One that I know will be an issue is that, out of the box, auto-increment fields won't work. This is fairly easily fixed, or just run MySQL instead.
        • One that I know will be an issue is that, out of the box, auto-increment fields won't work.

          See here [postgresql.org]:

          Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator.
          They're just not special types, but a reasonable use of sequences and default values.
    • you can use this little library [sourceforge.net] to get direct platform-indepentant access for all you access data.
  • I thought Access supported network attach via a DBI. Why don't you have a central, or semi-central DB server or servers with the applications going through DBI to get to the database? Using MySQL that would be a sinch.
  • SQL server. Access was never designed to do what you're doing. I doubt that a SQL server will use much more CPU and it shouldn't be too hard to re-write the access stuff from SQL. Even if it were only two users using it, I would have designed it to be SQL based from the beginning.
    • I doubt that a SQL server will use much more CPU and it shouldn't be too hard to re-write the access stuff from SQL.

      You're shifting the responsibility, though.

      The access solution is just using the 'server' machine as a file server; high bandwidth but low server load. Running SQL server shifts most of the operation to the server machine; lower bandwidth for DB access but high server load.

      You really do need a dedicated machine for SQL server, and one with *lots* of RAM. Obviously you can distribute the da
      • Although you are right in that SQLServer doubtless will consume more resources than Access, the main problem being considered here appears to be the synchronization problems. When everyone was using a single SAMBA share, some would get locked out and when multiple shares were used, manual synchronization had to be performed.

        By going with SQLServer (or another database) and using ODBC for connectivity, you place all of the synchronicity issues into the hands of a database server, which is typically built to
  • iirc, microsoft sql server will support and/or import access databases, so you can start small and grow as needed. And as others have posted, you can still use the access front ends. but in any event, you _need_ a real rdbms.

  • By the time I finish, this will be redundant... (Score:5, Informative)

    by Violet Null ( 452694 ) on Thursday April 10, 2003 @12:06AM (#5698877)
    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".

    • Pedantic correction... (Score:4, Interesting)

      by leonbrooks ( 8043 ) <SentByMSBlast-No ... .brooks.fdns.net> on Thursday April 10, 2003 @02:02AM (#5699377) Homepage
      It's not made for multi-user access

      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... (-:

      • For a couple of years now I have been volunteering at a radio station during their annual subscriberthon. A room of volunteers answers phones and enters subscriber details into a database. The database is Access. The frontend runs off a ColdFusion server. I asked the IT guy why ColdFusion and he said "because we can have multiple users of the Access database, without anyone actually having to run it".

        So if you really want to keep Access, try ColdFusion.. Although it'd cost you far more for a ColdFusion lic

        • because we can have multiple users of the Access database, without anyone actually having to run it

          Either CF is doing the arbitration for MS Excess, or it's doing Excess's own job for it. Either way, very clever of CF, hats off to them...

  • This is the dumbest question ever.

  • use MySQL (Score:2, Funny)

    by alonsoac ( 180192 )
    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
    • OK if you do this for a living I have a question. Access's SQL parser is actually richer than MySQL's, what do you do with SQL statements that MySQL can't handle?
      • Well yes that's a pain sometimes. It depends on the application. I converted one application for a client to MySql very easily because all SQL was simple. There is another one I made that I have wanted to convert from Oracle but some queries are very complicated (Oracle I think is even richer than Access) so I haven't had the time to do it yet.

  • Somthing wrong here.. (Score:5, Informative)

    by zulux ( 112259 ) on Thursday April 10, 2003 @12:19AM (#5698919) Homepage Journal
    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.

    • This guy is right on! I have an Access database for 16 users on a Samba share. It ran like crap until I: 1. Split the forms/reports from the data 2. Copy the forms/reports MDB to their local computer every login. If you skip 2, you may find that your forms/reports MDB gets very large and very slow. It is also convenient for distributing changes. As it is, I run into 1 corrupted record every 3 months or so, which is too often for my tastes. I am currently remaking it with mySQL and PHP so I can get rid of
    • I used to work for a company that, at one point, had ~70 employess that nearly all needed access to one or more of a dozen .mdb files on an NT4 fileserver. No problem whatsoever to get all of them to work together. The one issue that I had yet to see or hear about its' resolution, was a safe way to keep the idiot user from trashing the db when (s)he quits out imporperly. Seriously, the parent here is most correct. This was at an all WinBlows shop, except my terminal ;D But nonetheless you should not be

  • Sweet Jesus (Score:3)

    by recursiv ( 324497 ) on Thursday April 10, 2003 @12:19AM (#5698920) Homepage Journal
    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.
  • 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
  • We've developed a medium sized application in Access with a SQL backend. All you have to do is set up ODBC datasources and have a SQL Server backend by linking the tables to it.

    It's a piece of cake, and you can have the Access database on the client as no data is stored in them. Plus you can also consolodate all that data into one big database which would be usefull for backup and maintenance.

    Also Access databases have a tendency to corrupt, so never store data in them.

    • Re:SQL Server (Score:3, Insightful)

      by delus10n0 ( 524126 )
      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 gene

      • Re:SQL Server (Score:2, Insightful)

        by Baikala ( 564096 )
        ..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.
      • Awesome! I have no idea why someone modded me "Overrated", but thanks man. I appreciate it. My post certainly was that!
  • If you're a bank, you need to be running a DB that supports transactions. (PostgreSQL/Oracle come to mind). Anything else is taking unnecessary risks with your customers' data. Oracle, while very large, is an incredibly powerful, expensive, and frankly, worthwhile proposition. There are plenty of tools out there to convert your access databases (I wonder if they're even normalized?) to Oracle, and you can use J2EE or Forms, or your dev language of choice to make frontends for all your now happy users.

    (Does

    • Re:Oracle (Score:5, Insightful)

      by greenhide ( 597777 ) <`moc.ylkeewellivc' `ta' `todhsalsnadroj'> on Thursday April 10, 2003 @10:18AM (#5701353)
      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.

      • Screw arounf with the PostgreSQL buffers in your config file - PostgreSQL won't allocate enough memory to do decent caching, so out of the box, it's performance sucks.

        Get those buffers set right, and PostgreSQL will come very close to Access' speed.

  • Making Access databases multi-user (Score:3, Informative)

    by nebbian ( 564148 ) on Thursday April 10, 2003 @01:47AM (#5699314) Homepage Journal
    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 [microsoft.com] 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).
    • 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.
      Mod this guy up -- he recalled what I did not. And it is MOST accurate. Read my above post for more info.
    • 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 [microsoft.com] really shits me.

      Pardon me for being so insensitive, but you sir are an idiot.

      Access is more stable that SQL Server? Access is a desktop database, and maybe you'v
      • Pardon me for being so insensitive, but you sir are an idiot.

        That's pretty funny coming from someone who is so lonely the only way he can get a woman is to program his own [eisenschmidt.org].

        Geez man, lighten up!
        • That's pretty funny coming from someone who is so lonely the only way he can get a woman is to program his own [eisenschmidt.org].

          That doesn't make me an idiot, that makes me a genius. It frees me up from things like dinner and hanging out with her friends to troll message boards and bitch about other people.

    • 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 agree. I have always liked how Access stores all its data in a single file.

      Interbase [sourceforge.net] works in a similar way. All the data is stored in a single .gdb file which I can build on a windows machine and upload to a linux server.

      Of course Interbase, unlike Access, is just a database server and does not have any front end tools. However there are apis fo

  • A few more suggestions... (Score:3, Informative)

    by maunleon ( 172815 ) on Thursday April 10, 2003 @01:52AM (#5699337)
    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.

    • One other approach I forgot to mention, and it's about the same idea as the middle tier. Create a web service, have the web service query the database, and have your clients query through the web service.

  • 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 [microsoft.com] - which is really a limited version of the SQL Server engine. I think the only major limits are t
    • MSE is optimised for up to 5 concurrent connections, after that it starts slowing down *a lot*.

      Having said that, if you manage your connections properly in the front end you shouldn't have too many problems and it /is/ free...
    • Word of caution:

      If you use MSDE, please be sure to set an "sa" password. And don't forget to install SQL Server patches....

      It has been my experience (with thousands of customers using MSDE) that it is generally maintained poorly - when there is no UI, people tend to forget about it. I'd guess that the propagation of the recent SQL Server worms were due, in good part, to MSDE.

      Other than that, it's a good way to go.
  • Anyone have any other ideas on how you would have gone about sharing these MS-Access databases?

    The easy way to share access databases (if you really HAVE to use access - I'd strongly recommend a proper database server such as MySQL or if you can afford the licenses, MSSQL) is to share the database via ODBC. Bear in mind that when sharing Access in this fashion, the MS JET DB engine has a nasty habit of corrupting the database once you hit so many simultaneous users.

  • Only one reply (Score:3, Informative)

    by Eivind ( 15695 ) <eivindorama@gmail.com> on Thursday April 10, 2003 @03:31AM (#5699604) Homepage
    There's only one sensible reply to this question:Don't do that!.

    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 wi

  • If you use your access-db in a network you can also get a corrupted db. Although this can be solved by simply opening the db on the local computer => access-db is not designed for network use.
  • 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 acc
  • I haven't seen it mentioned here yet but it could be issues with samba. I don't think samba supports the granularity of locks on a network file that NT does. In a file based DB being able to lock just portions of the file for updates is crucial. I've seen places where people did some pretty sick things with Access so I know its possible to have 50 people use an MDB at the same time. If all 50 are doing updates though Access will pretty quickly shit itself and corrupt the db. It really isn't meant for m

  • Which bank? (Score:5, Funny)

    by seanmeister ( 156224 ) on Thursday April 10, 2003 @08:26AM (#5700385)
    Seriously, I want to know. Because if it's MY bank, our relationship is over.

    I mean, Access - come ON!
    • I think you'd be in for a serious shock if you ever got to see whats used at most banks. You remember all those bank mergers over the last 2 decades? You think all the IT infastructure got merged? No they did the typical corporate thing, grab the boxes and fire everybody from the old company who understands what's on them. Pass serious rules against making any changes and pray.

      Don't be suprised if a critical system is running on foxpro or paradox much less access.
    • Seriously, I know that things are most certainly less rosy than this at most banks around, but my friend, you took the words right out of my mouth.

      For the love of God, Access????

      Access?

      Bah. I'm going to start hiding my money in a mason jar again.
  • Access is never the correct solution in the enterprise. Put the data in sql and design a web front end to access, update, and delet data...
  • Have you tried debugging the problem from the Samba side? There are a number of helpful configuration documents and mailing lists.

    In one of the installations of Samba, as a replacement to Novell, I ran into a file locking/corruption problem. The problematic application was an ancient DOS scheduling program with multiple users. The solution turned out to be:

    [DOS_SHARE_NAME]
    oplocks = no

    There are a couple of other options for file locking but that was the one that fixed our problem.

    Other locking options:

    s

  • Access as an ... (Score:4, Informative)

    by JSCarr ( 312656 ) on Thursday April 10, 2003 @09:31AM (#5700888) Homepage
    ...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 [amazon.com]

    2) Post your question over here: comp.databases.ms-access [google.com]. 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.
  • Put the tables on a real server, be it free ( postgres, mysql, what ever ) or MSDE ( you get a distributable runtime if you own VB.. )

    You can still use Access for your GUI and queries and reports if you cant move them to something else.

    But what ever you use for the interface don't distribute raw MDB files for the data.. you are asking for problems. I don't care what Microsoft says, 'jet' is just a bad idea all around. Plus you get better performance as a side effect.
  • FYI,

    Access will attempt to optimize your queries and literally mangles them. Do so googling searches to find the registry key to turn this query optimization off by default.

    I remember reading an article about a company that used an Access front end to a PostgreSQL backend and they had to turn on debugging on the PostgreSQL server to see what the submitted queries were. Access was optimizing their already optimized queries and literally making them not optimized in the process. ;-)

    This sort of frontend
  • We make a product that puts a TCP/IP wrapper around Microsoft Access databases. This wrapper means you can seamlessly query these databases remotely from either Windows or Linux without having to worry about sharing violations.

    Our product name is UniverSQL [sidespace.com] and I would be more than happy to send you a demo if you email me. We provide full technical support and GUI admin and query tools that run on both Windows and Linux.

    Regards,

    Tyler

  • RUN (Score:3, Funny)

    by sohp ( 22984 ) <.moc.oi. .ta. .notwens.> on Thursday April 10, 2003 @11:24AM (#5702027) Homepage
    Please tell me what bank you are working at so I can be sure I never put my money in an account there.

  • I'm tempted to call you an idiot, but I'll refrain (Score:3, Insightful)

    by delus10n0 ( 524126 ) on Thursday April 10, 2003 @12:01PM (#5702364)
    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...

  • You're Doing It Wrong! (Score:3, Interesting)

    by yancey ( 136972 ) on Thursday April 10, 2003 @02:12PM (#5703686)

    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.

  • Damn. I read this and thought ... I have just the solution - use a real SQL database back end and use your Access forms to connect to that database via ODBC ... surely NOBODY would have thought of that.

    I'll be a hero!

    Me and every other mouth breathing, knuckle dragging, SQL using and ODBC connecting code monkey on /. I think even the First Post guys said 'umm FP - and use SQL via ODBC'

  • I've used Access over shares as a front end, and as a back end. And as others have said in their various ways, replace Either the front end or the back end. DON'T use Access for both. Whipping out a custom VB or web frontend to an existing database using standard automated tools is practically seamless, and with "ONLY" 50 users you can get away with leaving Access as the backside. You could also go with switching the actual data to any of the previously recommended Backends(MySQL, MSSQL,Oracle, etc) and
  • The company I currently work for has an in-house CRM system, the backend is currently an Access 97 DB, and the frontend is a custom written VB6 application. We have probably around 25-35 users on it at any one time.

    For a long time, we were having corruption issues, and didn't know what the reason was - it got so bad, that I ended up writing a patch to custom sync records on tables with a backup - that is, if a user changed a record, it would be saved prior to the change, then saved after - if any corruption

  • Webify? (Score:2, Informative)

    by acaird ( 530225 )

    You could try to webify it. Lots of things talk to Access over the web; even from a Unix web server. Look at ODCBSocketServer [sourceforge.net]+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.
  • 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 [msofficemag.net]. 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,

    • In new Access database projects I still prefer to use Data Access Objects (DAO) even though it is an 'older' technology.

      Here is why I generally dislike Active Data Objects (ADO):

      1. ODBC(DAO) is well understood technology in the free software community. The internals of OLEDB(ADO) are inadequately documented in comparison.
      2. DAO(ODBC) is more portable; making it easier to swap out the back end database. With ADO you must always use the database vendor's proprietary SQL dialect.
      3. ADO views on joined ta

  • Porting Rules from Access to MySQL (Score:3, Informative)

    by jpkazarian ( 549670 ) on Friday April 11, 2003 @05:16PM (#5713379) Homepage
    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
  • First, I agree to move to something else, but I won't say my preference here. Some wank will find a way to blame my choice on my family tree, and sidetrack the discussion into the gutter.

    That said, if you insist on using MDB's, hook to them with a front end...check out Cold Fusion, formerly from Allaire, now in Macromedia's [macromedia.com] hands.

    You shouldn't allow anyone but your DB Admin to tap directly into your Access files. Building an interactive, browser based front-end is not that hard, and it provides many o
    • I agree on webifying this with ColdFusion (I'm an expert with 8 years of CF experience under my belt), but it look like this guy is trying to get performance working better with the least amount of impact/work. On a previous post I have step by step directions to upgrade the data backend to SQL Server and then convert the front end to an ADP file and hooking it into SQL Server. CF would be excellent, but think of the time needed to re-create every form and business rule.
      • Makes good sense. Assuming he can move to SQL easily. I know I did. I wonder what kind of table structure his 50 db's represent.

        Like I said, I have my own preferences these days...none of which currently include MDB's. I think I only have one legacy Access base still working, and it's 3 hears old.

        Not knowing his skills, budget, resources and goals in detail, it's hard to recommend beyond examples, etc.
  • it can be done. The key is to make sure your applications are opening the databases and recordsets in the proper share/locking mode, minimize the amount of time lock is in place, minimize the amount of time recordsets are open, and handle the runtime errors correctly. Multiple users should be able to have read access to same database simultaneous, and still be able to update the database but at the same time. First make sure all your network shares are setup properly for this, and you probably have a big
  • There's an article that was linked from slashdot describing how to convert access to sql here: http://developers.slashdot.org/developers/03/04/14 /1059254.shtml?tid=185&tid=156

Slashdot Top Deals

He has not acquired a fortune; the fortune has acquired him. -- Bion

Close