Results 1 to 9 of 9

Detecting whether someone else has database open/testing for incorrect exit

  1. #1
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    249

    Detecting whether someone else has database open/testing for incorrect exit

    Folks

    Is there a way, when opening a database, to test through VBA whether someone else already has it open? I've found a way to search for an ldb file, but it always finds one because I have the database open by then.

    The context is this. I want my database to compact and repair whenever it's opened for the first time after having been closed incorrectly. I can achieve this using the method outlined in this post, but the problem is that it also tries to compact and repair whenever it's opened and someone else already has it open. How can I distinguish between these two circumstances?

    Thanks

    Remster



  2. #2
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    1) You really must split the database to hekp protect the data and also be set up properly form multiple users.

    See: Splitting your Access database into application and data

    2) Also back up a database before you compact it. Especially after it was not closed properly. When he compact fails (and it does) your database is lost.


    3) Check out: Application Starter
    Automatically checks for and downloads an updated version of the application front-end from network server prior to opening it on the end-user's computer.

  3. #3
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    249
    HiTechCoach

    Many thanks for your advice. I've used Tools -> Database Utilities -> Database Splitter, and now I have two files: Database and Database_be. However, everything is now painfully slow (don't worry, I'm experimenting with a copy). I take the point you make on your webpage about splitting the database for even just one user, but are you sure it's worth it given the change in performance (there are only three of us using it), considering that we're making regular backups anyway?

    Remster

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    Quote Originally Posted by Remster View Post
    HiTechCoach

    Many thanks for your advice. I've used Tools -> Database Utilities -> Database Splitter, and now I have two files: Database and Database_be. However, everything is now painfully slow (don't worry, I'm experimenting with a copy). I take the point you make on your webpage about splitting the database for even just one user, but are you sure it's worth it given the change in performance (there are only three of us using it), considering that we're making regular backups anyway?

    Remster
    Remster,

    If you have two or more user than you really want to split the database. I know of no safe way to avoid it.

    most of my multiple user Access applications have 5+ user with no performance issues.

    There are thing you can change to get the performance back.

    I always deploy the front end as a compiled database (.MDE or .ACCDE). This also helps with performance.

    See: Microsoft Access Performance FAQ

  5. #5
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    249
    Thanks. I've tried compiling, but it's still as slow as hell. I appreciate the corruption issues you've raised (none of the other disadvantages of not splitting seem to apply) and I'll try other things when I have the time. In the meantime, I can't think what to do other than back up frequently.

  6. #6
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    Have you done the following?

    Front end:
    - LDB locking which a persistent recordset connection fixes
    - Track name AutoCorrect should be off. (Access 2000 and newer)

    Back end:
    - Subdatasheet Name property set to [Auto] should be [None]. (Access 2000 and newer. See my note in the section below.)
    - Track name AutoCorrect should be off. (Access 2000 and newer)

  7. #7
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    Quote Originally Posted by Remster View Post
    Thanks. I've tried compiling, but it's still as slow as hell.
    In general slow performance is from poor network performance, poor software design, or both. With Access it generally has a lot to do with the design of the database.

    Can you be a lot more specific on what is slow?

    An example would be great.

  8. #8
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    699
    Are all users using Access 2003 with SP3?

    Also are you placing the front end on the local hard drive?

  9. #9
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    249
    Thanks for your continuing advice. I'll try your suggestions when I have time and hope that I don't come to regret the delay.

    Like many people who use Access, I expect, I have a job that has nothing to do with Access but have ended up creating a database because of the positive things it has to offer. When I started, I thought it would be as simple as creating an Excel workbook and had no idea what I was letting myself in for! Our IT department doesn't support Access and I have very little time to do things as a database professional would do them (which is the only reason why I might end up not following your advice). Fortunately, I'm not working with data that will change all that often or where there will be legal implications if I screw it up.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date And Time Of Member's Entry And Exit.
    By bhavik1978 in forum Access
    Replies: 4
    Last Post: 09-20-2010, 06:40 AM
  2. Replies: 3
    Last Post: 08-08-2010, 05:33 PM
  3. detecting a null date
    By tedpottel in forum Queries
    Replies: 3
    Last Post: 03-02-2010, 01:45 PM
  4. Testing links
    By piflechien73 in forum Forms
    Replies: 0
    Last Post: 06-01-2009, 05:09 PM
  5. Replies: 0
    Last Post: 05-17-2008, 01:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums