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

[Remster]

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

[HiTechCoach]

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.

[Remster]

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

[HiTechCoach]

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

[Remster]

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.

[HiTechCoach]

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)

[HiTechCoach]

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.

[HiTechCoach]

Are all users using Access 2003 with SP3?

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

[Remster]

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.