The PrivDBEngine Object

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Aug-1998 >

The PrivDBEngine Object

Julianne Lee and Michael Kaplan            
 
There's an undocumented object in DAO that lets you get the benefits of another copy of Access with none of the costs -- your own, private DBEngine.
 
Handling users who open a database exclusively can be a problem. To do it effectively, you'll have to use the undocumented PrivDBEngine object. However, the first step in handling this problem is to find out whether a user really has opened the database exclusively. This is a typical function you might think of using to determine whether a database is opened exclusively:
 

Function FopenExclusive1(stDbName As String) _

  As Boolean

On Error Resume Next

    Dim db As Database

    

    Set db = DBEngine.OpenDatabase(stDbName)

    

    ' If the database failed with error 3045,

    ' it was opened exclusively by someone else.

    FopenExclusive1 = (Err.Number = 3045)

    

    ' Close up the db in case we opened it.

    If Not (db Is Nothing) Then 

       db.Close

    End If

End Function

 
 
This function tries to open the database. If it fails and you get error number 3045, it means that another user has the database opened exclusively.
 
The problem with using this function is that Jet will let you open a database exclusively as often as you wish within a single session. You can't tell whether you have the database opened exclusively, because Access will always allow you to open the database. In other words, this function will only work when detecting whether someone else has opened the database exclusively.
 
Therefore, what you need is another way to create a new Jet/database session. The only documented way to do that is to open another session of Access via OLE Automation. Needless to say, this isn't a good idea from a performance standpoint.
 
There's a better way of dealing with this problem: You can use the undocumented PrivDBEngine object.
 
The PrivDBEngine object
The PrivDBEngine is a hidden member of the DAO library that's similar to a DBEngine. The major difference is that the PrivDBEngine creates a new instance of the DBEngine that doesn't use the same "session" as the regular DBEngine object does. The objects that PrivDBEngine creates behave exactly the same as the regular DBEngine object.
 
You can define a "private" DBEngine object with the following code:
 

Dim dbe as DBEngine

Set dbe = New PrivDBEngine

 
 
Using this new private DBEngine, you can improve upon the FopenExclusive1 function so that it will be able to tell whether the current database is opened exclusively by you. Here's the improved version, called FopenExclusive2:
 

Function FopenExclusive2(stDbName As String) _

  As Boolean

On Error Resume Next

    Dim dbe As New DBEngine

    Dim db As Database

    

    Set dbe = New PrivDBEngine

    Set db = dbe.OpenDatabase(stDbName)

    

    ' If the database failed with error 3045,

    ' it was opened exclusively by someone else

    ' or possibly by ourselves.

    FopenExclusive2 = (Err.Number = 3045)

    

    ' Close up the db in case we opened it.

    If Not (db Is Nothing) Then 

       db.Close

    End if

    Set dbe = Nothing

End Function

 
 
This function works the same as FopenExclusive1, except that it will return a true value regardless of who's opened the database exclusively. At this point, you can use code to warn the user that he or she has opened the database exclusively and is locking out other users. The database will then be closed:
 

If FopenExclusive2(CurrentDB) Then

  MsgBox "You are trying to open this " & _

   "database exclusively and are not allowed access."

  Application.closeCurrentDatabase

End if

 
 
See the sidebar, "Opening the Database," for more information on controlling how the database is opened.
 
More goodies
There are other features you can get from using the PrivDBEngine. Here are just a few.
 
Using more than one workgroup file
You belong to a workgroup that has access to one database. You need to look at data in another database that isn't in the same workgroup, and none of the users in the first workgroup have permissions to the objects that are secured by the first workgroup. So you're out of luck -- after all, Jet won't let you go between workgroups, right?
 
You could, in theory, open another session of Access via OLE Automation and get the data you need by going between the two sessions. You could also try to find a user who has access to both workgroups to work around the problem. Both of these solutions are options, but they're basically irritating to work with. There's one other option, though . . . you can open a New PrivDBEngine!
 
By using PrivDBEngine, you can open the other database and get data directly from it. The major difference with using the PrivDBEngine and the preceding workarounds is that the PrivDBEngine gives you a new instance of the Jet engine without starting a new instance of Access. This new instance of the PrivDBEngine can have a different System.mdw file than the DBEngine that you're currently using. Users of VB will be very familiar with the syntax used in setting up the new DBEngine that this creates:
 

    Dim dbe As New DBEngine

    Dim db As Database

    

    Set dbe = New PrivDBEngine

    DBE.SystemDB = "C:\Fruit\bananas.mdw"

    Set db = dbe.OpenDatabase(stDbName)

 
 
This database engine is equivalent to any other engine you would have run opening another session of Access. The only difference is that you didn't have to open another session of Access to get to it, so performance is significantly better.
 
The "Access won't close" bug
Many people have seen this bug before: You try to close Access, but while the database is closed, Access only minimizes its window. There's been a lot of speculation about what causes this problem, some true and some false. Here's the official story on this bug from Microsoft.
 
When Access is ready to shut down, it makes sure (like any OLE Automation server) that it has no more open references to objects in memory. This count of open objects is split into two parts: DAO objects (like databases and recordsets) and non-DAO (that is, Access) objects. This count doesn't include objects that your code references; instead, it includes only the objects to which Access itself is holding pointers for one purpose or another. When you shut down, all objects in the database are closed, and right before Access closes itself, it checks this count. If the count is greater than 0, then Access assumes there's an external client using one of its objects, and it only "hides" itself. The only way Microsoft Access can hide itself is to minimize its window. We should be grateful that Access doesn't do the equivalent of setting the Access.Application object's Visible property to True -- otherwise, you'd have no way of telling that something had gone wrong.
 
This sort of issue isn't only an Access problem. Many people who use Microsoft Word and Excel programmatically through Automation will recognize that Excel and Word often do the same thing, except they actually hide. Users don't notice this until they see dozens of open but hidden Excel sessions in the Task Manager.
 
There are many causes for these pointers not being freed, but by far the most common ones in Access are caused by DAO objects. Due to the complex interactions of VBA, Access, Jet, and DAO, there are times that DAO tries to free an object but Jet can't yet let it go (for example, if it needs it for a transaction, or for a RecordsetClone, or the like). What happens in these cases is that the object is "lost," and no one ever frees it. This wouldn't be the end of the world (since OLE tracks it and eventually will free it on the close of the host app), but since Access won't close while this count of objects isn't 0, Access stays open.
 
In many cases, the order in which you close down objects -- whether or not you set the object variables to Nothing when you're done with them, and whether you explicitly or implicitly close objects -- can influence this bug and sometimes eliminate it. However, one of the best ways to eliminate it is to use your own PrivDBEngine object instead of using the built-in Access DBEngine.
 
Why is this the case? Well, whenever you use the DBEngine object, Access actually uses the object Access.Application.DBEngine. As a result, Access will track all the things you open and close (and, perhaps, get it wrong). The PrivDBEngine object, however, isn't tracked by Access when it's counting the open objects. Therefore, when you shut down, Access itself will shut down completely. It's still always a good idea to explicitly close the objects you open and set them to Nothing, but using the PrivDBEngine will often work around the problem quite nicely.
 
Taking control
When you're using DAO, there are often things you want to do that the standard DBEngine won't support in Access. In Access, the DBEngine is initialized when you first start Access; by the time your code runs, a lot of the important decisions are already made. So whether you're trying to get to database files secured by other workgroups, determine whether a database is opened exclusively, or work around the "Access won't close" bug, you can use the PrivDBEngine to create a special private DBEngine of your very own.
 
 
 
 
Sidebar: Opening the Database

Here are some additional notes about the way a database opens. There are three means of setting the way the database opens: the FileOpen dialog box in Access, the Options menu, and the Registry.
 
FileOpen dialog box
The FileOpen dialog box in Access is probably the easiest way to set how the database is opened. When this dialog box comes up, there's a check box to the right of the dialog box that has the word "Exclusive" right next to it. If the box is checked, then your database will open exclusively. This box must be unchecked in order for the database to be opened in a shared mode.
 
How does Access decide whether or not the check box (to open the database exclusively) is checked? This is where the Options menu comes in handy.
 
Options menu
This is the next easiest method for changing the way a database is opened. You simply access the Tools | Options menu and then click the Advanced tab. In the top right-hand corner of the screen, you'll see Default Open Mode. There are two option buttons, labeled Shared and Exclusive -- simply click the Shared option button and you're set to go.
 
Registry
If the database is opened from the Most Recently Used (MRU) list, then Access decides how to open the database based on the MRUFlags value under HKEY_CURRENT_USER\Software\ Microsoft\Office\8.0\Access\Settings. In the MRU flags, 2 signals the database to be opened exclusively, and 0 is shared. You can set the default for your database from there. Here are some sample entries for four items on the MRU:
 

MRUFlags1      c:\program files\Northwind.mdb

MRUFlags2      c:\nurseryrhymes\SimpleSimon.mdb

MRUFlags3      c:\monkeys\GrapeApe.mdb

MRUFlags4      c:\doodle\Cartoons.mdb

 

MRUFlags1v     0x00000002 (2)

MRUFlags2      0x00000000 (0)

MRUFlags3      0x00000000 (0)

MRUFlags4      0x00000000 (0)

 
 
This example shows the default for the first database, Northwind.mdw, as being set to exclusive. The next three databases are shared.
 
The MRU settings default to the way the database was shared the last time it was opened. If you don't want to use that setting, this information will let you change the setting so it opens the way you want it to.