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:
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.
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.
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 is organized into a set of data-access objects. Each of the objects has collections, properties, and methods:
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 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:
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.
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.
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:
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.
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.
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.
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
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 |
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 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:
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.
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.
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 |
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.
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.
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 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:
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.
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. |
`
` 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 |
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.
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.
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.
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 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:
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.
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:
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.
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.
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.
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 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.
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 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.
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 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.
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
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
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 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.
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.
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.
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 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.
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.
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.
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:
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.
