Check for other users before compacting

[MatthewR]

I've programmed my database to occasionally prompt the user and offer to compact the database.



But, if there are multiple users, it won't compact. It generates an error message- "“You attempted to open a database that is already opened by user ‘xxxxx’ on machine 'xxxxx’. Try again when the database is available."

How can I check for other users, so that I can avoid prompting the user about compacting if there are multiple users at once?

Alternatively, if there is some way to catch and handle the error message, that would be an okay solution too.

Or alternatively, if there is some way to boot off other users before compacting, that would be okay too. There won't be more than 1-2 other users, and it'll only happen once in a while.

Please let me know if any of these approaches are possible.

FYI, I would prefer to avoid splitting the database. Thanks!!

Matt

[CJ_London]

not splitting the database is a really really bad idea - that way lies corruption and many wasted hours. Rule is backend - tables only and located in a mutually accessible folder, front end - everything else and located on each users own machine. The fact you have the db as you do is probably why you need to compact.

However if you do split the database, you still will not be able to compact the backend if there are other users still using it.

you can use standard error handling code to catch the error and take the appropriate action - something like

....
....
on error goto errHandler
compact db code
exit sub

errHandler:
debug.print err.number; err.description
select case err.number
    case ??? 'err code for compact fail
        msgbox "other users still active, please try later
        resume next
   case else
        msgbox "Error " & err.number & " - " & err.description
 end select

[MatthewR]

Thanks for the reply. The thing is, my code (which is in the main form's unload event & excerpted below) doesn't compact the database per se- it just sets the Auto Compact option to True.

So, the error does not happen until the database subsequently closes and tries to compact. So, it's not a run-time error that I can handle in the event's error handler. Is there a way to handle that type of error? Or alternatively, is there a way to directly compact the database with vba, so that I can catch the error in the event vba?

Also, regarding splitting the database- there's only 2 or 3 people in total who will use this database, and they will almost never end up editing the same record at the same time. Does that make it reasonable to avoid splitting the database, or is it still a horrible idea?

'Capture current compact counter:
CCounter = DLookup("CompactCounter", "tblAdmin", "ID=1")

    'Prompt user when CompactCounter reaches specific value:
    If CCounter >= 15 Then
        CResponse = MsgBox("It's been a while since you've compacted the database." & Chr(10) & Chr(10) & _
            "Compacting can improve the database's performance." & Chr(10) & Chr(10) & _
            "Would you like to compact before closing?", vbYesNoCancel, "Compact Database?")


        If CResponse = vbYes Then
            Application.SetOption "Auto compact", True 'Makes the database automatically compact on close.
            NewCCounter = 1 'Reset the compact counter to 1
        ElseIf CResponse = vbNo Then
            Application.SetOption "Auto compact", False 'Do not compact on close
            NewCCounter = 10 'Set the compact counter back to 10
        ElseIf CResponse = vbCancel Then
            Cancel = True 'Cancel close
        End If
    Else 'If no compact prompt, add 1 to compact counter:
        NewCCounter = CCounter + 1
    End If


     'Set CompactCounter to new value:
    DoCmd.SetWarnings False
    Sql1 = "UPDATE [tblAdmin]" _
    & " SET CompactCounter=" & NewCCounter _
    & " WHERE ID=1;"
    DoCmd.RunSQL Sql1
    DoCmd.SetWarnings True

[Micron]

The potential for corruption goes up with the number of concurrent users. How much is anybody's guess as there are a lot of variables in play. The basic answer is, how much risk are you willing to take - some, or none, and what would the impact be if you corrupted it so bad that it could not be recovered? Only you can decide that.
I'd take a different approach to compacting that considers db bloat and not some arbitrary number of logins. To do that, you'd need to check the file size upon user exit, and compact at that time if it exceeds a value you determine. If this is done on a split db where each user has their own FE, you do not have to worry about anyone else being logged in. If it's split but users share the same FE, then you need to log in users in a table and check the record count before attempting to compact. The latter method of splitting is not widely suggested or dare I say, practiced, among developers. I confess to using it to eliminate distribution issues across a wide network of users.

[MatthewR]

Okay, I'll look into spliting the database. But why is it that developers don't allow users to share the same FE in a split database?

[Micron]

It has advantages such as not having to worry about concurrent users for things like compaction and reduces the potential for corruption of tables, forms and reports. Very important (if using a shared FE) is designing the process to allow multiple users to do the same tasks at the same time. For example, if you load a temporary table with records that the user will edit, then want to write the changes to the main table, you need to restrict everything that will happen in this process to one particular user. That is, if the edits, writing to the main table AND the deletion of those records from the temp are not restricted to the particular user, guess what happens when you flush that temp table? Everyone's work suddenly disappears. This applies to every process. I created 3 applications that work this way because I did not want to go down the path of having to distribute 24 FEs for each. Plus, my knowledge of how this works was based on observations of others who wrote batch files to over-write versions when a new one had to be distributed. These always failed when people left their copy open after going home. I can say that these apps have been running for years without any issues and without any kind of corruption, but knowing what I know now, I would definitely consider doing it the other way. I would have a version number in a BE table which I would update before each new FE release, which has either a local table with this value, or as a custom db property. The FE would check its version number against the BE value and if not current, advise the user they had to get a new FE and then close the db. I haven't given the subject enough thought to decide on how I'd provide the update.

There are probably many other good reasons that contributors would like to add for having an FE for each user.

EDIT: even if I was going to be the only user of a db, I would split it. If design changes corrupt the FE, I would not lose all the data, plus it's better if backups of the fe don't contain data, and in case of emergency, you want to fall back to a design that was working. Otherwise you're looking at importing all the data into a new db - assuming you can retrieve it in the case of corruption.

[MatthewR]

Okay, thanks for the detailed answer. That is all very helpful. Sounds like the best way to go is probably multiple FE's.

I have one more question, if I may- when I compact a split database, should I compact the front-end, the back-end, or both (or neither)?

[CJ_London]

back end for definite - front end only if you have temporary tables - it will only contain, queries, forms and code so should not change in size.

[Micron]

Assuming you're not using any local tables in the fe, you should not notice a lot of bloat so I would simply keep an eye on fe file sizes for a while. You may elect to compact them on a frequency basis (such as once per month, or per 3 months or maybe not at all). This does not apply to your main fe template that you make all design changes in before re-releasing to the users. This one would undergo a compact/repair before release as it will bloat a bit during design changes. I'd make all fe's into compiled front ends (accde or mde as the case may be) to protect code and limit monkeying around with db objects (as much as that provides). Some people refer to these as 'executables' but they're not really.

As for the be, I'd recommend a method other than using the fe to compact the be. Typically, it will fail due to record locking, failure to have exclusive use if you try to use the fe connected to it, or even if the user does not have necessary network permissions for the location where the automatic backup will occur. Using a manual process, you might want to use a small db that has one form (or similar method) run the compact on your be. Alternatively, you could automate the whole process, but I personally would rather be in attendance in case something comes up. Here's a link on how to use DAO to compact remotely: https://msdn.microsoft.com/en-us/lib.../ff844821.aspx

[MatthewR]

Okay, got it. I'll compact the back end, and I'll look into a manual process. And I'll also compact the FE template after design changes.

But if I compact the BE, will it trigger an error if another user has a FE open?

[Micron]

Likely yes. Can't remember what the error number is, so for a manual operation, I'd do one of the following
- trap the error and abort the compact if it occurs (simplest)
- have everyone log in and record active users in a table (not completely reliable). You can get a count of logged in users but be warned - incorrect shut downs (such as a power failure or shutting down via Task Manager) can leave their logged in flag set to true when they are not in. It would not be corrected until you actually unchecked the field or they logged in and logged out correctly. The idea being that if the logged in count >1 then don't try to compact.
- use an off hours process if people are not there 24/7. I know I recommended manual, but if you have the desire to automate, it is possible. It's just a bit advanced from a programming perspective. Have Windows Task Scheduler access a shortcut to a small db that runs a batch file (*bat) to make a copy of the back end, overwriting the existing backup. Then this utility db compacts the back end remotely. I think the syntax is dbEngine.CompactDatabase "PathToSource" "PathToDestination"

[MatthewR]

Okay, thanks. I'll probably just trap the error. That seems like the simplest solution.