Access 97 DB exclusive access

[J_Orrell]

Hi folks

I've got a DB on a LAN which doesn't need to be p/wd protected or anything, but I want to restrict access to it to only ONE USER at a time. The users are using Access 97 on Win 95B

I've tried going into Tools/Options/Advanced and selecting "Exclusive" as the Default Open Mode, but that doesn't work. I've tried putting /excl on the end of the command-line in the shortcut on the users' desktops, but that doesn't work.

I guess what I want is for Access to not open the database if there is already an LDB file present for it.

I've searched the archives on this forum, but from what I've found, most people seem to be having the opposite problem: i.e.: Access not allowing shared access, while i'm trying to prevent it.

Anyone know how it can be done without the need to start messing around with log-ins and workgroup files, which are not needed with this DB?

 

[The_Doc_Man]

Well, there are a couple of things you could try. The simplest, to my simple mind, is a startup form that has some code running under the OnLoad event.

Inside that code, use the FindFile object to look at a file in the same location as your current database. If that file exists, disallow the user. Give him a message box with OK only and say, "Come back later." Then, in that branch of the code, do a Quit.

If the file does not exist, create it. The name can be arbitrary as long as it is unique and in the same directory as the DB itself. BUT it cannot be the .LDB file because Access creates that for you BEFORE any event gets triggered. So you cannot trust a test for the mere presence of the .LDB file. "Ask not for whom the .LDB tolls - it tolls for thee."

The more advanced version of this would look at the network username of the individual and write that to the file. Then close the file. Trap any errors that might have occurred when you did so. Just to be sure, open the file to read it. If and ONLY if your username is now in the file, you own the database. If not, someone else got in before you did. You DON'T own the database. Go away.

In EITHER approach, the simple rule to remember is: Open what you close, delete what you created. If you fail to delete your interlock file, it will be locked until you manually delete the file.

For the purists among you, this works because the file will be created on the file server using its file locking system. Even though the lock systems of the individual workstations won't see each other, they will BOTH use the same file system (the server's) when they create the interlock file. And for that brief moment, they both DO use the same lock system.

 

[J_Orrell]

Thanks The_Doc_Man. I am already using your first method on another database, but I was hoping for a better solution because the problem I've had is that if people quit Access with the X button, my D-I-Y locking file doesn't get deleted: it only gets deleted through using my "Quit" option. I've never found a way of getting Access to run VB code when someone closes a database or the application.