Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    Louisville, Kentucky, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!

  4. #4
    New Lounger
    Join Date
    Dec 2002
    Location
    Louisville, Kentucky, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  5. #5
    New Lounger
    Join Date
    Dec 2002
    Location
    Louisville, Kentucky, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Sharing Database (Access 97)

    As long as you don't split it into a FE/BE (and have the FE on each users PC) you will continue to have these problems.

    When you make a change just email them the change or setup the latest FE on the network where thay can copy it to their PC.
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  8. #8
    New Lounger
    Join Date
    Dec 2002
    Location
    Louisville, Kentucky, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sharing Database (Access 97)

    I agree with all the recommendations to split your database - may I suggest you take a look at this tutorial.
    Wendell

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sharing Database (Access 97)

    They also need delete privileges for that folder so that the ldb file can be deleted when the last person exits.
    Charlotte

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sharing Database (Access 97)

    Your memory is still OK - it's Tools/Add-Ins/Database Splitter in Access 97.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •