VB6 - Jet UserRoster, Connection Control, Backup

Background

When Jet 4.0 came out it offered many new features that go beyond those in Jet 3.x and the Jet 4.0 OLEDB Provider exposes many of them through ADO and a few more through JRO.

Some of these can be useful in getting a Jet database closed so you can back it up, as well as another way to do the backup that runs a compact and repair operation to reclaim space and fix up indexes.


User Roster

It is possible for programs to retrieve a list of active connections open on a database. Since this even shows which computer each connection comes from on a shared database it can be useful in getting persistent opens closed.


Connection Control

A program can invoke Connection Control to prevent new connections to a database from being established. The idea is to get the database closed for backup or other maintenance through attrition, without interfering with existing activity.

Invoke this and put the word out. As connections are closed eventually you can perform the database maintenence once the last man standing... sits.


CompactDatabase

A method of the JRO.JetEngine object you can call to compact an existing database into a new database. Useful as a backup step because it reclaims unused "holes" in the MDB file created by lots of updates and deletes, compacts indexes, and repairs some kinds of internal broken links.


Backup Demo

This demo has two programs:


DbDemo

A small program that opens a database (creating it if necessary) and does some simple queries and updates. It can be run passing a database folder on the command line if you want to test access over the network to a database on a file share.


BackupDemo

A small program that opens the database (creating it if necessary) and sets Jet Connection Control to prevent new connections. Then it polls the database for a list of current users/connections and displays them. Once the connection count is one (just this program) it closes the current database, copies it as a backup, and uses JRO to compact the backup to create a new "current" database.


Running The Demo

Open each Project and compile (Make) it.

Run DbDemo and create a few records.

Attachment 103937

Run a second copy of DbDemo.

Run BackupDemo. Observe the users list.

Attachment 103939

In the second screenshot there are three connections listed. One of these is an instance of DbDemo running on another computer (ATOMANT in this case). Two more connections show from the first computer (SNITZ), one being a local instance of DbDemo and the other being BackupDemo itself.

All of them show as the user "Admin" since this isn't a secured Jet database. Jet User/Group security is beyond the scope of this writeup.

Close a copy of DbDemo and the list shown in BackupDemo should change to reflect this.

Close the other copy of DbDemo.

Attachment 103941

Try starting a new DbDemo copy. It should fail with an error message describing the database's state, which currently blocks attempts to open or lock it.

Attachment 103943

When the last DbDemo is gone, BackupDemo should enable its "Back Up MDB" button. Click on this to start the compact & backup process.

Attachment 103945

At this point BackupDemo is done. Users can start running DbDemo again, using the compacted database. The original database has been renamed and can be copied off to archival storage as desired.


Remarks

This is a demonstration providing sample code. For a real application you might do things differently, such as creating backup MDB names with timestamps in them. You might create a backup utility with no user interface that can be scheduled to run via Task Scheduler and just "takes off" and does the backup once all other users have closed out.

You might even add some mechanism to send messages from your backup utility to any running copies of the database client application. These could warn the users to finish up and close out for a while, and let them know when the backup is complete.

Don't try to back up directly to a slow network share. It is also best to run your backup utility directly on the box hosting the database. You can't always do this, but it makes the process much quicker and more reliable when possible.

Here is the archive containing the two Projects.