DAO Object Model: The Definitive Reference By Helen Feddema 1st Edition January 2000 (est.) ISBN 1-56592-435-5 400 pages, Includes CD-ROM

Chapter 2
DBEngine Object

The DBEngine object is the highest-level object in the Jet/DAO object model, representing the entire hierarchy of data objects you can manipulate from code. There is only one DBEngine object, and you can't create additional ones. This object corresponds to the Application object that is at the top of most of the Microsoft Office object hierarchies.

DAO has two flavors: Jet and ODBCDirect. The Jet version of DAO lets you access data in Jet databases (basically, Access databases or .mdb databases used by other Microsoft applications), Jet-connected ODBC databases, and installable ISAM data sources such as Paradox or Lotus (although ISAM data sources have become much less important in Office 2000). The object model for the Jet version of DAO is shown in Figure 2-1.

Figure 2-1. The DAO object model for Jet workspaces

The ODBCDirect version of DAO is used to access data sources through ODBC without use of the Jet engine. Its object model is shown in Figure 2-2. This object model lacks some of the components that are needed for working with data in Jet databases. This chapter will cover the more extensive Jet version of the DAO object model.

The DBEngine object contains two collections, Errors and Workspaces, which will be discussed in the next two chapters. The VBA code behind an Access form, shown in Example 2-1, lists the DBEngine object's properties if Access has been started by the user, rather than through automation. The resulting message box is shown in Figure 2-3.

Example 2-1: Access Code to Display DBEngine Properties

Private Sub cmdDBEngine_Click()   Call AppProperties(Me)   End Sub   Private Function AppProperties(obj As Object) As Integer   Dim objAccess As Access.Application Dim i As Integer Dim strProperties As String   On Error Resume Next Set objAccess = obj.Application   If objAccess.UserControl = True Then For i = 0 To objAccess.DBEngine.Properties.Count - 1 strProperties = strProperties & _ objAccess.DBEngine.Properties(i).Name & vbCrLf Next i End If   MsgBox left(strProperties, Len(strProperties) - 2), _ vbOKCancel, "DBEngine properties"   End Function

 

Figure 2-2. The DAO object model for ODBC workspaces

 

Figure 2-3. DBEngine object properties when Access is started by the user

Although the example in Access Help implies that you will get a different set of properties when Access is started from Automation code rather than by the user, the code in Example 2-2, an Excel VBA function that opens Access using automation code, produces the same listing of properties.

Example 2-2: Excel VBA Code to Display DBEngine Properties

Function ListDBEngineProps()   Dim objAccess As New Access.Application Dim i As Integer Dim strProperties As String If objAccess.UserControl = False Then For i = 0 To objAccess.DBEngine.Properties.Count - 1 strProperties = strProperties & _ objAccess.DBEngine.Properties(i).Name & vbCrLf Next i End If MsgBox Left(strProperties, Len(strProperties) - 2), _ vbOKCancel, "DBEngine properties"   End Function

Since it is more than likely that there will be several different versions of DAO on any computer (installed by various versions of Microsoft applications), in order to avoid an "ActiveX component can't create object" error, you should append the version number after the DBEngine object reference in your call to the CreateObject function, as shown in the following Word VBA and Outlook VBScript code samples. For Access 97, the DAO version ranges from 3.0 (referenced in code as 30) to 3.51 (referenced as 35) depending on whether you have the original release, SR-1, or SR-2; for Access 2000, it is 3.6 (referenced as 36).

TIP: The first parameter passed to the VBA CreateObject function is a programmatic identifier. When it includes only an object reference (as in DAO.DBEngine), it is a version-independent programmatic identifier. When it includes a version in the object reference (as in DAO.DBEngine.36), it is a version-dependent programmatic identifier. Typically, version-independent programmatic identifiers in the registry are updated to include information about the most recent installed version of that object. DAO, however, does not appear to do this, making it more important that a version-dependent programmatic identifier be used when calling the CreateObject function.

In order to use the DAO object model from other applications, such as Word or Outlook, you need to first define a reference to the DBEngine object. Example 2-3 and Example 2-4, both of which use the CreateObject function to create an instance of the DBEngine object, show how to do this using VBA for Word and VBScript for Outlook, respectively. The Word VBA code in Example 2-3 imports three fields from the sample Northwind Products table into a Word table that's also created from code (see Table 2-1 for a portion of the table of imported data). The Outlook VBScript code in Example 2-4 imports three fields of data from the Northwind Customers table into a list box on an Outlook form. The Word VBA code in Example 2-3 uses early binding (after setting references to the Access and DAO object libraries) and declares its variables as specific data types; the Outlook VBScript code in Example 2-4 uses late binding and no data types because VBScript does not support early binding or data typing.

Table 2-1: Data Imported into a Word Table from the Northwind Products Table

ID	Product Name	Units in Stock
1	Chai	39
2	Chang	17
3	Aniseed Syrup	13
4	Chef Anton's Cajun Seasoning	53
5	Chef Anton's Gumbo Mix	0
6	Grandma's Boysenberry Spread	120
7	Uncle Bob's Organic Dried Pears	15
8	Northwoods Cranberry Sauce	6
9	Mishi Kobe Niku	29
10	Ikura	31

 

Example 2-3: Word VBA Code to Access the DBEngine Object

Sub FillTableFromAccess()   Dim dao As DAO.DBEngine Dim wks As Workspace Dim dbs As Database Dim rst As Recordset Dim strAccessDir As String Dim strDBName As String Dim objAccess As New Access.Application   'Get path to Access database directory from Access SysCmd function. Set objAccess = CreateObject("Access.Application") strAccessDir = objAccess.SysCmd(9) strDBName = strAccessDir & "Samples\Northwind.mdb" Debug.Print "DBName: " & strDBName objAccess.Quit   'Set up reference to Access database. Set dao = CreateObject("DAO.DBEngine.35") Set wks = dao.Workspaces(0) Set dbs = wks.OpenDatabase(strDBName)   'Set reference to Products table. Set rst = dbs.OpenRecordset("Products")   'Create 3-column Word table to fill with Access data. ActiveDocument.Tables.Add Range:=Selection.Range, _ NumRows:=2, NumColumns:=3 With Selection .TypeText Text:="ID" .MoveRight Unit:=wdCell .TypeText Text:="Product Name" .MoveRight Unit:=wdCell .TypeText Text:="Units in Stock" .MoveRight Unit:=wdCell End With   'Write info from a record in Products to a row of the Word table; 'loop through recordset until all data has been written to the table. Do Until rst.EOF With Selection .TypeText Text:=rst![ProductID] .MoveRight Unit:=wdCell .TypeText Text:=rst![ProductName] .MoveRight Unit:=wdCell .TypeText Text:=rst![UnitsInStock] .MoveRight Unit:=wdCell End With rst.MoveNext Loop   End Sub

 

Example 2-4: VBS Outlook Code to Import Data into a Listbox on an Outlook Form

Function FillListBox()   Dim rst Dim dao Dim wks Dim dbs Dim ctl Dim strAccessDir Dim objAccess Dim CustomerArray(99, 2)   'Get path to Access database directory from Access SysCmd function. Set objAccess = Item.Application.CreateObject("Access.Application") strAccessDir = objAccess.SysCmd(9) strDBName = strAccessDir & "Samples\Northwind.mdb" 'MsgBox "DBName: " & strDBName objAccess.Quit   'Set up reference to Access database. Set dao = Application.CreateObject("DAO.DBEngine.35") Set wks = dao.Workspaces(0) Set dbs = wks.OpenDatabase(strDBName)   'Retrieve Customer info from table. Set rst = dbs.OpenRecordset("Customers") Set ctl = Item.GetInspector.ModifiedFormPages( _ "Filling Combo & List Boxes").Controls("lstCustomers")   ctl.ColumnCount = 3 ctl.ColumnWidths = "50; 150 pt; 75 pt"   'Assign Access data to an array of 3 columns and 100 rows. CategoryArray(99, 2) = rst.GetRows(100)   'Display array data in list box. ctl.Column() = CategoryArray(99, 2)   End Function

In addition to the 2 collections we've briefly discussed (the Errors and Workspaces collections), the DBEngine object supports 8 properties (shown in Table 2-2) and 12 methods (shown in Table 2-3).

Table 2-2: DBEngine Properties

Property	Description
DefaultPassword	Defines the type of the next Workspace object to be created
DefaultUser	Defines the user name used to create the default workspace whenever it is initialized
DefaultType	Defines the password used to create the default workspace whenever it is initialized
IniPath	Indicates the registry key containing information about Jet engine settings
LoginTimeout	Determines the number of seconds to wait before an attempt to log onto an ODBC database is considered unsuccessful
Properties	Returns a reference to the DBEngine object's Properties collection
SystemDB	Defines the Jet engine's workgroup information file
Version	Indicates the version of the Jet engine

 

Table 2-3: DBEngine Methods

Method	Description
BeginTrans	Begins a new transaction
CommitTrans	Ends a transaction and saves the changes
CompactDatabase	Compacts a closed database
CreateDatabase	Creates a new database
CreateWorkspace	Creates a Workspace object
Idle	Suspends processing to allow the database engine to complete any pending tasks
OpenConnection	Opens a database connection
OpenDatabase	Opens a database
RegisterDatabase	Stores connection information for an ODBC data source in the system registry
RepairDatabase	Repairs a corrupted database
Rollback	Ends a transaction and discards its changes
SetOption	Temporarily overrides default configuration settings

It's worth emphasizing that all of the members of the DBEngine object are globally available to any VB or VBA application (although not to VBS host applications) that has a reference to the DAO object library. In other words, although you can reference each member of the DBEngine object by explicitly including a reference to the DBEngine object, you can also reference the member without referencing the DBEngine object. For example, the following two statements are identical:

Set db = DBEngine.Workspaces(0).Databases(0)
Set db = Workspaces(0).Databases(0)

Similarly, the following two method calls are identical:

Set db = DBEngine.OpenDatabase(NORTHWIND)
Set db = OpenDatabase(NORTHWIND)

The global members of the DBEngine Object are listed in Table 2-4.

Table 2-4: Global Members of the DBEngine Object

DBEngine Member	Type	Global Availability
BeginTrans	Method	Yes
CommitTrans	Method	Yes
CompactDatabase	Method	Yes
CreateDatabase	Method	Yes
CreateWorkspace	Method	Yes
DefaultPassword	Property	Yes
DefaultType	Property	Yes
DefaultUser	Property	Yes
Errors	Property/Collection	Yes
Idle	Method	Yes
IniPath	Property	Yes
LoginTimeout	Property	Yes
OpenConnection	Method	Yes
OpenDatabase	Method	Yes
Properties	Property/Collection	Yes
RegisterDatabase	Method	Yes
RepairDatabase	Method	Yes
Rollback	Method	Yes
SetOption	Method	Yes
SystemDB	Property	Yes
Version	Property	Yes
Workspaces	Property/Collection	Yes

The following sections document the DBEngine object's methods and properties, with the exception of the three properties (Errors, Properties, and Workspaces) that return collections.

Access to the DBEngine Object

Creatable

Yes

Returned by

The DBEngine object is the top-level object in the DAO object model.

DBEngine Properties

DefaultPassword

Data Type

String

Description

When it is initialized, uses a case-sensitive string to set the password used to create the default workspace. The password string can be 1-20 characters in length for Jet workspaces or any length for ODBCDirect workspaces. Any character is permitted except ASCII 0. By default, DefaultPassword is a zero-length string, which means that the database is not password protected. DefaultPassword must be set before the default workspace is used in order to have any effect. Use this method if you want to assign a certain password to all new databases. See the DefaultUser entry for a code sample using this method.

DefaultType

Data Type

Long

Description

Sets or returns a value dictating what type of workspace ( Jet or ODBCDirect) the next Workspace object created will be. The property can be set to the values listed in Table 2-5.

Table 2-5: The Values of the DefaultType Property

Named Constant	Value	Description
dbUseJet	2	Creates Workspace objects connected to the Jet engine
dbUseODBC	1	Creates Workspace objects connected to an ODBC data source

DefaultUser

---

Data Type

String

Description

When it is initialized uses a string 1-20 characters in length to set the user name used to create the default workspace. Alphabetic characters, accented characters, numbers, spaces, and symbols are permitted, except for the characters listed in Table 2-6, leading spaces, and control characters (ASCII 00 to ASCII 31). By default, DefaultUser is set to "admin."

Normally, user names aren't case sensitive, except when you are recreating a user account created or deleted in another workgroup. In that case, the user name must be a case-sensitive match to the original name.

Table 2-6: Characters Not Permitted in DefaultUser Strings

Character	ASCII Number	Description
"	34	Double quotes
/	47	Forward slash
\	92	Backslash
[	91	Left bracket
]	93	Right bracket
:	58	Colon
|	124	Pipe
<	60	Less than
>	62	Greater than
+	43	Plus
=	61	Equal sign
;	59	Semicolon
,	44	Comma
?	63	Question mark
*	42	Asterisk

IniPath

---

Data Type

String

Description

Sets or returns a value indicating the Windows Registry key containing information about Microsoft Jet database engine settings or parameters needed for installable ISAM (Indexed Sequential Access Method) databases such as Excel, dBASE, and Paradox. This property must be set before invoking any other DAO code, or the change has no effect.

You can use either the HKEY_LOCAL_MACHINE or the HKEY_LOCAL_USER key (as a String) with this property. If you don't supply a root key, HKEY_LOCAL_MACHINE will be used as the default. When setting the property, note that the DAO engine does not test for the existence of the registry key; it simply assigns the string you specify to the IniPath property.

The code sample from Access 97 and Access 2000 Help does not work if run from Microsoft Access, presumably because of some "under the hood" initialization of DAO by Access itself. (Recall that the IniPath property must be set before any other DAO code is invoked.) It does work properly if run from Visual Basic or any other VBA-hosted environment.

Word VBA Code

This example shows how to use the IniPath property to retrieve and set the value of the registry key containing information about the Jet database engine or installable ISAM driver settings, as shown in Figure 2-4.

Private Sub cmdIniPath_Click()
 
On Error GoTo cmdIniPath_ClickError
 
   Debug.Print "Original IniPath setting = " & _
         IIf(DBEngine.IniPath = "", "[Empty]", DBEngine.IniPath)
      DBEngine.IniPath = _
         "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\" & _
         "Jet\3.5\ISAM Formats\Excel 3.0"
   Debug.Print "New IniPath setting = " & _
      IIf(DBEngine.IniPath = "", "[Empty]", _
          DBEngine.IniPath)
 
cmdIniPath_ClickExit:
   Exit Sub
 
cmdIniPath_ClickError:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume cmdIniPath_ClickExit
End Sub


Figure 2-4. 
The Windows 98 Registry Editor, showing Excel ISAM formats 




 

LoginTimeout

---

Data Type

Integer

Description

Sets or returns the number of seconds allowed before an error occurs when you try to log on to an ODBC database. The default value is 20 seconds. If LoginTimeout is set to 0, no timeout will occur.

SystemDB

---

Data Type

String

Description

Sets or returns the workgroup information file (typically System.mdw) for Microsoft Jet workspaces. In the interface, you can change to a different workgroup file by using the Workgroup Administrator applet (Wrkgadm.exe), usually found in the Windows system folder for Access 97, and the Office folder for Office 2000. Typically, you need to change to a different workgroup information file in order to log on to a secured database.

Version

Data Type

String

Description

Returns a string representing the version of DAO currently in use. Table 2-7 shows how Jet versions map with various Microsoft application versions.

Table 2-7: Jet Engine and Application Versions

Jet Version and Year	Access	Visual Basic	Excel	Visual C++
1.0 (1992)	1.0	N/A	N/A	N/A
1.1 (1993)	1.1	3.0	N/A	N/A
2.0 (1994)	2.0	N/A	N/A	N/A
2.5 (1995)	N/A	4.0 (16-bit)	N/A	N/A
3.0 (1995)	95 (7.0)	4.0 (32-bit)	95 (7.0)	4.x
3.5 (1996)	97 (8.0)	5.0	97 (8.0)	5.0
3.6 (1999)	2000 (9.0)	N/A	N/A	N/A

VBA Code

This example reports on the Jet version currently in use in the running database:

Private Sub cmdVersion_Click()
 
   MsgBox "Currently using Jet, v. " & DBEngine.Version
 
End Sub

DBEngine Methods

BeginTrans

---

DBEnginw.BeginTrans()

The BeginTrans method is listed in Access 97 Help as a method of the DBEngine object. Actually, it is a method of the Workspace object, one of the members of the Workspaces collection under the DBEngine object, so it will be discussed in Chapter 4, Workspaces Collection and Workspace Object. This error has been corrected in Access 2000 Help.

CommitTrans

---

DBEngine.CommitTrans()

As with BeginTrans, this method is actually a method of the Workspace object and will be discussed in Chapter 4.

CompactDatabase

---

DBEngine.CompactDatabase srcname, dstname, [dstlocale], [options], [srclocale]

Argument	Data Type	Description
srcname	String	The filename (including extension) of a closed database. May include full path and can be in UNC convention (\\server1\share1\dir1\db1.mdb). If srcname is currently open, DAO generates runtime error 3049 ("Can't open database...") or 3356 ("You attempted to open a database that is already opened exclusively...").
dstname	String	The filename and path of the new, compacted database. Must be different from srcname. If dstname already exists, runtime error 3204, "Database already exists," is generated.
dstlocale	Variant	(Optional) Sets the collating order for creating dstname. If omitted, the locale is the same as that of srcname. See Table 2-8 for a list of constants that can be used for this argument and their values. Help says that you can create a password for dstname by concatenating the password string (starting with ";pwd=") with a constant in the dstlocale argument to save having to specify two parameters. If dstname is to be a password-protected database that uses the same locale as srcname, you can omit the dstlocale constant and supply just the password preceded by the string ";pwd=".
options	Integer	(Optional) Defines the format (version and encryption) of the database. See Table 2-9 for a list of constants that can be used for this argument, and their integer values. If omitted, the encryption and version of dstname is the same as that of srcname. If supplied, the version constant must represent the same or a later version than that of srcname.
srclocale	Variant	(Optional) For password-protected databases, a Variant containing a String expression. The string ";pwd=" must precede the password. This setting is ignored if you include a password setting in the dstlocale argument.

This method must have been changed at the last moment before the release of Office 97, because Access 97 Help incorrectly lists the parameters as olddb, newdb, locale, options, and password . The correct parameter names are listed in the IntelliSense popup. These incorrect parameter names are still listed in Access 2000 Help.

Table 2-8: The dstlocale Named Constants

Named Constant	Value	Description
dbLangGeneral	";LANGID=0x0409;CP=1252;COUNTRY=0"	English, German, French, Portuguese, Italian, and Modern Spanish
dbLangArabic	";LANGID=0x0401;CP=1256;COUNTRY=0"	Arabic
dbLangChineseSimplified	";LANGID=0x0804;CP=936;COUNTRY=0"	Simplified Chinese
dbLangChineseTraditional	";LANGID=0x0404;CP=950;COUNTRY=0"	Traditional Chinese
dbLangCyrillic	";LANGID=0x0419;CP=1251;COUNTRY=0"	Russian
dbLangCzech	";LANGID=0x0405;CP=1250;COUNTRY=0"	Czech
dbLangDutch	";LANGID=0x0413;CP=1252;COUNTRY=0"	Dutch
dbLangGreek	";LANGID=0x0408;CP=1253;COUNTRY=0"	Greek
dbLangHebrew	";LANGID=0x040D;CP=1255;COUNTRY=0"	Hebrew
dbLangHungarian	";LANGID=0x040E;CP=1250;COUNTRY=0"	Hungarian
dbLangIcelandic	";LANGID=0x040F;CP=1252;COUNTRY=0"	Icelandic
dbLangJapanese	";LANGID=0x0411;CP=932;COUNTRY=0"	Japanese
dbLangKorean	";LANGID=0x0412;CP=949;COUNTRY=0"	Korean
dbLangNordic	";LANGID=0x041D;CP=1252;COUNTRY=0"	Nordic languages (Microsoft Jet database engine version 1.0 only)
dbLangNorwDan	";LANGID=0x0414;CP=1252;COUNTRY=0"	Norwegian and Danish
dbLangPolish	";LANGID=0x0415;CP=1250;COUNTRY=0"	Polish
dbLangSlovenian	";LANGID=0x0424;CP=1250;COUNTRY=0"	Slovenian
dbLangSpanish	";LANGID=0x040A;CP=1252;COUNTRY=0"	Traditional Spanish
dbLangSwedFin	";LANGID=0x040B;CP=1252;COUNTRY=0"	Swedish and Finnish
dbLangThai	";LANGID=0x041E;CP=874;COUNTRY=0"	Thai
dbLangTurkish	";LANGID=0x041F;CP=1254;COUNTRY=0"	Turkish

 

Table 2-9: The Options Named Constants

Named Constant	Value	Description
dbEncrypt	2	Encrypts the database while compacting
dbDecrypt	4	Decrypts the database while compacting
dbVersion10	1	Creates a database that uses the Microsoft Jet database engine version 1.0 file format while compacting
dbVersion11	8	Creates a database that uses the Microsoft Jet database engine version 1.1 file format while compacting
dbVersion20	16	Creates a database that uses the Microsoft Jet database engine version 2.0 file format while compacting
dbVersion30	32	Creates a database that uses the Microsoft Jet database engine version 3.0 file format (compatible with version 3.5) while compacting

The CompactDatabase method copies and then compacts a closed database, with options for changing the database's DAO version, collating order, and encryption. The basic functionality of the CompactDatabase method in Access 97 is somewhat different than compacting in the Access 97 interface, where you can use the new compact-in-place functionality, eliminating the need to compact to another database (as in earlier versions of Access). Access 2000 combines Compact and Repair into a single command in the interface, also as an in-place option. The CompactDatabase method also offers a few extra choices not available in the interface.

WARNING: Specifying a different dbVersion constant for the compacted database doesn't convert the compacted database to another version of Access  only the data format is affected (for purposes of DAO access). Microsoft Access objects, such as forms and reports, are not affected. To convert a database to another version of Access, you need to select Tools ➝ Database Utilities ➝ Convert Database in the Access interface. The only supported conversion on this menu is from Access 2000 to Access 97.

VBA Code

This code compacts a copy of an Access 97 database with DAO 3.51 code to a database with DAO 2.0 code. The On Error Resume Next statement is used before the Kill statement so that the Kill statement does not generate an error if the Northwind20.mdb database does not exist:

Private Sub cmdCompact_Click()
 
   Dim strAccessDir As String
   Dim strDBName35 As String
   Dim strDBName20 As String
 
   'Get default Access directory from SysCmd function.
   strAccessDir = SysCmd(9)
   strAccessDir = strAccessDir & "Samples\"
   strDBName35 = strAccessDir & "Northwind35.mdb"
   strDBName20 = strAccessDir & "Northwind20.mdb"
 
   On Error Resume Next
   Kill strDBName20
   DBEngine.CompactDatabase srcname:=strDBName35, _
      dstname:=strDBName20, _
      Options:=dbEncrypt + dbVersion20
 
End Sub

WARNING: In my experience, using the dstlocale parameter to set a new password, with or without a new locale, does not work; the following two code samples (without any error messages) do not create new databases. The third code sample, using the srclocale parameter, does create a new database with the new password.

The following three code examples attempt to define a password for the database produced by calling the CompactDatabase method. The first provides a locale (dblangSpanish) and a password in the argument passed to dstlocale. The second provides only a password in the dstlocale argument. Neither of these procedures successfully compacts the database. The final example, which passes the password in the srclocale argument, does work as expected:

Private Sub cmdCompactPW1_Click()
 
   Dim strAccessDir As String
   Dim strOldDBName As String
   Dim strNewDBName As String
   
   'Get default Access directory from SysCmd function.
   strAccessDir = SysCmd(9) & "Samples\"
   strOldDBName = strAccessDir & "Northwind.mdb"
   strNewDBName = strAccessDir & "New Northwind 1.mdb"
   
   On Error Resume Next
   Kill strNewDBName
   DBEngine.CompactDatabase srcname:=strOldDBName, _
      dstname:=strNewDBName, dstlocale:="dblangSpanish;pwd=Mortimer"
 
End Sub
 
Private Sub cmdCompactPW2_Click()
   
   Dim strAccessDir As String
   Dim strOldDBName As String
   Dim strNewDBName As String
   
   'Get default Access directory from SysCmd function.
   strAccessDir = SysCmd(9) & "Samples\"
   strOldDBName = strAccessDir & "Northwind.mdb"
   strNewDBName = strAccessDir & "New Northwind 2.mdb"
   
   On Error Resume Next
   Kill strNewDBName
   DBEngine.CompactDatabase srcname:=strOldDBName, _
      dstname:=strNewDBName, dstlocale:=";pwd=Mortimer"
 
End Sub
 
Private Sub cmdCompactSetPW3_Click()
   
   Dim strAccessDir As String
   Dim strOldDBName As String
   Dim strNewDBName As String
   
   'Get default Access directory from SysCmd function.
   strAccessDir = SysCmd(9) & "Samples\"
   strOldDBName = strAccessDir & "Northwind.mdb"
   strNewDBName = strAccessDir & "New Northwind 3.mdb"
   
   On Error Resume Next
   Kill strNewDBName
   DBEngine.CompactDatabase srcname:=strOldDBName, _
      dstname:=strNewDBName, srclocale:=";pwd=Mortimer"
   
End Sub

CreateDatabase

---

The CreateDatabase method is listed in Help as a method of the DBEngine object, but actually it is a method of a Workspace object in the Workspaces collection under the DBEngine object, so it will be discussed in Chapter 4. This error remains in Access 2000 Help.

CreateWorkspace

---

Set workspace = CreateWorkspace(name, username, password, [usetype])

Argument	Data Type	Description
workspace	Workspace object	The Workspace object you are creating.
name	String	The name of the new Workspace object. Must be unique.
username	String	The new Workspace's owner.
password	String	The new Workspace's password. Can be up to 14 characters long and can include any characters except ASCII Character 0 (Null).
usertype	Integer	(Optional) A named constant or Integer value indicating the workspace type (see Table 2-10).

Creates a second workspace within the DBEngine object. The first (default) workspace is created automatically, so you need to use this method only in the rare cases in which you need to work with two or more workspaces simultaneously. Workspaces aren't permanent and can't be saved to disk. Once created, only the Workspace object's Name property can be altered, and that property can only be altered before appending the new Workspace to the Workspaces collection.

TIP: When calling CreateWorkspace, it is not necessary to specify DBEngine before the method call.

Table 2-10: The Type Intrinsic Constants

Named Constant	Value	Description
dbUseJet	2	Creates a Jet workspace
dbUseODBC	1	Creates an ODBCDirect workspace

Idle

---

DBEngine.Idle[dbRefreshCache]

This method suspends data processing so the Jet database engine can complete any pending tasks, such as memory optimization or page timeouts. The optional dbRefreshCache argument forces any pending writes to .mdb files and refreshes memory with current data from the .mdb file.

TIP: There is no point in using this method in single-user environments, unless you are running multiple instances of an application. In multi-user situations, it may improve performance since it forces the database engine to write data to the disk, releasing memory locks.

VBA Code

This example uses the Idle method (run from a function called from the procedure) to ensure that an output procedure uses the most current data available from the database:

Private Sub cmdIdle_Click()
 
On Error GoTo cmdIdle_ClickError
 
   Dim strAccessDir As String
   Dim strDBName As String
   Dim dbs As Database
   Dim strCountry As String
   Dim strSQL As String
   Dim rstOrders As Recordset
 
   strAccessDir = SysCmd(9)
   strAccessDir = strAccessDir & "Samples\"
   strDBName = strAccessDir & "Northwind.mdb"
   Set dbs = OpenDatabase(strDBName)
 
   'Get name of country from user and build SQL statement with it.
   strCountry = Trim(InputBox("Enter country:"))
   strSQL = "SELECT * FROM Orders WHERE ShipCountry = '" & _
      strCountry & "' ORDER BY OrderID"
 
   'Open Recordset object with SQL statement.
   Set rstOrders = dbs.OpenRecordset(strSQL)
 
   'Display contents of Recordset object.
   IdleOutput rstOrders, strCountry
 
   rstOrders.Close
   dbs.Close
 
cmdIdle_ClickExit:
   Exit Sub
 
cmdIdle_ClickError:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume cmdIdle_ClickExit
 
End Sub
 
Sub IdleOutput(rstTemp As Recordset, strTemp As String)
 
   'Call the Idle method to release unneeded locks, force
   'pending writes, and refresh the memory with the current
   'data in the .mdb file.
   DBEngine.Idle dbRefreshCache
 
   'Enumerate the Recordset object.
   With rstTemp
      Debug.Print "Orders from " & strTemp & ":"
      Debug.Print , "OrderID", "CustomerID", "OrderDate"
      Do While Not .EOF
         Debug.Print , !OrderID, !CustomerID, !OrderDate
         .MoveNext
      Loop
   End With
 
End Sub

OpenDatabase

---

This method is listed in Help as a method of the DBEngine object, but actually it is a method of a Workspace object in the Workspaces collection under the DBEngine object, so it will be discussed in Chapter 4.

RegisterDatabase

---

DBEngine.RegisterDatabase dsn, driver, silent, attributes

Argument	Data Type	Description
dsn	String	The name used in the OpenDatabase method, referring to a block of descriptive information about the data source.
driver	String	The name of the ODBC driver.
silent	Boolean	Set to True (-1) to hide the ODBC driver dialog boxes prompting for driver-specific information or False (0) to display the dialogs. If silent is True, attributes must contain all the necessary driver-specific information; otherwise, the dialogs will be displayed.
attributes	String	A list of keywords to be added to the Windows Registry in the form of a carriage-return-delimited string.

Enters connection information for an ODBC data source in the Windows Registry. This information is needed by the ODBC driver when the ODBC data source is opened during an ODBC session.

TIP: If you have access to the system on which the code will run, it is a lot easier to use the ODBC Data Source Administrator applet in the Control Panel to add new data sources or edit existing ones than to use the RegisterDatabase method.

VBA Code

This example registers a SQL Server data source named Publishers in the Windows Registry:

Private Sub cmdRegister_Click()
 
Dim dbsRegister As Database
   Dim strDescription As String
   Dim strAttributes As String
   Dim errLoop As Error
 
   'Build keywords string.
   strDescription = InputBox("Enter a description " & _
      "for the database to be registered.")
   strAttributes = "Database=pubs" & _
      vbCr & "Description=" & strDescription & _
      vbCr & "OemToAnsi=No" & _
      vbCr & "Server=Server1"
 
   'Update Windows Registry.
   On Error GoTo Err_Register
 
DBEngine.RegisterDatabase "Publishers", "SQL Server", _
      True, strAttributes
   On Error GoTo 0
 
   MsgBox "Use regedit.exe to view changes: " & _
      "HKEY_CURRENT_USER\" & _
      "Software\ODBC\ODBC.INI"
 
   Exit Sub
 
Err_Register:
 
   'Notify user of any errors that result from
   'the invalid data.
   If DBEngine.Errors.Count > 0 Then
      For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & _
            vbCr & errLoop.Description
 
Next errLoop
   End If
 
   Resume Next
 
End Sub

RepairDatabase

---

DBEngine.RepairDatabase name

Argument	Data Type	Description
name	String	The path and filename of an existing Jet database file. If your system supports UNC naming, you can specify a network path as \\server1\share1\dir1\db1.mdb.

This method attempts to repair a closed database. If you run it on an open database, an error occurs. In Access 2000, Compact and Repair are combined into a single command in the interface, but they remain separate methods in code.

TIP: Before repairing, it is a good idea to back up your database for safety. After repairing a database, it is a good idea to compact it.

VBA Code

This procedure repairs and compacts the Northwind sample database:

Private Sub cmdRepair_Click()
 
   Dim strAccessDir As String
   Dim strDBName As String
   Dim strCDBName As String
 
   'Get default Access directory from SysCmd function.
   strAccessDir = SysCmd(9)
   strDBName = strAccessDir & "Samples\Northwind35.mdb"
   strCDBName = strAccessDir & "Samples\Compacted Northwind.mdb"
 
   DBEngine.RepairDatabase strDBName
   DBEngine.CompactDatabase srcname:=strDBName, dstname:=strCDBName
 
End Sub

Rollback

---

Rollback is a method of both the DBEngine object and a Workspace object in the Workspaces collection. The method is generally used with a workspace, so it will be discussed in Chapter 4.

SetOption

---

DBEngine.SetOption option, value

Argument	Data Type	Description
option	Long	The value whose default setting (as defined in the registry) is to be changed (see Table 2-11)
value	Variant	Value to set value to

Temporarily overrides registry values at run time. The values supplied as option arguments all represent value entries of the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\x.x\Engines\Jet x.x, where x.x is the version of the Jet engine. The changes remain in effect until either SetOption is used again to change them or the DBEngine object is closed. This is in contrast to the SaveSetting statement in the Access object model, where the changes to the registry are preserved after the Access object is closed. (However, SaveSetting can only be used for changes to the settings for custom applications you create.)

Table 2-11: The Parameter Intrinsic Constants

Named Constant	Value	Description
dbExclusiveAsyncDelay	60	The ExclusiveAsyncDelay value
dbFlushTransactionTimeout	66	The FlushTransactionTimeout value
dbImplicitCommitSync	59	The ImplicitCommitSync value
dbLockDelay	63	The LockDelay value
dbLockRetry	57	The LockRetry value
dbMaxBufferSize	8	The MaxBufferSize value
dbMaxLocksPerFile	62	The MaxLocksPerFile value
dbPageTimeout	6	The PageTimeout value
dbRecycleLVs	65	The RecycleLVs value
dbSharedAsyncDelay	61	The SharedAsyncDelay value
dbUserCommitSync	58	The UserCommitSync value

WARNING: Don't confuse this method with the SetOption method in the Access object model, which refers to options in the Access Tools ➝ Options dialog.

VBA Code

This example lets the user temporarily change the maximum buffer size while a procedure is running. Note that the SetOption method is qualified as belonging to the DAO.DBEngine object to avoid an ambiguous reference, since SetOption is also a method of the Access Application object:

Private Sub cmdSetOption_Click()
 
   Dim intMaxBuffer As Integer
 
   'Ask user for new value for MaxBufferSize.
   intMaxBuffer = Val(InputBox("Enter the Maximum Buffer Size"))
   If intMaxBuffer > 0 Then
      DAO.DBEngine.SetOption dbMaxBufferSize, intMaxBuffer
      MsgBox "Maxmum buffer size set to " & intMaxBuffer & _
         " for the duration of the procedure"
   Else
      MsgBox "Maximum buffer size not changed"
   End If
 
End Sub

Back to: DAO Object Model: The Definitive Reference