  
Day 9
Visual Basic and
the Microsoft Jet Engine
Today you'll learn the details of the heart of the Visual Basic database system--Microsoft
Jet, the part of Visual Basic that handles all database operations. Whether you are
reading a Microsoft Access-format database, accessing a FoxPro file, or connecting
to a back-end database server using ODBC, Microsoft Jet is there. You can also use
Visual Basic to create a link between an existing Microsoft Jet database and data
in non-Microsoft Jet databases. This process of attaching external data sources provides
an excellent way to gain the advantages of the Microsoft Jet data access object layer
without having to convert existing data to Microsoft Jet format.
Today you will learn about several object collections that exist in Visual Basic
Microsoft Jet databases, including the new ODBCDirect objects available in the Microsoft
Jet 3.5 data engine. The objects covered in this chapter include the following:
- The DBEngine object
- The Workspace object
- The Database object
- The TableDef object
- The Field object
- The Index object
- The Relation object
- The Connection object
- The Recordset object
Throughout this lesson, you will build a single Visual Basic project that illustrates
the various data access objects you learn about today. You can apply the Visual Basic
coding techniques you learn today in future Visual Basic database projects.
What Is the Microsoft
Jet Database Engine?
The idea behind Microsoft Jet is that you can use one interface to access multiple
types of data. Microsoft designed Microsoft Jet to present a consistent interface
to the user regardless of the type of data the user is working with. Consequently,
you can use the same Microsoft Jet functions that you use to access an ASCII text
file or Microsoft Excel spreadsheet to also perform data operations on Microsoft
Access databases.
The Microsoft Jet engine is not a single program; it is a set of routines that
work together. The Microsoft Jet engine talks to a set of translation routines. These
routines convert your Microsoft Jet request into a request that the target database
can understand. Translation routines exist for Microsoft Access databases and for
non-Microsoft Access ISAM files such as dBASE, FoxPro, Paradox, and so on. A translation
set even exists to handle ODBC data sources using the Microsoft Jet interface. In
theory, you could access any data file format through the Microsoft Jet engine, as
long as some set of translation routines is made available to the engine.
NOTE: The detailed inner workings of the Microsoft
Jet engine go beyond the scope of this book. If you want to learn more about how
the Microsoft Jet interface works, you can obtain copies of several white papers
Microsoft has released on the topic of Microsoft Jet and the data access object layer.
You can get these papers through various online sources and through the Microsoft
Developers Network CDs.
Advantages of Microsoft
Jet over the Data Control Object
So far, you have learned to use the data control object to perform database administrative
tasks. The data-access objects (DAOs) addressed in this chapter perform all of the
services that the data control does, as well as many more. The data-access objects
give you complete control over database management.
If possible, use the data control object to manage your data. It is much easier
to use because many of the administrative functions are handled for you. You can
always add DAO in your code to work with the data control object.
Microsoft Jet Data
Objects
Microsoft Jet is organized into a set of data-access objects. Each of the objects
has collections, properties, and methods:
- Collections: Data-access objects that contain the same type of objects.
- Properties: The data contained within an object (control button, form, and so
on) that defines its characteristics. You set an object's properties.
- Methods: The procedures that can be performed on an object. You invoke a method.
The Microsoft Jet data access objects exist in a hierarchy, which means that a
top-down relationship exists between the objects. You learn the various Microsoft
Jet data-access objects in the order they reside in the hierarchy. As you push deeper
into the object hierarchy, you move toward more specific data objects. For example,
the first data object in the hierarchy is the DBEngine data-access object. All other
data-access objects exist underneath the DBEngine data-access objects.
NOTE: Throughout the rest of this chapter you
will see the phrases "data-access objects" and "data objects."
They both refer to the data-access object layer of the Microsoft Jet engine.
If you do not already have Visual Basic up and running, start it now and begin
a new Standard EXE project. Make sure that your system can reference the Microsoft
Jet 3.5 Data Access Object Library.
WARNING: If you don't have a reference to the
data-access object layer in your project, you cannot access any of the features of
the Microsoft Jet database engine.
If you can't tell whether your reference to the data access object is activated,
select Project | References... from the Visual Basic main menu. Use Figure 9.1 as
a reference.
Figure
9.1. Reviewing the data-access object
reference.
Throughout this chapter you'll be using the Microsoft Jet 3.5 data engine. This is
the most recent version of the data engine available. You can use older versions
of the data engine to maintain compatibility with earlier Visual Basic projects,
but it is recommended that you use Microsoft Jet 3.5 for all future projects.
The DBEngine Data
Object
The DBEngine data object is the default data object for all access to the database
operations under Visual Basic. Even if you do not explicitly use the DBEngine object,
your program is still accessing all other data objects by way of the DBEngine object
because it is invoked by default when Visual Basic begins any database work.
TIP: Even though Visual Basic does not require
that you explicitly use the DBEngine data object, you should use the object in all
your future Visual Basic projects to ensure maximum compatibility with any future
versions of Visual Basic.
The DBEngine Object Collections The DBEngine object contains three different object
collections. Each of these collections in turn contains other data-access objects.
To put it another way, the DBEngine is the top level of the DAO hierarchy, and it
contains the following collections:
- Workspaces: A collection of all the defined Workspace objects. The next section
of this chapter covers Workspace objects. The Workspace collection is the default
collection for the DBEngine object.
- Errors: A collection of the most recent database-related errors encountered in
this session. Error objects are covered later in this chapter.
- Properties: A collection of all the properties of the DBEngine object.
The DBEngine Object Properties Like all Visual Basic objects, you can list the
properties of the object by accessing the Properties collection. Let's write
a short bit of code to list (enumerate) all the properties of the DBEngine data access
object.
Before coding the DBEngine routines, you need to add a support routine to your
form. This routine makes it easier to read the output of the rest of the routines
in this chapter. Create a new function called ShowType and enter the code
from Listing 9.1.
Listing 9.1. Creating
the ShowType support routine.
Public Function ShowType(varTypeCode As Variant) As String
`
` return friendly name of variable type
`
Dim strReturn As String
`
Select Case varTypeCode
Case vbEmpty
strReturn = "Empty"
Case vbNull
strReturn = "Null"
Case vbInteger
strReturn = "Integer"
Case vbLong
strReturn = "Long"
Case vbSingle
strReturn = "Single"
Case vbDouble
strReturn = "Double"
Case vbCurrency
strReturn = "Currency"
Case vbDate
strReturn = "Date"
Case vbString
strReturn = "String"
Case vbObject
strReturn = "Object"
Case vbError
strReturn = "Error"
Case vbBoolean
strReturn = "Boolean"
Case vbVariant
strReturn = "Variant"
Case vbDataObject
strReturn = "dao"
Case vbDecimal
strReturn = "Decimal"
Case vbByte
strReturn = "Byte"
Case vbArray
strReturn = "Array"
Case Else
strReturn = "[" & CStr(varTypeCode) & "]"
End Select
`
ShowType = strReturn
`
End Function
Now you're ready to start DAO programming!
First, add a single button to the bottom of the current form. Set its Name property
to cmdDBEngine and its Caption property to DBEngine. Now double-click the button
to bring up the cmdDBEngine_Click event window and enter the code shown
in Listing 9.2.
Listing 9.2. Coding
the cmdDBEngine_Click event.
Private Sub cmdDBEngine_Click()
`
` show engine properties
`
On Error GoTo LocalErr
`
Dim objItem As Object
Dim strMsg As String
`
strMsg = ""
For Each objItem In DBEngine.Properties
strMsg = strMsg & objItem.Name
strMsg = strMsg & " = "
strMsg = strMsg & objItem.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(objItem.Type) & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "DBEngine"
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
End Sub
In Listing 9.2, you first tell Visual Basic to ignore any errors it might receive
while enumerating the DBEngine properties. Then you declare an object variable to
hold the properties of the DBEngine object and a string variable to hold the constructed
display message. You then use the Visual Basic 5 For..Each loop to list
each of the properties of the DBEngine object and build a display message string.
Save the form as FRMMSJET.FRM and the project as PRJMSJET.VBP.
When you run the project, you see a single button at the bottom of the form. Click
that button to force Visual Basic to enumerate the properties of the DBEngine data-access
object. Your screen should look like Figure 9.2.
Figure
9.2. The enumerated DBEngine properties.
Setting the DBEngine Properties You can set the properties of the DBEngine object
in your program, too. For example, you can use the IniPath property to point to a
special ISAM driver needed to process the related database:
DBEngine.IniPath = _"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\
ISAM Formats\FoxPro 3.0"
NOTE: In Microsoft Jet 2.5, the IniPath property
actually points to an INI file in the <WINDOWS> folder on the workstation.
In Microsoft Jet 3.0 and 3.5, the IniPath property is used to point to a location
in the workstation's System Registry.
The DefaultUser and DefaultPassword properties are covered when you learn about
the Workspace data-access object. The DBEngine Object Methods We'll cover six of
the Visual Basic methods that are associated with the DBEngine data-access object:
- RepairDatabase is used to fix corrupted Microsoft Jet database files.
- CompactDatabase is used to clean up, and also convert, existing Microsoft
Jet databases.
- RegisterDatabase is used to create a link between an external data source
and an existing Microsoft Jet database.
- Idle is used to force Visual Basic to pause processing while the DBEngine
updates the contents of any existing data access objects.
- SetOption is used to modify one or more of the Microsoft Jet Registry
settings at runtime.
- CreateWorkspace is used to establish a workspace for accessing one or
more databases. You'll learn about this method in the section on Workspace objects
later in this chapter.
Using the RepairDatabase Method You can use the RepairDatabase method
to fix corrupted Microsoft Jet database files. The default syntax to invoke this
method is
DBEngine.RepairDatabase databasename
Add another command button to the current project. Place it at the bottom of the
screen. Set its Name property to cmdDBRepair and its Caption property to DBRepair.
Add a CommonDialog control to the form and then enter the code in Listing 9.3.
Listing 9.3. Coding
the cmdDBRepair_Click event.
Private Sub cmdDBRepair_Click()
`
` fix a corrupted db
`
Dim strDBName As String
`
CommonDialog1.ShowOpen
strDBName = CommonDialog1.filename
`
If strDBName <> "" Then
DBEngine.RepairDatabase strDBName
MsgBox strDBName & " Repaired"
End If
`
End Sub
The code in Listing 9.3 declares a local variable for the database name and then
prompts the user to enter the name of a database to repair. After checking to make
sure a database name was entered, the code executes the RepairDatabase method
and reports the results.
Save and run the program. When you click the Repair button, locate and select
the DBREPAIR.MDB database (see Figure 9.3).
Figure
9.3. Entering a database to repair.
The repair method executes and the final message box appears.
WARNING: The RepairDatabase method overwrites
the existing file with the repaired database file. You should make a backup copy
of your database files before you execute the RepairDatabase method.
Using the CompactDatabase Method The CompactDatabase method cleans out
empty space in Microsoft Jet databases and performs general optimization chores that
improve access speed. You can also use the CompactDatabase method to convert
older versions of Microsoft Jet databases to newer versions.
The syntax for this method is
DBEngine.CompactDatabase oldDatabase, NewDatabase, locale, options
In this line, oldDatabase is the name (including path) of the database
to be compacted; NewDatabase is the name (including path) of the new, compacted
database; and locale is the language in which the data is written. Options
can be added to encrypt or decrypt a database, as well as to change versions. Multiple
options must be joined with the plus (+) sign.
Add another button to the PRJMSJET.VBP project. Set its Name property
to cmdDBCompact and its Caption property to &DBCompact. Enter the code in Listing
9.4 into the cmdDBCompact_Click event window. This code compacts any Microsoft
Jet database.
Listing 9.4. Coding
the cmdDBCompact_Click event.
Private Sub cmdDBCompact_Click()
`
` compact/convert an MS db
`
Dim strOldDBName As String
Dim strNewDBName As String
Dim intEncrypt As Integer
Dim strVersion As String
Dim intVersion As Integer
Dim strHeader As String
`
DBCompactStart:
`
` init vars
strOldDBName = ""
strNewDBName = ""
strVersion = ""
strHeader = "Compact Database Example"
`
` get db to read
CommonDialog1.DialogTitle = "Open Database to Convert"
CommonDialog1.Filter = "MS Jet | *.mdb"
CommonDialog1.ShowOpen
strOldDBName = CommonDialog1.filename
`
If Trim(strOldDBName) = "" Then Exit Sub
`
` get new name to write
CommonDialog1.DialogTitle = "Open Database to Write"
CommonDialog1.Filter = "MS Jet | *.mdb"
CommonDialog1.filename = "TDP_Fixed.mdb"
CommonDialog1.ShowOpen
strNewDBName = CommonDialog1.filename
`
If Trim(strNewDBName) = "" Then GoTo DBCompactStart
`
` get target version (must be same or higher!)
dbVersion:
intVersion = 0
strVersion = InputBox("Enter target version" & vbCrLf & "1.1, 2.0, 2.5, Â3.0, 3.5", strHeader)
MsgBox strVersion
Select Case Trim(strVersion)
Case "1.1"
intVersion = dbVersion11
Case "2.0"
intVersion = dbVersion20
Case "2.5"
intVersion = dbVersion20
Case "3.0"
intVersion = dbVersion30
Case "3.5"
intVersion = dbVersion30
Case Else
MsgBox "Invalid version!", vbCritical, "Version Error"
GoTo dbVersion
End Select
`
` encryption check
intEncrypt = MsgBox("Encrypt this Database?", vbInformation + vbYesNo, strHeader)
If intEncrypt = vbYes Then
intEncrypt = dbEncrypt
Else
intEncrypt = dbDecrypt
End If
`
` now try to do it!
DBEngine.CompactDatabase strOldDBName, strNewDBName, dbLangGeneral, intVersion + intEncrypt
MsgBox "Process Completed"
`
End Sub
The code in Listing 9.4 declares its local variables and then prompts the user to
enter the database file to compact or convert. If no filename is entered, the routine
skips to the exit. If a filename is entered, the user is prompted to enter a target
filename. If no name is entered, the program returns to try the whole thing again.
After getting the filename, the user is prompted to supply the target MSJH version
number. The value entered is checked and the user is returned to the input box if
an invalid option was entered. Finally, the user is asked whether the database should
be encrypted. After that, the CompactDatabase method is invoked.
Save your work and execute this program. You are prompted to enter the name of
the database to compact. Enter the path and name for DBREPAIR.MDB. You then
must enter a database to compact to. You can just accept the filename suggested to
you. Next, enter the version. Answer Yes when you are prompted with the encryption
question. The new database is now compacted and saved.
WARNING: If you plan to run your database application
using any 16-bit data tool, you'll need to store the database in the Microsoft Jet
2.5 version. Only Microsoft Jet 2.5 can run on both 32- and 16-bit platforms.
Using the RegisterDatabase Method The RegisterDatabase method enables
you to register an ODBC data source for Microsoft Jet access. The Visual Basic documentation
encourages programmers to rely on the Windows Control Panel ODBC Setup utility rather
than using the RegisterDatabase method. If, however, you want to perform
the ODBC registration process within your Visual Basic program, you can use the RegisterDatabase
method to do so.
The easiest way to provide ODBC registration capabilities in your program is to
supply a limited number of parameters and force Windows to present the ODBC registration
dialog for you--a fairly easy task. For this example, add a new command button to
the bottom of the form. Set its Name property to cmdDBRegister and its Caption property
to DBRegister. Add the code in Listing 9.5.
Listing 9.5. Coding
a DBRegistration routine.
Private Sub cmdDBRegister_Click()
`
` invoke ODBC registration
`
On Error Resume Next
`
Dim strDSN As String
Dim strDriver As String
Dim blnQuiet As Boolean
Dim strAttrib As String
Dim strDelim As String
`
strDelim = Chr(0)
strDSN = "TDPSample"
strDriver = "SQL Server"
blnQuiet = False
strAttrib = "SERVER=\\SQLSERVER2" & strDelim
strAttrib = strAttrib & "DATABASE=ProductionData" & strDelim
strAttrib = strAttrib & "DESCRIPTION=Sample ODBC Registration" & strDelim
`
DBEngine.RegisterDatabase strDSN, strDriver, blnQuiet, strAttrib
`
End Sub
The preceding code first tells Visual Basic to ignore any reported errors, and then
it supplies a set of parameters for creating an ODBC data source. The parameters
for the RegisterDatabase method are
- SourceName: The name that will be used as the database name for the
OpenDatabase method.
- DriverName: The name of an ODBC driver installed and available on your
work- station.
- SilentFlag: Setting this to False forces Windows to present
the ODBC registration dialog box. If it is set to True, Windows attempts
to register the ODBC data source without prompting the user with the ODBC registration
dialog box.
- AttributeList: A list of attribute settings for the ODBC source. Examples
of attributes include any server device name, database name, and any other parameters
required by the back-end database server.
WARNING: The Microsoft Visual Basic documentation
tells you to create an Attributes list with each attribute separated by a CR-LF pair.
This is not correct. You should delimit each attribute entry with a CHR(0) in order
for the RegisterDatabase routine to work properly.
Save and run the project. When you click the DBRegister button, you see the Windows
ODBC Registration dialog box appear with some of the parameters already entered.
You can complete the information and click OK to register the ODBC data source on
your system. Refer to Figure 9.4 as an example.
Figure
9.4. Registering an ODBC data source.
Completing an ODBC registration inserts data into the HKEY_USERS\DEFAULT\ODBC\ODBC.INI
section of the Windows Registry on 32-bit systems. The data is added to the ODBC.INI
file in the <WINDOWS> folder on 16-bit systems. You can add features
to the earlier cmdDBRegister_Click example by prompting the user to enter
the SourceName and DriverName. You could also fill out all values
within the program and set the SilentFlag to True. In this way,
you could use the routine to install new ODBC connections for Visual Basic applications
without requiring the user to know anything at all about ODBC or Microsoft Jet.
WARNING: Failure to register an ODBC data source
properly can result in un- expected errors and possible loss of data. Be sure to
test your RegisterDatabase routines completely before using them on live
data.
The SetOption Method The SetOption method of the DBEngine object allows
you to override performance values in the Registry at runtime. You can use this option
to perform runtime tuning of the Microsoft Jet engine. Table 9.1 shows the values
you can adjust using the SetOption method.
Table 9.1. Tuning values for the SetOption method of the DBEngine.
Constant |
Description |
dbPageTimeout |
PageTimeout key |
dbSharedAsyncDelay |
SharedAsyncDelay key |
dbExclusiveAsyncDelay |
ExclusiveAsyncDelay key |
dbLockRetry |
LockRetry key |
dbUserCommitSync |
UserCommitSync key |
dbImplicitCommitSync |
ImplicitCommitSync key |
dbMaxBufferSize |
MaxBufferSize key |
dbMaxLocksPerFile |
MaxLocksPerFile key |
dbLockDelay |
LockDelay key |
dbRecycleLVs |
RecycleLVs key |
dbFlushTransactionTimeout |
FlushTransactionTimeout key |
For example, to adjust the value of the LockRetry setting, you could use the following
code:
DBEngine.SetOption dbLockRetry = dbLockRetry * 1.5
Any changes made to the Registry settings are in effect only as long as your program
is running. They are not saved to the Windows Registry. The Idle Method The Idle
method forces Visual Basic to pause while the DBEngine catches up on any changes
that have been made to all the open data-access objects. This method becomes useful
when you have a lot of database traffic or a lot of data-access objects in a single
program. The syntax is simple:
DBEngine.Idle
The Workspace Data Object
The Workspace data object identifies a database session for a user. Workspaces
are created each time you open a database using Microsoft Jet. You can explicitly
create Workspace objects to manage database transactions for users and to provide
a level of security during a database session. Even if you do not explicitly create
a Workspace object, Visual Basic 5.0 creates a default Workspace each time you begin
database operations.
NOTE: Although you can create Workspace data
objects, you can't save them. Workspace objects are temporary. They cease to exist
as soon as your program stops running or as soon as you close your last data access
object.
The Workspace object contains three collections, two properties, and eight methods.
The Workspaces collection contains one property (Count) and one method (Refresh).
The Workspaces collection enables you to access multiple Workspace objects. The Workspace
object enables you to access the properties, collections, and methods of the named
Workspace object. The Workspace Object Collections The Workspace data-access object
contains three object collections:
- Databases: A collection of all the Database objects opened for this Workspace
object. This is the default collection.
- Groups: A collection of all the defined Group objects that have access to this
Workspace.
- Users: A collection of all the defined User objects that have access to this
Workspace.
NOTE: You can only access the Group and User
objects if the Microsoft Jet security is activated. You can only activate Microsoft
Jet security through Microsoft Access. Although Visual Basic cannot initiate database
security, you can manage the security features using Visual Basic 5.0. Security features
are covered on Day 21, "Securing Your Database Applications."
The Workspace Object Properties Three Workspace object properties exist: the workspace
name, the workspace user name, and the Isolate ODBC Trans property. The Isolate ODBC
Trans property can be used to control the number of ODBC connections used during
the database session.
NOTE: ODBC connections are covered in depth in
Week 3 of this book. For now, just remember that you can control the number of connections
used by the session by altering the Isolate ODBC Trans property of the Workspace
object.
When you begin a database operation, Visual Basic 5.0 creates a default workspace
with the name #Default Workspace # and the user name admin. Let's
add some code to the CH1001.VBP project to enumerate the default Workspace
properties.
Add a new button to the form. Set its Name property to cmdWorkspaces and its Caption
property to &Workspaces. Enter the code in Listing 9.6 into the cmdWorkspaces_Click
code window.
Listing 9.6. Coding the cmdWorkspace_Click
event.
Private Sub cmdWorkspaces_Click()
`
` show workspaces
`
On Error GoTo LocalErr
`
Dim objWS As Workspace
Dim objItem As Object
Dim strMsg As String
`
strMsg = ""
For Each objWS In DBEngine.Workspaces
For Each objItem In objWS.Properties
strMsg = strMsg & objItem.Name
strMsg = strMsg & " = "
strMsg = strMsg & objItem.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(objItem.Type) & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "Workspaces"
`
Next
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
The code in Listing 9.6 should look familiar to you. It is almost identical to the
code used to enumerate the DBEngine properties. The only change that has been made
is that you now have two For ... Each loops in the routine.
The outer loop walks through all defined workspaces in the Workspace collection.
The inner loop walks through all the properties of the selected Workspace object.
Save and run the program. When you click on the Workspace button, the program
lists all the properties of the object. Your screen should look like Figure 9.5.
Figure
9.5. Enumerating the Workspace object
properties.
Creating a New Workspace Object You can create new Workspace objects using the CreateWorkspace
method of the DBEngine. Even though Visual Basic 5 creates and uses a default Workspace
object when you first begin database operations, you should create an explicit, named
Workspace from within Visual Basic. When you create a unique Workspace object, you
isolate all your database operations into a single session. You can then group a
set of database transactions into a single session to improve database integrity
and security.
Let's add a new command button to the project that will create a new Workspace
object. Set the button's Name property to cmdNewWorkSpace and set its Caption property
to &New WS. Add the code in Listing 9.7 into the cmdNewWorkSpace_Click
code window.
Listing 9.7. Coding the cmdNewWorkSpace_Click
event.
Private Sub cmdNewWorkSpace_Click()
`
` create a new workspace
`
Dim ws As Workspace
Dim strWSName As String
Dim strWSUser As String
Dim strWSPassword As String
`
` init vars
strWSName = "ws" & App.EXEName
strWSUser = "admin"
strWSPassword = ""
`
` create it
Set ws = DBEngine.CreateWorkspace(strWSName, strWSUser, strWSPassword)
`
` append to collection
DBEngine.Workspaces.Append ws
`
` show them all
cmdWorkspaces_Click
`
End Sub
The code in Listing 9.7 establishes local variables and then initializes them to
the correct values. Notice that you can use any unique name you like for the Workspace
object, but you must use valid User and Password parameters. These values must already
exist in the system security file or as the default values if Microsoft Access security
is not active. Because you do not use Microsoft Access security here, this example
used the default admin user name and empty password.
You used the CreateWorkspace method to create a valid Workspace object.
You can now use this object throughout your program. As an option, you can add the
new object to the Workspaces collection, by using the Append method. After
adding the new object, you can force Visual Basic to display the Workspaces collection
to see your results.
WARNING: It is not a good idea to append your
workspace definitions to the Workspaces collection in a production environment. In
rare cases, someone could "listen in" on a network connection that uses
workspaces and hack one or more of the valid names, users, and passwords for secured
tables. This can be done by locating and walking through the Workspaces collection.
To prevent troubles, it is a good idea to never append workspaces to the Workspaces
collection.
Save and run the project. After you click the New WS button, you see two workspaces
displayed on the form. Check your screen against the one in Figure 9.6.
Figure
9.6. The results of adding a new Workspace
object.
Using the Workspace Object Methods The Workspace object methods fall into several
related groups. Table 9.2 shows the Workspace methods in their respective groups.
Table 9.2. Workspace methods.
Group |
Method |
Transactions |
BeginTrans, CommitTrans, Rollback |
Security |
CreateUser, CreateGroup |
Microsoft Jet |
CreateDatabase, OpenDatabase, Close |
ODBCDirect |
OpenConnection, Close |
You learn more about the Transaction group on Day 17, "Multiuser Considerations,"
and the Security group is covered on Day 21. The ODBCDirect methods are covered in
another section in this chapter. That leaves the Microsoft Jet database methods:
CreateDatabase, OpenDatabase, and Close. Using the Microsoft
Jet Database Methods The two database-related Workspace methods are CreateDatabase
and OpenDatabase. You use the CreateDatabase method to create a
new database, and you use the OpenDatabase method to open an existing database.
Let's first add a command button to create a new database. Set the button's Name
property to cmdCreateDB and its Caption property to CreateDB. Add the code in Listing
9.8 to the cmdCreateDB_Click code window.
Listing 9.8. Coding the cmdCreateDB_Click
event.
Private Sub cmdCreateDB_Click()
`
` create a new database
`
On Error Resume Next
`
Dim dbOne As Database
Dim dbTwo As Database
Dim ws As Workspace
Dim dbTemp As Database
`
Dim strDBNameOne As String
Dim strDBNameTwo As String
Dim strWSName As String
Dim strWSUser As String
Dim strWSPassword As String
Dim strMsg As String
`
` init vars
strDBNameOne = App.Path & "\CreateDBOne.mdb"
strDBNameTwo = App.Path & "\CreateDBTwo.mdb"
strWSName = App.EXEName
strWSUser = "admin"
strWSPassword = ""
`
` erase dbs if they exist
Kill strDBNameOne
Kill strDBNameTwo
`
` create workspace
Set ws = DBEngine.CreateWorkspace(strWSName, strWSUser, strWSPassword)
`
` create new jet db
Set dbOne = ws.CreateDatabase(strDBNameOne, dbLangGeneral, dbVersion30)
Set dbTwo = ws.CreateDatabase(strDBNameTwo, dbLangGeneral, dbVersion30)
`
` now show db collection
For Each dbTemp In ws.Databases
strMsg = strMsg & "Name: " & dbTemp.Name & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "CreateDB"
`
` now clean up your work
dbOne.Close
dbTwo.Close
ws.Close
`
Set dbOne = Nothing
Set dbTwo = Nothing
Set ws = Nothing
`
End Sub
The code in Listing 9.8 declares some variables, initializes them, and then goes
on to create a workspace for this session. It then creates the new Database object
and, finally, shows you all the databases that are a part of the current workspace.
Database objects are covered in greater detail in the next section of today's lesson.
It is important to note here that you create a Workspace object before you create
the database to make sure that the Database object becomes a part of the Workspace
object. Now all activity on that database is a part of the Workspace. As you can
see from the code, you can open more than one database in the same workspace and
group the database operations together.
It is also important to note the clean-up code added at the end of the routine.
When you finish using DAO objects, you need to close them and release the memory
they occupied by setting the program variables to Nothing. If you do not
do this, you risk running out of memory in DAO-intensive applications.
Save and run the project. When you click on the CreateDB button, the program creates
the new databases and shows the results on the form. Your screen should look like
Figure 9.7.
Figure
9.7. Creating a new database.
You can also open the same database in two different workspaces. This is handy when
you want to provide read/write access in one operation, but only want to provide
read-only access in another operation. As an example, add a new command button and
set its Name property to cmdOpenDB and its Caption property to &OpenDB. Add the
code in Listing 9.9 to the cmdOpenDB_Click code window.
Listing 9.9. Coding the cmdOpenDB_Click
event.
Private Sub cmdOpenDB_Click()
`
` open the same db in two workspaces
`
On Error Resume Next
`
Dim wsReadWrite As Workspace
Dim wsReadOnly As Workspace
Dim dbReadWrite As Database
Dim dbReadOnly As Database
Dim wsTemp As Workspace
Dim dbTemp As Database
`
Dim strWSrwName As String
Dim strWSroName As String
Dim strDBName As String
Dim strWSUser As String
Dim strWSPassword As String
Dim strMsg As String
`
` init vars
strWSrwName = "wsReadWrite"
strWSroName = "wsReadOnly"
strWSUser = "admin"
strWSPassword = ""
strDBName = App.Path & "\..\..\data\books5.mdb"
`
` create workspaces
Set wsReadWrite = DBEngine.CreateWorkspace(strWSrwName, strWSUser, ÂstrWSPassword)
Set wsReadOnly = DBEngine.CreateWorkspace(strWSroName, strWSUser, ÂstrWSPassword)
`
` add them to the workspaces collection
DBEngine.Workspaces.Append wsReadWrite
DBEngine.Workspaces.Append wsReadOnly
`
` open database in both ws
Set dbReadWrite = wsReadWrite.OpenDatabase(strDBName)
Set dbReadOnly = wsReadOnly.OpenDatabase(strDBName, , True)
`
` now show ws collection
For Each wsTemp In DBEngine.Workspaces
strMsg = strMsg & "Workspace: " & wsTemp.Name & vbCrLf
For Each dbTemp In wsTemp.Databases
strMsg = strMsg & vbTab & "Database: " & dbTemp.Name & vbCrLf
Next
Next
`
MsgBox strMsg, vbInformation, "OpenDB"
`
` cleanup code
dbReadOnly.Close
dbReadWrite.Close
wsReadOnly.Close
wsReadWrite.Close
`
Set dbReadOnly = Nothing
Set dbReadWrite = Nothing
Set wsReadOnly = Nothing
Set wsReadWrite = Nothing
`
End Sub
The code in Listing 9.9 declares and initializes several variables for the two Workspace
and Database object pairs, along with some temp objects for the collection enumeration
at the end of the routine. Then each workspace is created and appended to the collection,
and the single database is opened once under each workspace session. Finally, all
the workspaces and all their databases are listed on the screen. Note that you do
not have to use different user names and passwords for the two Workspace objects.
Save and run the project. When you click the OpenDB button, the program opens
the database under two different workspaces and shows the results. Notice that the
#Default Workspace# appears in the list. It always exists in the Workspaces collection.
Check your screen against Figure 9.8.
Figure
9.8. The results of the OpenDatabase
method in two workspaces.
Creating and Opening Non-Microsoft Jet Databases You can only create Microsoft Jet-format
databases using the CreateDatabase method. The other ISAM-type databases
(dBASE, FoxPro, Paradox, and Btreive) all use a single directory or folder as the
database object. To create non-Microsoft Jet databases, you have to create a new
directory or folder on the disk drive. You can then use the OpenDatabase
method to open the non-Microsoft Jet database. When it is opened, you can add tables
and indexes using the existing Visual Basic data objects and methods. You'll learn
about opening non-Microsoft Jet databases in the next section.
The Database Data Object
The Database data object has 5 collections, 8 properties, and 16 methods. The
Database object contains all the tables, queries, and relations defined for the database.
It is also part of the Databases collection of the Workspace object. The Database
object is created whenever you open a database with the OpenDatabase method.
Database objects continue to exist in memory until you use the Close method
to remove them.
WARNING: Do not confuse the Database object with
the database file. The Database object is a Visual Basic program construct used to
access the physical database file. Throughout this section, you will hear about the
Database object.
The Collections of the Database Object The Database object has five collections:
- TableDefs is the collection of Table objects that contain the detailed definition
of each data table in the database. This is the default collection.
- QueryDefs is the collection of SQL queries stored in the database.
- Relations is the collection of database integrity relationship definitions stored
in the database.
- Recordsets is the collection of active Recordsets opened from this database.
Recordsets include any Tables, Dynasets, or Snapshots currently open. Recordsets
are temporary objects and are not stored with the database file.
- Containers is the collection of all TableDefs, QueryDefs, and Relations stored
in the physical database file. You can use the Containers collection to enumerate
all the persistent (stored) objects in the database.
The data-access objects are described in later sections of this chapter. This
section focuses on the properties and methods associated with the Database data-access
object. The Properties of the Database Object The Database object has eight properties.
To illustrate these properties, add another command button to the CH1001.VBP
project. Set its Name property to cmdDBProperties and its Caption property to DB
Properties. Enter the code in Listing 9.10 into the cmdDBProperties_Click
code window.
Listing 9.10. Coding the cmdDBProperties_Click
event.
Private Sub cmdDBProperties_Click()
`
` show all database properties
`
On Error GoTo LocalErr
`
Dim ws As Workspace
Dim db As Database
Dim objItem As Property
`
Dim strDBName As String
Dim strMsg As String
`
` use db created earlier
strDBName = App.Path & "\CreateDBOne.mdb"
`
` open db in default ws
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` enumerate all the properties of the db
strMsg = ""
For Each objItem In db.Properties
strMsg = strMsg & objItem.Name
strMsg = strMsg & " = "
strMsg = strMsg & objItem.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(objItem.Type) & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "DBProperties"
strMsg = ""
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
In Listing 9.10, you opened an existing Microsoft Jet database in the default workspace
(but did not explicitly declare a session). Then you enumerated the properties of
the Database object. Save and run the project. Click the DBProperties button and
compare your screen to the one in Figure 9.9.
Figure
9.9. The results of enumerating Database
object properties.
Table 9.3 lists the Database object properties and their meanings.
Table 9.3. Database object properties.
Property |
Type/Value |
Meaning/Use |
Name |
String |
The name of the physical database file or the name of the ODBC data source. |
Connect |
String |
If the data source is not a Microsoft Jet database, this property contains additional
information needed to connect to the data using Microsoft Jet. |
Transactions |
True/False |
If set to True, this data source supports the use of the BeginTrans,
CommitTrans, and Rollback methods. |
Updatable |
True/False |
If set to True, Visual Basic can provide updates to this data source. If
set to False, this is a read-only data source. |
Collating Order |
Numeric |
This value controls the order in which Microsoft Jet sorts or indexes the records.
It is set by the locale parameter of the CreateDatabase method. |
Query Time Out |
Numeric (seconds) |
This is the amount of time Microsoft Jet waits before reporting an error while waiting
for the results of a query. |
Version |
String |
Indicates the Microsoft Jet version used to create the database. |
Records Affected |
Numeric |
Shows the number of records affected by the last database operation on this file. |
ReplicaID |
Numeric |
This is the unique ID number of this copy of the replicated database. This is set
when you initiate replication services (see Day 20, "Database Replication"). |
ReplicaMaster |
Numeric |
This is the unique ID value that identifies the Replica Master for this database
(see Day 20). |
Connection |
Object |
This is a reference to the ODBCDirect object that can be used to access this database.
See the section later in this chapter on ODBCDirect data-access objects. |
Let's modify the routine to open a non-Microsoft Jet database in order to compare
the differences in the property values between Microsoft Jet and non-Microsoft Jet
databases. Change the code to match the following example and run the program again
to review the results:
`
` use db created earlier
`strDBName = App.Path & "\CreateDBOne.mdb"
strDBName = App.Path
`
` open db in default ws
Set ws = DBEngine.Workspaces(0)
`Set db = ws.OpenDatabase(strDBName)
Set db = ws.OpenDatabase(strDBName, False, False, "Text;")
`
You can see from this code snippet that the database name has been set to just
the application path and that the OpenDatabase method has been altered to open the
directory folder as if it were a Text database. Make the changes to your program,
save it, and run it. When you click the DBProperties button this time, you see different
property values.
TIP: This last coding example points out a very
important fact about the Microsoft Jet database engine. While the Microsoft Jet engine
treats the Microsoft Access database as a single file with many tables inside that
file, the Microsoft Jet engine treats all other ISAM-type databases quite differently.
To Microsoft Jet, the directory folder is the database and the ISAM files are the
data tables. This is why it is a good idea to keep all ISAM-type data files in the
same directory folder.
The Methods of the Database Object The Database object has 11 methods, but we
won't cover all of them here. Table 9.4 shows the Database object methods grouped
in a logical fashion.
Table 9.4. The Database object methods.
Group |
Methods |
Replication |
MakeReplica, PopulatePartial, Synchronize |
Security |
NewPassword |
Child Objects |
CreateQueryDef, CreateTableDef, CreateRelation |
Database Objects |
OpenRecordset, Execute, CreateProperty, Close |
You'll learn about the Security methods in Day 20 and the NewPassword method
is covered in Day 21. The Child Object methods are covered later in this chapter.
That leaves the OpenRecordset, Execute, CreateProperty,
and Close methods for review here. The OpenRecordset Method of the Database
Object You use the OpenRecordset method to access data in existing tables
in the database. You can use OpenRecordset to create Dynaset, Snapshot,
or Table data objects.
The format of the OpenRecordset method is as follows:
Set Variable = Database.OPENRECORDSET(Source, Type, options)
In this syntax, Database is the name of the database that will be used
to create the Recordset. Type indicates whether the Recordset created is
a Table (dbOpenTable), a Dynaset (dbOpenDynaset), or a Snapshot (dbOpenSnapshot).
A Table type is created if you don't specify a type. You can also add options for
security and record viewing. See Visual Basic online help for a complete description
of these options.
Add a new command button to the project. Set its Name property to cmdOpenRS and
its Caption property to. Add the code in Listing 9.11 in the cmdOpenRS_Click
code window.
Listing 9.11. Coding the cmdRecordset_Click
event.
Private Sub cmdOpenRS_Click()
`
` open record sets
`
On Error Resume Next
`
Dim ws As Workspace
Dim db As Database
Dim rsTable As Recordset
Dim rsDynaset As Recordset
Dim rsSnapshot As Recordset
Dim rsTemp As Recordset
`
Dim strDBName As String
Dim strRSTable As String
Dim strRSDynaset As String
Dim strRSSnapshot As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\..\..\data\books5.mdb"
strRSTable = "Buyers"
strRSDynaset = "Publishers"
strRSSnapshot = "Authors"
`
` create ws and open db
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` create rs objects
Set rsTable = db.OpenRecordset(strRSTable, dbOpenTable)
Set rsDynaset = db.OpenRecordset(strRSDynaset, dbOpenDynaset)
Set rsSnapshot = db.OpenRecordset(strRSSnapshot, dbOpenSnapshot)
`
` enumerate recordsets in collection
strMsg = ""
For Each rsTemp In db.Recordsets
strMsg = strMsg & rsTemp.Name & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "OpenRS"
`
End Sub
The code in Listing 9.11 creates three Recordsets, one of each type, and then displays
the list of open Recordsets on the form. Save and run the form. Compare your results
with those in Figure 9.10.
Figure
9.10. The results of the OpenRecordset
method.
NOTE: The Recordset created with this method
is a very extensive object itself. You'll learn more about the Recordset object's
properties and methods later in this chapter.
Using the Execute Method You can use the Execute method on a database
to perform SQL action queries. The Execute method updates the RecordsAffected
property of the Database object with the total number of records found or updated
by the SQL statement.
NOTE: An action query is an SQL statement that
performs an action on a database (add, edit, or delete records; create or remove
data tables; and so on). Action SQL queries are covered in detail on Day 13, "Creating
Databases with SQL."
Add a new command button to your project. Set its Name property to cmdExecute
and its Caption property to Execute. Add the code in Listing 9.12 to the cmdExecute_Click
event.
Listing 9.12. Coding the cmdExecute_Click
event.
Private Sub cmdExecute_Click()
`
` execute an SQL statement
`
Dim ws As Workspace
Dim db As Database
`
Dim strDBName As String
Dim strSQL As String
Dim lngRecords As Long
`
` init vars
strDBName = App.Path & "\..\..\data\books5.mdb"
strSQL = "DELETE FROM NewAuthors WHERE AUID<10"
lngRecords = 0
`
` open db in default ws
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` perform SQL & get results
db.Execute strSQL, dbFailOnError
lngRecords = db.RecordsAffected
`
` show results
MsgBox CStr(lngRecords), vbInformation, "Deleted Records"
`
` clean up
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing
`
End Sub
The code in Listing 9.12 opens a database and performs an SQL action query that deletes
records from a table. The routine displays the RecordsAffected property to show you
how many records were deleted, and then it closes the database.
Save and run the project. Click Execute and compare your on-screen results with
the screen in Figure 9.11.
Figure
9.11. The results of the Execute
method.
Using the CreateProperty Method Visual Basic lets you create user-defined properties
(UDPs) for most data-access objects. These UDPs get stored with the database and
can be read and updated by your Visual Basic program. In this example, you use the
CreateProperty method to add a UDP to a database.
WARNING: The capability to create and store UDPs
is only available when you use the Microsoft Jet version 3.0 or later database format.
If you are not using Microsoft Jet 3.0 or later, you can't complete the example in
this exercise.
Add a command button to the project. Set its Name property to cmdMakeUDP and its
Caption property to MakeUDP. Add the code in Listing 9.13 to the cmdMakeUDP_Click
window.
Listing 9.13. Coding the cmdMakeUDP_Click
event.
Private Sub cmdMakeUDP_Click()
`
` add user-defined properties
`
On Error Resume Next
`
Dim ws As Workspace
Dim db As Database
Dim pr As Property
Dim prTemp As Property
`
Dim strDBName As String
Dim strUDPName As String
Dim intUDPType As Integer
Dim varUDPValue As Variant
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\CreateDBOne.mdb"
`
` open ws and db
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` add first UDP
strUDPName = "DBAdmin"
intUDPType = dbText
varUDPValue = "D.B. Guru"
`
db.Properties.Delete strUDPName
Set pr = db.CreateProperty(strUDPName, intUDPType, varUDPValue)
db.Properties.Append pr
`
` add second UDP
strUDPName = "Programmer"
intUDPType = dbText
varUDPValue = "V.B. Coder"
`
db.Properties.Delete strUDPName
Set pr = db.CreateProperty(strUDPName)
pr.Type = intUDPType
pr.Value = varUDPValue
db.Properties.Append pr
`
` now show results
For Each prTemp In db.Properties
strMsg = strMsg & prTemp.Name
strMsg = strMsg & " = "
strMsg = strMsg & prTemp.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(prTemp.Type) & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "MakeUDP"
`
` cleanup
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing
`
End Sub
The routine in Listing 9.13 adds two user-defined properties to the database. Notice
that you attempt to delete the properties first. That way you can run this example
several times without getting an error. Notice that you also used two different code
structures to create the properties. Either one is correct.
Save and run the project. When you click the MakeUDP button, you should see a
screen similar to Figure 9.12.
Figure
9.12. The results of the CreateProperty
method.
The TableDef Data Object
The TableDef data object contains all the information needed to define a Base
table object in the Database. You can access Base table objects using the OpenRecordset
method. You use TableDef objects to create and maintain Base tables. TableDef objects
have 3 collections, 5 methods, and 10 properties. The TableDef Collections The TableDef
object has three collections:
- Fields is the collection that contains all the information about the database
fields defined for the TableDef object. This is the default object.
- Indexes is the collection that contains all the information about the database
indexes defined for the TableDef object.
- Properties is the collection that contains all the information about the current
TableDef object.
Details of the Field and Index objects are covered later in this chapter. The
CreateTableDef Method and the TableDef Properties The TableDef properties are set
when the table is created. The values of the properties differ depending on whether
the TableDef object is a native Microsoft Jet object or an attached object. Listing
9.14 shows the properties of a native Microsoft Jet TableDef object.
Add another button to the project. Set its Name property to cmdTableDef and its
Caption property to TableDef. Add the code in Listing 9.14 to the cmdTableDef_Click
event.
Listing 9.14. Adding the TableDef
button.
Private Sub cmdTableDef_Click()
`
` show tabledef properties
`
On Error GoTo LocalErr
`
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim pr As Property
`
Dim strDBName As String
Dim strTDName As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\..\..\data\books5.mdb"
strTDName = "NewTable"
`
` open ws and db
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` now enumerate the empty table defs
strMsg = ""
For Each td In db.TableDefs
For Each pr In td.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type) & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "TableDefs"
strMsg = ""
`
Next
`
db.Close
ws.Close
Set pr = Nothing
Set td = Nothing
Set db = Nothing
Set ws = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
The code in Listing 9.14 opens a database and then "walks through" all
the table definitions in the database, listing the properties of each table. Save
and run the project. Click the TableDef button and compare your screen with the one
in Figure 9.13.
Figure
9.13. Viewing the TableDef properties.
NOTE: You also see several internal data tables
in this listing. The tables that start with "MSYS" are used by Microsoft
Jet to keep track of indexes, relationships, table definitions, and so on. Do not
attempt to read, delete, or modify these tables. Doing so can permanently damage
your database.
The actual properties you see on your screen my be different. There are many properties
of the TableDef object. Most of them are easy to understand. You can search the Visual
Basic online documentation for detailed listings on each of the properties.
NOTE: You may see one or more properties in your
TableDefs that are not documented in the Visual Basic online documents. This is because
the Microsoft Jet DAO language allows programmers to invent and store their own custom
properties. You may be looking at properties invented by some other application (Microsoft
Access, MS Project, custom applications, and so on).
The TableDef Methods Along with the CreateTable method of the database,
there are five methods that you can apply to the TableDef object:
- OpenRecordset enables you to open a Table, Dynaset, or Snapshot Recordset
from the TableDef object.
- RefreshLink updates and refreshes any attached table links for the TableDef
object.
- CreateProperty enables you to create and store a user-defined property.
See the UDP example under the Database object elsewhere in this chapter.
- CreateIndex enables you to add an index to the TableDef object. This
method is covered in "The Index Data Object" section later in this chapter.
- CreateField enables you to add a new field to an existing TableDef object.
You learn more about this method in "The Field Data Object" section.
Creating a New Table in the Database The code in Listing 9.15 enables you to create
a very simple database and table. Add another command button to the form. Set its
Name property to cmdCreateTable and its Caption property to &CreateTable. Add
the code in Listing 9.15 to the cmdCreateTable_Click event.
Listing 9.15. Coding the cmdCreateTable_Click
event.
Private Sub cmdCreateTable_Click()
`
` create a new table in a database
`
On Error Resume Next
`
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim fl As Field
Dim pr As Property
`
Dim strDBName As String
Dim strTDName As String
Dim strFLName As String
Dim intFLType As Integer
Dim strMsg As String
`
` init values
strDBName = App.Path & "\NewDB.mdb"
strTDName = "NewTable"
strFLName = "NewField"
intFLType = dbText
`
` erase db if it's there
Kill strDBName
`
` open ws and create db
Set ws = DBEngine.Workspaces(0)
Set db = ws.CreateDatabase(strDBName, dbLangGeneral, dbVersion30)
`
` create a new table
Set td = db.CreateTableDef(strTDName)
`
` create a new field in table
Set fl = td.CreateField(strFLName, intFLType)
`
` add new objects to collections
td.Fields.Append fl
db.TableDefs.Append td
`
` now show new table properties
On Error GoTo LocalErr
strMsg = ""
For Each pr In td.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type) & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "CreateTable"
`
` clean up
db.Close
ws.Close
Set pr = Nothing
Set td = Nothing
Set db = Nothing
Set ws = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
The code in Listing 9.15 creates a new database (erasing any old one first), creates
a new table object, creates a single field object for the table, and then appends
the new objects to their respective collections. Finally, the properties of the new
table are displayed. Save and run the project. Check your results against Figure
9.14.
Figure
9.14. The results of adding a new table.
Modifying and Deleting Existing Tables You can add new fields or delete existing
fields by using the Append or Delete methods on the TableDef object.
Add a command button with the Name property cmdModifyTable and a Caption property
of Modify Table. Add the code in Listing 9.16 to the cmdModifyTable_Click
event.
Listing 9.16. Coding the cmdModifyTable_Click
event.
Private Sub cmdModifyTable_Click()
`
` modify an existing table
`
On Error Resume Next
`
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim fl As Field
`
Dim strDBName As String
Dim strTDName As String
Dim strFLName As String
Dim intFLType As Integer
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\NewDB.mdb"
strTDName = "NewTable"
strFLName = "FollowDate"
intFLType = dbDate
`
` first create table with other subroutine
cmdCreateTable_Click
`
` now open ws & db & td
Set ws = DBEngine.Workspaces(0)
Set db = OpenDatabase(strDBName)
Set td = db.TableDefs(strTDName)
`
` add a new field
Set fl = td.CreateField(strFLName, intFLType)
td.Fields.Append fl
`
` make list of fields
strMsg = "Appended Field:"
For Each fl In td.Fields
strMsg = strMsg & vbTab & fl.Name & vbCrLf
Next
`
` now delete the new field
td.Fields.Delete strFLName
`
` make list again
strMsg = strMsg & "Deleted Field:"
For Each fl In td.Fields
strMsg = strMsg & vbTab & fl.Name & vbCrLf
Next
`
` show list
MsgBox strMsg, vbInformation, "Deleted Field"
`
` clean up
db.Close
ws.Close
Set fl = Nothing
Set td = Nothing
Set db = Nothing
Set ws = Nothing
`
End Sub
In Listing 9.16, you call the previous code section to create the table again. Then
you add a new field using the Append method, and delete that field using
the Delete method. Save and run the project, and check your final results
against Figure 9.15.
Figure
9.15. The results of adding and deleting
fields.
Attaching External Data You can attach an existing external, non-Microsoft Jet database
table to an existing Microsoft Jet-format database. Attaching tables in this way
gives you access to the external data using the standard Visual Basic data-access
object interface. It also enables you to mix Microsoft Jet and non-Microsoft Jet
data in the same database, which is great for handling queries that combine data
from both sources.
NOTE: You can create and store queries on the
attached external data, too. Queries are covered later in this chapter.
You cannot open a table-type Recordset on an attached table. You must use the
Dynaset or Snapshot objects for accessing attached tables. Even though you must use
Dynaset data objects, attached tables respond faster than external data links.
Let's illustrate attachments by adding another command button to the form. Set
its Name property to cmdAttachTable and its Caption property to Attach Table. Add
the code in Listing 9.17 to the cmdAttachTable_Click event.
Listing 9.17. Coding the cmdAttachTable_Click
event.
Private Sub cmdAttachTable_Click()
`
` attach a non-jet table to database
`
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
`
Dim strDBName As String
Dim strATName As String
Dim strATDBType As String
Dim strATDBName As String
Dim strATSrcName As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\NewDB.mdb"
strATName = "FoxProAttachment"
strATDBName = App.Path
strATDBType = "FoxPro 2.5;"
strATSrcName = "Customer.dbf"
`
` call routine to create table
cmdCreateTable_Click
`
` now open ws & db
Set ws = DBEngine.Workspaces(0)
Set db = OpenDatabase(strDBName)
`
` add a new tabldef
Set td = db.CreateTableDef(strATName)
`
` define the new def as an attachment
td.Connect = strATDBType & "DATABASE=" & strATDBName
td.SourceTableName = strATSrcName
`
` append attachment to collection
db.TableDefs.Append td
`
` show list of tables
strMsg = ""
For Each td In db.TableDefs
strMsg = strMsg & td.Name & vbCrLf
Next
MsgBox strMsg, vbInformation, "AttachTable"
`
db.Close
ws.Close
Set td = Nothing
Set db = Nothing
Set ws = Nothing
`
End Sub
The code in Listing 9.17 calls the routine that creates your test database and then
opens the created database and creates a new table definition. This time, instead
of creating field definitions to append to the new table definition, you create an
attachment to another external database. Attachments always have two parts: the Connect
string and the SourceTableName.
The Connect string contains all information needed to connect to the external
database. For desktop (ISAM-type) databases, you need to supply the driver name (dBASE
III, Paradox 3.x, and so on) and the device/path where the data file is located.
For back-end database servers, you might need to supply additional parameters.
The SourceTableName contains the name of the data table you want to attach to
the Microsoft Jet database. For desktop databases, this is the database filename
in the device location (NAMES.DBF, CUSTOMERS.DBF, and so on). For
back-end database servers, this is the data table name that already exists in the
server database.
Save and run the project. When you click the Attach Table button, you see a few
message dialogs flash by. The final dialog lists all the tables in the database (see
Figure 9.16).
Figure
9.16. Viewing the attached tables dialog.
Notice that the FoxProAttachment table now appears. You can now manipulate this table
like any native Microsoft Jet data table object.
NOTE: You also see several internal data tables
in this listing. The tables that start with "MSYS" are used by Microsoft
Jet to keep track of indexes, relationships, table definitions, and so on. Do not
attempt to read, delete, or modify these tables. Doing so can permanently damage
your database.
The Field Data Object
The Field object contains all the information about the data table field. In the
previous section on TableDef objects, you created and deleted fields. You can also
access the Field object to get information on field properties. The Field object
has only one collection--the Properties collection. There are 17 properties and 4
methods. The Field Properties There are 17 Field properties. You can use these properties
to determine the size and type of a field, and whether it is a native Microsoft Jet
field object or an attached field from an external database. In version 3.0 Microsoft
Jet formats, you can set the default value for the field, and define and enforce
field-level validation rules.
Listing 9.18 shows all the properties for selected fields. Add another button
to the form. Set its Name property to cmdFields and its Caption property to &Field.
Add the code in Listing 9.18 to the cmdFields_Click event window.
Listing 9.18. Coding the cmdFields_Click
event.
Private Sub cmdFields_Click()
`
` show all the field properties of a table field
`
On Error GoTo LocalErr
`
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim fl As Field
Dim pr As Property
`
Dim strDBName As String
Dim strTDName As String
Dim strFLName As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\NewDB.mdb"
strTDName = "NewTable"
strFLName = "NewField"
`
` build new database & table
cmdCreateTable_Click
`
` now open ws and db and td
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` open table and get a field
Set td = db.TableDefs(strTDName)
Set fl = td.Fields(strFLName)
`
` show properties of the field
strMsg = ""
For Each pr In fl.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type) & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "Fields"
`
` cleanup
db.Close
ws.Close
Set pr = Nothing
Set fl = Nothing
Set td = Nothing
Set db = Nothing
Set ws = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
The code in Listing 9.18 creates the database and then opens a single table to access
one of the fields. The rest of the code loops through the collection to list the
properties for the selected field. The results are displayed in the message box.
Check your screen against the one in Figure 9.17.
Figure
9.17. The Field properties in the Debug
window.
The list of field properties is quite extensive. You are encouraged to check out
the Visual Basic documentation for details on some of the less obvious properties.
Also remember that you may be seeing properties added by other DAO applications and
that there may be no documentation for these custom properties.
The Index Data Object
The Index object is used to contain information on defined indexes for the associated
table. Indexes can only be built for native Microsoft Jet data tables (no attached
tables allowed). You can use indexes for two purposes: to enforce data integrity
rules and to speed access for single-record lookups.
Indexes are always associated with an existing data table. You must create a native
Microsoft Jet data table before you can create an index. Listing 9.19 shows how to
create an index through Visual Basic code and view its properties.
Add a command button to the form with a Name property of cmdIndex and a Caption
property of &Index. Add the code in Listing 9.19 to the cmdIndex_Click
event.
Listing 9.19. Coding the cmdIndex_Click
event.
Private Sub cmdIndex_Click()
`
` create a new index and display its properties
`
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim ix As Index
Dim fl As Field
Dim pr As Property
`
Dim strDBName As String
Dim strTDName As String
Dim strFLName As String
Dim strIXName As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\NewDB.mdb"
strTDName = "NewTable"
strFLName = "NewField"
strIXName = "PKNewTable"
`
` create db and table
cmdCreateTable_Click
`
` open ws, db and table
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
Set td = db.TableDefs(strTDName)
`
` now create an index
Set ix = td.CreateIndex(strIXName)
Set fl = ix.CreateField(strFLName)
ix.Required = True
ix.Primary = True
`
` add field to index's fields collection
ix.Fields.Append fl
`
` add index to table's index collection
td.Indexes.Append ix
`
` now show index properties
strMsg = ""
For Each pr In ix.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type)
strMsg = strMsg & "}"
strMsg = strMsg & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "Index"
`
` clean up
db.Close
ws.Close
Set pr = Nothing
Set fl = Nothing
Set ix = Nothing
Set td = Nothing
Set db = Nothing
Set ws = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
The code in Listing 9.19 seems pretty familiar, right? After creating a database
and adding a table (handled by cmdCreateTable), you build and add the index.
Notice that you first name the index, and then create a Field object for the target
index. By adding the Field object and setting some other properties, you have completed
the index definition. Finally, you append the index to the collection of indexes
for the specific table.
TIP: Although you append indexes to a specific
table object, the index name is global for the entire database. You cannot create
an Index object called Index1 for Table1 and then create another Index1 for Table2.
You must have unique index names.
Save and run the project. Click the Index button and check your results against
those in Figure 9.18.
Figure
9.18. The results of adding an index.
The QueryDef Data Object
The QueryDef object contains information about a stored SQL query. SQL queries
can be used as record sources for the Visual Basic data control or as the first parameter
in the Recordset object. QueryDef objects run faster than inline SQL queries, because
Visual Basic must go through a processing step before executing an SQL query. Stored
queries (QueryDef objects) are stored in their processed format. Using QueryDef objects
means there is one less processing step to go through before you see your data.
The example in Listing 9.20 creates a simple SELECT SQL query and stores
it for later use. After creating the query, you apply it as a record source when
creating a Recordset object. Finally, you enumerate the QueryDef properties. Add
another button with its Name property set to cmdQuery and its Caption property set
to &Query. Add the code in Listing 9.20 to the cmdQuery_Click code window.
Listing 9.20. Coding the cmdQuery_Click
event.
Private Sub cmdQueryDef_Click()
`
` create a stored query
`
On Error Resume Next
`
Dim ws As Workspace
Dim db As Database
Dim qd As QueryDef
Dim pr As Property
`
Dim strDBName As String
Dim strQDName As String
Dim strQDSQL As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\NewDB.mdb"
strQDName = "qryNewQuery"
strQDSQL = "SELECT * FROM NewTable WHERE NewField<>NULL"
`
` create db & table
cmdCreateTable_Click
`
` open ws and db
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` create a new query
Set qd = db.CreateQueryDef(strQDName)
qd.SQL = strQDSQL
`
` show properties of the querydef
strMsg = ""
For Each pr In qd.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type)
strMsg = strMsg & "}" & vbCrLf
Next
`
MsgBox strMsg, vbInformation, "QueryDef"
`
db.Close
ws.Close
Set pr = Nothing
Set qd = Nothing
Set db = Nothing
Set ws = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
Save and run the project. Check your final screen against the one in Figure 9.19.
Figure
9.19. The results of creating a QueryDef
object.
The code in Listing 9.20 exposes one very important aspect of creating QueryDef objects
that you might not have noticed. There is no Append method to add the QueryDef
to the QueryDefs collection. It is added automatically. As soon as you define the
QueryDef with a name property, you have added it to the collection.
TIP: You can also create a QueryDef that is not
added to the QueryDefs collection. Simply execute the CreateQueryDef method
with an empty name:
set qd = db.CreateQueryDef("")
You can then fill the SQL property of the query and execute it to get the resulting
dataset. When you close the query, it is destroyed instead of being saved to the
QueryDefs collection. This is especially handy when you want to execute dynamic SQL
statements, but do not want to create and delete QueryDefs at runtime.
Getting Results from QueryDefs There are two basic methods for working with QueryDefs--Execute
and OpenRecordset. The Execute method is used to perform SQL action
queries. Action queries are SQL statements that perform some action on the data table.
Examples of action queries are SQL statements that
- Add, modify, or remove table records
- Add indexes or relationship rules
- Add, modify, or remove tables from the database
The other method used when working with QueryDefs is the OpenRecordset
method. This method is used to retrieve data from the tables into a programming object
for manipulation.
Add another button to the form. Set its Name property to cmdRunningQDs and its
Caption to Running QDs. Now enter the code from Listing 9.21 into the cmdRunningQDs_Click
event
Listing 9.21. Coding the cmdRunningQDs_Click
event.
Private Sub cmdRunningQDs_Click()
`
` running stored queries
`
On Error GoTo LocalErr
`
Dim ws As Workspace
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Dim pr As Property
`
Dim strDBName As String
Dim strQDName As String
Dim strQDSQLInsert As String
Dim strQDSQLSelect As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\NewDB.mdb"
strQDName = "qryNewQuery"
strQDSQLInsert = "INSERT INTO NewTable VALUES(`Mike')"
strQDSQLSelect = "SELECT * FROM NewTable"
`
`
` create db & table
cmdCreateTable_Click
`
` open ws & db
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)
`
` create temp query and execute
Set qd = db.CreateQueryDef("")
qd.SQL = strQDSQLInsert
qd.Execute
`
` view query properties
strMsg = ""
For Each pr In qd.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type)
strMsg = strMsg & "}" & vbCrLf
Next
MsgBox strMsg, vbInformation, "TempQueryDef"
`
` create stored query and get results
Set qd = db.CreateQueryDef(strQDName)
qd.SQL = strQDSQLSelect
Set rs = qd.OpenRecordset(dbOpenDynaset)
`
` view query properties
strMsg = ""
For Each pr In qd.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type)
strMsg = strMsg & "}" & vbCrLf
Next
MsgBox strMsg, vbInformation, "SavedQueryDef"
`
rs.Close
db.Close
ws.Close
Set pr = Nothing
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
Set ws = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
Notice that this code creates and executes two QueryDefs. The first query is an action
query--it uses the Execute method. Note also that this first query was never
assigned a value for the Name property. It is treated as a temporary query by Microsoft
Jet, and it is not appended to the QueryDefs collection.
The second QueryDef selects records from the data table. Because this is not an
action query, the OpenRecordset method is used to perform this query. Also,
because this query was given a value for the Name property, it is appended automatically
to the QueryDefs collection and saved with the database.
Now save and run this code. You see the now familiar CreateTable dialog followed
by two more dialogs. The first is the property list for the temporary query. Note
that the Name property has been filled by Microsoft Jet with #Temporary QueryDef#
and that the RecordsAffected property has been set to 1 (see Figure 9.20).
Figure
9.20. Viewing the property list for a
temporary QueryDef.
The next dialog is the property list for the saved QueryDef. This query pulls data
from the table into a programming object. Note that the DateCreated and LastUpdated
properties are set to valid values (see Figure 9.21).
Figure
9.21. Viewing the property list for a
saved QueryDef.
The ODBCDirect Connection Data
Object
The Connection object is new to Visual Basic 5.0. This data object is part of
the ODBCDirect data access model. This model allows programmers to access ODBC data
sources without first defining a Microsoft Jet data object. The ability to open a
direct connection to ODBC instead of first opening a Microsoft Jet session provides
added flexibility to your programs.
The process of creating and using a Connection object begins at the workspace
level. When you create a new workspace, you must explicitly mark it as an ODBCDirect
workspace. You can then perform an OpenConnection method to open a new connection
to an ODBC data source. Once the connection has been established, you can use the
OpenRecordset, Execute, CreateQueryDef, and Close
methods with which you are already familiar.
Add a new button to the form and set its Name property to cmdConnection and its
Caption to Connection. Now enter the code from Listing 9.22 into the cmdConnection_Click
event.
Listing 9.22. Coding the cmdConnection_Click
event.
Private Sub cmdConnection_Click()
`
` show use of ODBCDirect Connection object
`
Dim ws As Workspace
Dim co As Connection
`
Dim strWSName As String
Dim strCOName As String
Dim strDSN As String
Dim strDBQ As String
Dim strCOConnect As String
Dim strMsg As String
`
` init vars
strWSName = "wsODBCDirect"
strCOName = "TDPConnection"
strDSN = "DSN=MS Access 7.0 Database;"
strDBQ = "DBQ=C:\TYSDBVB5\Source\Data\Books5.mdb"
strCOConnect = "ODBC;" & strDSN & strDBQ
`
` create ws for ODBCDirect
Set ws = DBEngine.CreateWorkspace(strWSName, "admin", "", dbUseODBC)
`
` open a connection
Set co = ws.OpenConnection(strCOName, dbDriverNoPrompt, False, strCOConnect)
`
` show properties of connection object
` connection objects *do not* have a properties collection!
strMsg = strMsg & "Name = " & co.Name & vbCrLf
strMsg = strMsg & "Connect = " & co.Connect & vbCrLf
strMsg = strMsg & "Database = " & co.Database.Name & vbCrLf
strMsg = strMsg & "QueryTimeOut = " & co.QueryTimeout & vbCrLf
strMsg = strMsg & "RecordsAffected = " & co.RecordsAffected & vbCrLf
strMsg = strMsg & "StillExecuting = " & co.StillExecuting & vbCrLf
strMsg = strMsg & "Transactions = " & co.Transactions & vbCrLf
strMsg = strMsg & "Updatable = " & co.Updatable & vbCrLf
`
MsgBox strMsg, vbInformation, "Connection"
`
` clean up
co.Close
ws.Close
Set co = Nothing
Set ws = Nothing
`
End Sub
In the code in Listing 9.22, you first create a workspace object with the dbUseODBC
parameter added. This creates the ODBCDirect-type workspace. Next, the code performs
the Open Connection method on the workspace using the Connect string
built-in program variables. This Connect string uses the default Microsoft Access
driver that ships with Microsoft Office 95 or later. Notice that you are actually
pointing to the BOOKS5.MDB database used throughout this book. Another key
point to notice is that you are now using Visual Basic DAO to open an Access database.
This is not possible if you are using the standard Microsoft Jet ODBC connection.
TIP: You can now use ODBCDirect to open any ISAM-type
database formats, including dBASE, FoxPro, Paradox, and so on, along with Microsoft
Access and the back-end RDBMS formats such as SQL Server and Oracle.
Finally, after successfully opening the connection to the database, the Connection
object properties are displayed. Unfortunately, the Connection object does not support
the use of the Properties collection. This makes coding the property display a bit
more labor-intensive than coding the other DAO objects.
Save and run the project. When you press the Connection button, you see the Connection
property list appear on your screen (see Figure 9.22).
Figure
9.22. Viewing the Connection object property list.
The Recordset Data Object
By far, the most commonly used objects in Visual Basic programming are the objects
that contain datasets. In the Microsoft Jet object model, this object is the Recordset
object. Recordset objects can be created from the Database object, the Connection
Object, the QueryDef object, and even from another Recordset object. This list of
parent objects speaks to the importance of the Recordset as the primary data object
in the Microsoft Jet DAO.
The property and method list of the Recordset also reflects its versatility and
importance. We have mentioned many of the Recordset's methods in previous chapters.
You'll also use the Recordset methods in the next chapter, "Creating Database
Programs with Visual Basic Code." The property list of the Recordset object
is also quite extensive. Even more important, the exact methods and properties available
for the Recordset depend on whether the Recordset was created within an ODBCDirect
workspace or a Microsoft Jet workspace.
Rather than take up space in the book to list these methods and properties, look
up the "Recordset Object, Recordset Collection Summary" topic in the Visual
Basic 5 help files. This help topic lists every method and property with extensive
notes regarding the differences between ODBCDirect and Microsoft Jet. You can also
use this help topic as a starting point for exploring the details of each method
and property.
However, to illustrate the differences and similarities between ODBCDirect Recordsets
and Microsoft Jet Recordsets, add a new button to the form. Set its Name property
to cmdRecordsets and its caption to Recordsets. Now add the code from Listing 9.23
to the cmdRecordsets_Click event.
Listing 9.23. Coding the cmdRecordsets_Click
event.
Private Sub cmdRecordsets_Click()
`
` demonstrate ODBCDirect and MS Jet Recordsets
`
On Error GoTo LocalErr
`
Dim wsDirect As Workspace
Dim wsJet As Workspace
Dim db As Database
Dim co As Connection
Dim pr As Property
Dim rsDirect As Recordset
Dim rsJet As Recordset
`
Dim strWSDName As String
Dim strWSJName As String
Dim strDBName As String
Dim strCOName As String
Dim strRSDName As String
Dim strRSJName As String
Dim strConnect As String
Dim strMsg As String
`
` init vars
strWSDName = "wsDirect"
strWSJName = "wsJet"
strCOName = "coDirect"
strConnect = "ODBC;DSN=MS Access 7.0 Database;DBQ=C:\ ÂTYSDBVB5\Source\Data\books5.mdb"
strDBName = App.Path & "\..\..\Source\Data\books5.mdb"
strRSDName = "SELECT * FROM Buyers"
strRSJName = "SELECT * FROM Publishers"
`
` establish ODBCDirect connection
Set wsDirect = DBEngine.CreateWorkspace(strWSDName, "admin", "", dbUseODBC)
Set co = wsDirect.OpenConnection(strCOName, dbDriverNoPrompt, False, ÂstrConnect)
Set rsDirect = co.OpenRecordset(strRSDName, dbOpenForwardOnly)
`
` establish MS Jet connection
Set wsJet = DBEngine.CreateWorkspace(strWSJName, "admin", "")
Set db = wsJet.OpenDatabase(strDBName)
Set rsJet = db.OpenRecordset(strRSJName, dbOpenDynaset)
`
` now show results
strMsg = ""
For Each pr In rsDirect.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type)
strMsg = strMsg & "}"
strMsg = strMsg & vbCrLf
Next
MsgBox strMsg, vbInformation, "rsDirect"
`
strMsg = ""
For Each pr In rsJet.Properties
strMsg = strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type)
strMsg = strMsg & "}"
strMsg = strMsg & vbCrLf
MsgBox strMsg
Next
MsgBox strMsg, vbInformation, "rsJet"
`
` cleanup
rsDirect.Close
rsJet.Close
db.Close
co.Close
wsDirect.Close
wsJet.Close
`
Set pr = Nothing
Set rsDirect = Nothing
Set rsJet = Nothing
Set db = Nothing
Set co = Nothing
Set wsDirect = Nothing
Set wsJet = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
When you save and run this routine, you see a long list of Recordset properties for
each of the objects. Note that the lists are different. Even when the property names
are the same, some of the values are different (see Figure 9.23).
The Relation Data Object
The last data-access object covered today is the Relation data object. This object
contains information about established relationships between two tables. Relationships
help enforce database referential integrity. Establishing a relationship involves
selecting the two tables you want to relate, identifying the field you can use to
link the tables together, and defining the type of relationship you want to establish.
Figure
9.23. Viewing Recordset property lists.
NOTE: The details of defining relationships are
covered next week in the chapters on advanced SQL (Days 15 and 16). For now, remember
that you can use the Relation object to create and maintain database relationships
within Visual Basic code.
The final coding example for today is to create a new database, add two tables,
define fields and indexes for those two tables, and then define a relationship object
for the table pair. This example calls on most of the concepts you have learned today.
Add one more button to the project. Set its Name property to cmdRelation and its
Caption property to Re&lation. Add the code in Listing 9.24 to the cmdRelation_Click
event window.
Listing 9.24. Coding the cmdRelation_Click
event.
Private Sub cmdRelations_Click()
`
` demonstrate relationship objects
`
On Error Resume Next
`
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim fl As Field
Dim ix As Index
Dim rl As Relation
Dim pr As Property
`
Dim strDBName As String
Dim strTDLookUp As String
Dim strTDMaster As String
Dim strIXLookUp As String
Dim strIXMaster As String
Dim strRLName As String
Dim strMsg As String
`
` init vars
strDBName = App.Path & "\RelDB.mdb"
strTDLookUp = "ValidUnits"
strTDMaster = "MasterTable"
strIXLookUp = "PKUnits"
strIXMaster = "PKMaster"
strRLName = "relUnitMaster"
`
` erase old db if it's there
Kill strDBName
`
` open ws and create db
Set ws = DBEngine.Workspaces(0)
Set db = ws.CreateDatabase(strDBName, dbLangGeneral, dbVersion30)
`
` now create the lookup list table & fields
Set td = db.CreateTableDef(strTDLookUp)
Set fl = td.CreateField("UnitID", dbText, 10)
td.Fields.Append fl
Set fl = td.CreateField("Description", dbText, 50)
td.Fields.Append fl
`
` now add table to database
db.TableDefs.Append td
`
` index the new table
Set ix = td.CreateIndex(strIXLookUp)
ix.Primary = True
ix.Required = True
Set fl = ix.CreateField("UnitID")
ix.Fields.Append fl
td.Indexes.Append ix
`
` now create master record table
Set td = db.CreateTableDef(strTDMaster)
Set fl = td.CreateField("MasterID", dbText, 20)
td.Fields.Append fl
Set fl = td.CreateField("MasterUnitID", dbText, 10)
td.Fields.Append fl
`
` add index to the master table
Set ix = td.CreateIndex(strIXMaster)
ix.Primary = True
ix.Required = True
Set fl = ix.CreateField("MasterID")
ix.Fields.Append fl
td.Indexes.Append ix
`
` now add defined table
db.TableDefs.Append td
`
` *now* do the relationship!
Set rl = db.CreateRelation(strRLName)
rl.Table = strTDLookUp ` table for lookups
rl.ForeignTable = strTDMaster ` table to verify
Set fl = rl.CreateField("UnitID")
fl.ForeignName = "MasterUnitID"
rl.Fields.Append fl
rl.Attributes = dbRelationUpdateCascade
db.Relations.Append rl
`
` now show relation object
strMsg = "Relation Properties:" & vbCrLf
For Each pr In rl.Properties
strMsg = vbTab & strMsg & pr.Name
strMsg = strMsg & " = "
strMsg = strMsg & pr.Value
strMsg = strMsg & " {"
strMsg = strMsg & ShowType(pr.Type)
strMsg = strMsg & "}"
strMsg = strMsg & vbCrLf
Next
`
strMsg = strMsg & "Relation Fields:" & vbCrLf
For Each fl In rl.Fields
strMsg = vbTab & strMsg & fl.Name & vbCrLf
strMsg = vbTab & strMsg & fl.ForeignName
Next
MsgBox strMsg, vbInformation, "Relation"
`
` cleanup
db.Close
ws.Close
`
Set pr = Nothing
Set fl = Nothing
Set ix = Nothing
Set td = Nothing
Set db = Nothing
Set ws = Nothing
`
Exit Sub
`
LocalErr:
strMsg = strMsg & "<err>"
Resume Next
`
End Sub
The code in Listing 9.24 performs the basic tasks. Create a database and build two
tables with two fields each. Construct primary key indexes for both tables. Then
create the relationship object.
Save and run the project. When you click the Relation command button, the program
creates all the data objects, and then displays the resulting Relation object on
the form. Compare your results to the screen in Figure 9.24.
Figure
9.24. Viewing the results of a Relation
object.
Notice that you added an attribute to make this relationship enforce cascading updates,
which means that any time a value is changed in the lookup table, all the corresponding
values in the foreign table are updated automatically too. You can also set delete
cascades. If the value is deleted from the lookup table, all corresponding records
in the foreign table are deleted.
Summary
In today's lesson, you learned the features and functions of Visual Basic Microsoft
Jet data access objects and ODBCDirect access objects. These objects are used within
Visual Basic code to create and maintain workspaces, databases, tables, fields, indexes,
queries, and relations. You learned the properties, methods, and collections of each
object. You also learned how to use Visual Basic code to inspect the values in the
properties, and how to use the methods to perform basic database operations.
Quiz
- 1. What does the Jet in the Microsoft Jet Database Engine stand for?
2. Describe the difference between a property and a method.
3. What is the top-level data-access object (DAO)?
4. What command would you issue to repair a database? Is this a method or
a property?
5. What is the syntax of the CompactDatabase method?
6. What happens if you don't declare a Workspace when you open a database?
7. What data object types can be created with the OpenRecordset method?
8. What is the difference between the Execute and the ExecuteSQL
methods?
9. Which TableDef method can be used to create a table in an existing database?
What syntax does this method follow?
10. Which data-access object would you use to determine the data type of a
table column?
11. Can you use the Index data object to build an index for a FoxPro 2.5 database?
12. What information does the QueryDef object store?
Exercise
Assume that you are a systems consultant to a large multinational corporation.
You have been assigned the task of building a program in Visual Basic that creates
a database to handle customer information. In this database, you need to track CustomerID,
Name, Address (two lines), City, State/Province, Zip, Phone, and Customer Type.
Start a new project and add a single command button to a form that executes the
code to build this database. Include the following in your code:
- A section that deletes the database if it already exists
- A table for customer information (called Customers) and a table for customer
types (called CustomerTypes)
- Primary keys for both tables
- A relationship between the two tables on the Customer Type field
- A message that signifies that the procedure is complete
When you have completed the entry of this code, display the database in Visdata.
Add information to both tables. Take note of how the referential integrity is enforced
by deleting records from the CustomerTypes table that are used in the Customers table.
  
|