Sharing Database (Access 97)

I'm new to Access and built a Database recently with multiple tables, reports, and a form. I put this Database on a shared LAN drive and want about 4 people to use it in our department to update information within. However, I'm finding that whenever one of our associates has this database open,,,, it gives the rest of us an error message when we get in and try to get out. We get "Couldn't Save: Currently locked by User 'Admin' on machine 'WK9370'". I've set the database to be record locking only and even REMOVED that part and made it completely open. What am I forgetting about ? <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

Re: Sharing Database (Access 97)

Do all users have read and write privileges in the network folder? If some or all of them only have read rights, Access gets into trouble, because it always wants to create/write to an .ldb file in the same folder as the database.
Has any of the users set the default open mode for databases to Exclusive? (Tools/Options..., Advanced tab)
If users save the design of a database object while others have it open (intentionally or automatically), this can lead to problems when they exit, but it shouldn't cause an error message on opening the database.

Re: Sharing Database (Access 97)

Have you made it as a backend/frontend structure? That means one mdb containing tables on the server. One mdb containing (LINKED TABLES), queries, forms etc. on each users workstation. If you haven't then I strongly recommend you do so for performance alone.
I have also seen odd behaviour when a number of users attempt to use one central database on the server that contained everything. That included the developer modifying forms that other users were working with and then he wondered why it crashed/lost his changes!

Re: Sharing Database (Access 97)

To Hans, I think everyone has Write priviliges to the network folder because we are all updating within this folder the same database (tables). I do notice that there is a .ldb file in the same folder as the database. Is this possibly what is causing our issue ? How would I fix this ? I tried to delete it once and it wouldn't let me.
No, nobody has it set to Exclusive. I've checked this one several times on each PC.
Also, problems only exist when we try to exit. Not when we go in.

Re: Sharing Database (Access 97)

Andy, Thanks. I hear what you are saying. However, I am updating the design often right now and don't have many users on this yet. Therefore, I wanted to stray if possible from creating a system whereas I had to go update each end user PC when I make an update. (And I'm not near good enough yet to create an automatic distribution of updates.)

Re: Sharing Database (Access 97)

Even if you're not ready to distribute the frontend to individual users, you should still split your database into a frontend and backend for reliability (in my experience, if something gets corrupted it's usually the forms or reports, so a split database means that it's much less likely database corruption will cause lost data). It's quite all right to leave the frontend and backend in the same folder.

DON'T muck with the ldb file unless you're sure no one is using the database (in which case the ldb file will normally already have been automatically deleted); it is used by Access to track multi-user locking.

Make sure users have write access to the directory that the database is in (which implies that they can create and delete files in that directory) in addition to write access to the database files, since the ldb file is created and deleted automatically.

By the way, it is dangerous to change forms and reports in an A97 database if anyone else is using the database (even if they aren't using the forms/reports you are changing). I've had major grief with corruption when I've done that. That's another reason for having separate copies of your frontend.

Re: Sharing Database (Access 97)

Doug, Where can I go to learn how to program the front end separate from the backend ? I'm fairly new ot all of this and I don't have a clue on how to achieve what you suggest. Thanks so much for all the advice.

Re: Sharing Database (Access 97)

Split it into FE and BE. Have two copies of the FE with different names. One is functioal, one is development. Then you work on the development one whilst the users can enter data through the functional one. Do NOT modify the user interface while it is in use in the functional FE. It is easier to spend five minutes splitting it than throwing away five hours of your (and your users) work.
A simple but crude update is to have a bat file in the startup folder of the users PC's that deletes the local copy, then copies the master FE from the server to the local HD.

Ther is no difference to coding a FE than a complete DB. All that is different is that the tables are linked instead of local.

Re: Sharing Database (Access 97)

I no longer have A97 so I'm hazy on the exact menu location (however, I've done it many times in both A97 and A2000 - it's just the menu location that is different). I'm pretty sure in A97 the database splitter is found under Tools | Addins. If not, poke around until you find it.

Simply run the database splitter, and it will do the appropriate things for you. Once it's done its thing you'll find that you now have two database files - the one with the original name is the front end (which is the one you should be running). The one with the same name except with a _be suffix is the back end. The back end contains your tables. The front end contains links to the tables in the back end. Unless you've got some very fancy programming (there's some fancy things you can do with tables that you can't do with links to tables) you should see no difference other than things being somewhat slower (them's the breaks - I firmly believe the advantages outweigh the slowdown).

Re: Sharing Database (Access 97)

luavul: One more little item for your consideration is as follows:Thanks to Hans, I now understand that, if you plan to split a DB into FE/BE, you can't have any links to Excel spreadsheets. My DB (before the split thing) worked with a table that was really an Excel spreadsheet. Hans pointed out to me this morning that Access by its nature is designed for the multi-user environment; not so with Excel. That means that, before I can split my DB, I must replace the linked spreadsheet with a for-real table created with data imported--not linked. My DB works as is, but we can't have more than one user at work on any part that involves the linked spreadsheet.