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. These objects include the following:
Throughout this lesson, you will build a single Visual Basic project that illustrates the various data access objects you are learning about today. You can apply the Visual Basic coding techniques you learn today in future Visual Basic database projects.
The JET in Microsoft JET stands for Joint Engine Technology. The idea behind Microsoft JET is that you can use one single interface to access multiple types of data. Microsoft designed Microsoft JET to be able 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 perform data operations on Microsoft Access databases.
Microsoft JET is not a single program; it is a set of routines that work together. The Microsoft JET 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 via the Microsoft JET, as long as some set of translation routines is made available to Microsoft JET.
NOTE: The detailed inner workings of the Microsoft JET 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 to perform database administrative tasks. The Data Access Objects (DAO) 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 to manage your data. It is a much easier tool to use, because many of the administrative function are handled for you. You can always add DAO in your code to work with the Data Control.
The 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 data access objects. You learn the various Microsoft JET data access objects in the order they reside in the hierarchy. As you push deeper into the data access 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 phrase data access objects and data objects. They both refer to the Data Access Object Layer of the Microsoft JET.
If you do not already have Visual Basic up and running, start it now and begin a new project. Make sure that your system can reference the Data Access Object set.
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 Tools | References from the Visual Basic main menu. If you are running the 32—bit version of Visual Basic 4, you should make sure the Version 3 Data Access Object checkbox is turned on. If you are running the 16-bit version of Visual Basic 4, you should make sure that the checkbox for Version 2.5 Data Access Object is turned on. Use Figure 10.1 as a reference.
Reviewing the data access object reference.
The DBEngine data object is the default data object for all access to the database operations under Visual Basic 4. Even if you use the old Visual Basic 3 syntax to open and update database objects, you still use the DBEngine data object because it is invoked by default when Visual Basic 4 begins any database work.
TIP: Even though Visual Basic 4 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 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:
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 that will list (enumerate) all the properties of the DBEngine data access object.
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 10.1.
Listing 10.1. Coding the cmdDBEngine_Click event.
Private Sub cmdDBEngine_Click()
On Error Resume Next
'
Dim oItem As Object
'
For Each oItem In DBEngine.Properties
Me.Print oItem.Name; " | ",
Me.Print oItem.Type; " | ",
Me.Print oItem.VALUE; " | ",
Me.Print oItem.Inherited; " | "
Next
'
End Sub
In Listing 10.1, you first tell Visual Basic to ignore any errors it might receive while enumerating the DBEngine properties. Then you declare a single variable as an object to represent the property you are inspecting. You then use the Visual Basic 4 For...Each loop to list each of the properties of the DBEngine object. Separate each property with the pipe character. Each property has four parameters: its name, its data type, its value, and the inheritance flag.
Save the form as CH1001.FRM and the project as CH1001.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 10.2.
The enumerated DBEngine properties.
You can set the properties of the DBEngine object in your program, too. For example, if you want to set the IniPath property of the DBEngine, you could add a single code line.
DBEngine.IniPath= App.Path + "\ch1001.ini" ' set the default ini path
Add this code line to the project just before the For...Each statement. Save and run the project. You should now see that the IniPath property of the DBEngine has been set. The DefaultUser and DefaultPassword properties are covered when you learn about the Workspace data access object.
Five Visual Basic methods are associated with the DBEngine data access object:
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 cmdRepair and its Caption property to &Repair. Add the code in Listing 10.2 in the cmdRepair_Click code window.
Listing 10.2. Coding the cmdRepair_Click event.
Private Sub cmdRepair_Click()
'
' attempt to fix a currupted database
'
Dim cDBName As String
'
cDBName = InputBox("Enter Database To Repair:", "RepairDatabase Example")
If Len(Trim(cDBName)) <> 0 Then
DBEngine.RepairDatabase cDBName
MsgBox cDBName + " Repaired"
End If
End Sub
The code in Listing 10.2 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, enter CH1001.MDB in the input dialog (see Figure 10.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 might want to make a backup copy of your database files before you execute the RepairDatabase 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 CH1001.VBP project. Set its Name property to cmdCompact and its Caption property to &Compact. Enter the code in Listing 10.3 into the cmdCompact_Click event window. This code will compact any Microsoft JET database.
Listing 10.3. Coding the cmdCompact_Click event.
Private Sub cmdCompact_Click()
Dim cOldDB As String
Dim cNewDB As String
Dim nEncrypt As Integer
Dim cVersion As String
Dim nVersion As Integer
Dim cHeader As String
'
' start of routine
cmdCompactClickStart:
' init vars
cOldDB = ""
cNewDB = ""
cVersion = ""
nEncrypt = False
cHeader = "CompactDatabase Example"
'
' get file to convert/compact
cOldDB = InputBox("Enter File to Compact/Convert", cHeader)
If Len(Trim(cOldDB)) = 0 Then
GoTo cmdCompactClickEnd
End If
'
' get target filename
cNewDB = InputBox("Enter Target File Name", cHeader)
If Len(Trim(cNewDB)) = 0 Then
GoTo cmdCompactClickStart
End If
'
' get target version
cmdCompactClickVersion:
nVersion = 0
cVersion = InputBox("Enter Target Version Number" + Chr(13) +
Chr(10) + "1.0, 1.1, 2.5, or 3.0", cHeader)
Select Case Trim(cVersion)
Case Is = ""
GoTo cmdCompactClickStart
Case Is = "1.0"
nVersion = dbVersion10
Case Is = "1.1"
nVersion = dbVersion11
Case Is = "2.5"
nVersion = dbVersion20
' Add the next two lines only if you are working in 32-bit mode
Case Is = "3.0"
nVersion = dbVersion30
Case Else
MsgBox "Invalid Version!", vbCritical, "Input Error"
GoTo cmdCompactClickVersion
End Select
'
' ask about encryption
nEncrypt = MsgBox("Encrypt Database?", vbInformation + vbYesNo, cHeader)
If nEncrypt = vbYes Then
nEncrypt = dbEncrypt
Else
nEncrypt = dbDecrypt
End If
'
' now try to do it!
DBEngine.CompactDatabase cOldDB, cNewDB, dbLangGeneral, nVersion + nEncrypt
GoTo cmdCompactClickEnd
'
cmdCompactClickEnd:
'
End Sub
The code in Listing 10.3 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 CH1001.MDB. You then must enter a database to compact to. Enter the same path, but enter the name as CH1001X.MDB. Next, enter the version. Users of 16-bit systems should enter 2.5 (3.0 is available in 32-bit mode only). Answer Yes when you are prompted with the encryption question. The new database is now compacted and saved as CH1001X.MDB.
NOTE: A good program would present the user with the File Open dialog box to locate the files. This example uses the InputBox to save time and simplify the code example.
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 instead of 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 cmdODBC and its Caption property to &ODBC. Add the following code in the cmdODBC_Click code window.
Private Sub cmdODBC_Click()
On Error Resume Next
'
' simple call to register an ODBC data source
DBEngine.RegisterDatabase "MyODBC", "SQL Server", False, ""
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 as follows:
Save and run the project. When you click the ODBC 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 10.4 as an example. For now, select Cancel and don't register.
Registering an ODBC data source.
Completing an ODBC registration inserts data into the ODBC.INI file on 16-bit systems and adds information to the Windows Registry file on 32-bit systems. You can add features to the cmdODBC_Click example above 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 your 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 unexpected errors and possible loss of data. Be sure to test your RegisterDatabase routines completely before using them on live data.
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 the Microsoft JET. You can 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 4 will create 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 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 4. Security features are covered on Day 20.
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 three of the 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 4 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 cmdWorkspace and its Caption property to &Workspace. Enter the code in Listing 10.4 into the cmdWorkspace_Click code window.
Listing 10.4. Coding the cmdWorkspace_Click event.
Private Sub cmdWorkspace_Click()
On Error Resume Next
'
Dim oItem As Object
Dim x As Integer
'
' show properties
Me.Cls
For x = 0 To DBEngine.Workspaces.Count - 1
For Each oItem In DBEngine.Workspaces(x).Properties
Me.Print oItem.Name; " | ",
Me.Print oItem.Type; " | ",
Me.Print oItem.VALUE; " | ",
Me.Print oItem.Inherited; " | "
Next
Next
End Sub
The code in Listing 10.4 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 in the For...Each code line. Instead of enumerating the DBEngine properties, this time you enumerated the properties of DBEngine.Workspaces(x). You also added an additional loop that will enumerate the properties of all Workspace objects that might exist.
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 10.5.
Enumerating the Workspace object properties.
You can create new Workspace objects using the CreateWorkspace method of the DBEngine. Even though Visual Basic 4 will create and use a default Workspace object when you first begin database operations, you should create an explicit 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 CH1001.VBP project that will create a new Workspace object. Set the button's Name property to cmdNewWrkSp and set its Caption property to &New WS. Add the code in Listing 10.5 into the cmdNewWrkSp_Click code window.
Listing 10.5. Coding the cmdNewWrkSp_Click event.
Private Sub cmdNewWrkSp_Click()
'
Dim wsNew As Workspace
Dim cWSName As String
Dim cWSUser As String
Dim cWSPassword As String
'
' init vars
cWSName = "NewWorkspace" ' this can be any name
cWSUser = "admin" ' user must already exist
cWSPassword = "" ' password must match user
'
' create workspace object
Set wsNew = DBEngine.CreateWorkspace(cWSName, cWSUser, cWSPassword)
'
' add object to collection
DBEngine.Workspaces.Append wsNew
'
' show the entire collection now
cmdWorkspace_Click
'
End Sub
The code in Listing 10.5 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.MDA 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. Before the system can use the object, you must add the new object to the Workspaces collection, which you do using the Append method. After adding the new object, you force Visual Basic to display the Workspaces collection to see your results.
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 10.6.
The results of adding a new Workspace object.
Eight methods exist for the Workspace object. The Close method is used to close an existing Workspace object. Three of the methods enable you to manage transaction processing (BeginTrans, CommitTrans, and Rollback). Transaction processing is covered on Day 17, "Multiuser Considerations." Two other methods are used to create User and Group objects. You'll learn more about those on Day 20, "Security and Access Control." The remaining two methods enable you to create, open, and close Database objects.
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 10.6 to the cmdCreateDB_Click code window.
Listing 10.6. Coding the cmdCreateDB_Click event.
Private Sub cmdCreateDB_Click()
On Error Resume Next ' ignore errors
'
Dim dbNew As DATABASE ' new db object
Dim cDBName As String ' new db name
Dim wsNew As Workspace ' new workspace object
Dim cWSName As String ' new workspace name
Dim cWSUser As String ' new workspace user
Dim cWSPassword As String ' new workspace password
Dim dbTemp As DATABASE ' for enumerating dbs
'
' init vars
cDBName = App.Path + "\ch1001x.mdb"
cWSName = "ch1001x Workspace"
cWSUser = "admin"
cWSPassword = ""
'
' erase the new db if it's already there
Kill cDBName
'
' create workspace for session
Set wsNew = DBEngine.CreateWorkspace(cWSName, cWSUser, cWSPassword)
DBEngine.Workspaces.Append wsNew
'
' create new JET database
Set dbNew = DBEngine.Workspaces(cWSName).CreateDatabase(cDBName,
dbLangGeneral, dbVersion20)
'
' now show the databases for the workspace
Me.Cls
For Each dbTemp In Workspaces(cWSName).Databases
Me.Print dbTemp.Name
Next
'
End Sub
The code in Listing 10.6 declares some variables, initializes them, and then goes on to create a workspace for this session. Then, it 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. You can open more than one database in the same workspace and group the database operations together.
Save and run the project. When you click on the CreateDB button, the program creates the new database and then shows the results on the form. Your screen should look like Figure 10.7.
Creating a new database.
You can also open the same database in two different workspaces. Modify the project to open the newly created database under two different workspaces at the same time. Add a new command button and set its Name property to cmdOpenDB and its Caption property to &OpenDB. Add the code in Listing 10.7 to the cmdOpenDB_Click code window.
Listing 10.7. Coding the cmdOpenDB_Click event.
Private Sub cmdOpenDB_Click()
On Error Resume Next ' ignore errors
'
Dim wsOne As Workspace ' for first ws
Dim wsTwo As Workspace ' for second ws
Dim cWSOneName As String ' first ws name
Dim cWSTwoName As String ' second ws name
Dim cWSUser As String ' for both ws
Dim cWSPassword As String ' for both ws
Dim dbOne As DATABASE ' first db object
Dim dbTwo As DATABASE ' second db object
Dim cDBName As String ' db name
Dim wsTemp As Workspace ' for listing
Dim dbTemp As DATABASE ' for listing
'
' init vars
cWSOneName = "WorkspaceOne"
cWSTwoName = "WorkspaceTwo"
cWSUser = "admin"
cWSPassword = ""
cDBName = App.Path + "\ch1001x.mdb"
'
' create first workspaces
Set wsOne = DBEngine.CreateWorkspace(cWSOneName, cWSUser, cWSPassword)
Set wsTwo = DBEngine.CreateWorkspace(cWSTwoName, cWSUser, cWSPassword)
DBEngine.Workspaces.Append wsOne
DBEngine.Workspaces.Append wsTwo
'
' now open database first time
Set dbOne = Workspaces(cWSOneName).OpenDatabase(cDBName)
'
' now open database second time
Set dbTwo = Workspaces(cWSTwoName).OpenDatabase(cDBName)
'
' show workspaces and databases
Me.Cls
For Each wsTemp In DBEngine.Workspaces ' enumerate workspaces
Me.Print wsTemp.Name; "|", ' workspace name
For Each dbTemp In wsTemp.Databases ' enumerate databases
Me.Print dbTemp.Name; "|", ' database name
Next
Me.Print "" ' complete print line
Next
'
End Sub
The code in Listing 10.7 declares and initializes several variables for the two Workspace and Database object pairs. 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 to 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 will always exist in the Workspaces collection. Check your screen against Figure 10.8.
The results of the OpenDatabase method in two workspaces.
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 five collections, eight properties, and 16 methods. The Database object contains all the tables, queries, and relations defined for the database. The Database object 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 Database object has five collections:
The data access objects just described are covered in later sections of this chapter. This section focuses on the properties and methods associated with the Database data access 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 cmdDBProps and its Caption property to DB&Props. Enter the code in Listing 10.8 into the cmdDBProps_Click code window.
Listing 10.8. Coding the cmdDBProps_Click event.
Private Sub cmdDBProps_Click()
On Error Resume Next ' ignore errors
'
Dim dbFile As DATABASE ' data object
Dim cDBName As String ' db name
Dim oItem As Object ' to hold properties
'
cDBName = App.Path + "\ch1001.mdb" ' db to open
'
' open db in default workspace
Set dbFile = OpenDatabase(cDBName)
'
' enumerate the db properties
Me.Cls
For Each oItem In dbFile.Properties
Me.Print oItem.Name; "|",
Me.Print oItem.Type; "|",
Me.Print oItem.VALUE; "|",
Me.Print oItem.Inherited
Next
'
dbFile.Close ' close the database
'
End Sub
In Listing 10.8, you opened an existing Microsoft JET database in the default workspace (but did not explicitly declare a session) and then enumerated the properties of the Database object. Save and run the project. Click the DBProps button and compare your screen to the one in Figure 10.9.
The results of enumerating Database object properties.
Table 10.1 lists the Database object properties and their meanings.
Table 10.1. 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 via the locale parameter of the CreateDatabase method. |
| Query Time Out | Numeric (seconds) | This is the amount of time Microsoft JET will wait 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. |
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.
Private Sub cmdDBProps_Click()
On Error Resume Next ' ignore errors
'
Dim dbFile As DATABASE ' data object
Dim cDBName As String ' db name
Dim cConnect As String ' connect parameters
Dim oItem As Object ' to hold properties
'
cDBName = App.Path ' db to open
cConnect = "Text;" ' open a text file
'
' open db in default workspace
Set dbFile = OpenDatabase(cDBName, False, False, cConnect)
..... (code continues).....
Only the first section of the code appears here because that section contains the modifications. Make the changes to your program, save it, and run it. When you click the DBProps button this time, you will see different property values.
The Database object has 16 methods, but this text won't cover all of them here. Three relate to transaction management (BeginTrans, CommitTrans, and Rollback). Three relate to managing QueryDef objects (CreateQueryDef, OpenQueryDef, and DeleteQueryDef). These methods are covered in the section titled, "The QueryDef Data Object," later in this chapter. CreateTableDef methods appear in the section on the Table data object. You will also learn about a CreateRelation method during the Relation Data Objects section. Finally, the Close method is used to close a Database object.
Of the remaining seven methods, three exist only for backward compatibility with older versions of Visual Basic. The new OpenRecordset method has replaced the CreateDynaset, CreateSnapshot, and OpenTable methods. The OpenRecordset, CreateProperty, Execute and ExecuteSQL methods are covered in this section.
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 will be 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 CH1001.VBP project. Set its Name property to cmdRecordset and its Caption property to R&ecordset. Add the code in Listing 10.9 in the cmdRecordset_Click code window.
Listing 10.9. Coding the cmdRecordset_Click event.
Private Sub cmdRecordset_Click()
On Error Resume Next 'ignore errors
'
Dim wsArea As Workspace
Dim dbFile As DATABASE
Dim rsTable As Recordset
Dim rsDynaset As Recordset
Dim rsSnapshot As Recordset
Dim cDBName As String
Dim cTable As String
Dim cDynaset As String
Dim cSnapshot As String
Dim rsTemp As Recordset
'
' init vars
cDBName = App.Path + "\ch1001.mdb"
cTable = "Buyers"
cDynaset = "Publishers"
cSnapshot = "Authors"
'
' create workspace and open database
Set wsArea = DBEngine.CreateWorkspace("wsArea", "admin", "")
DBEngine.Workspaces.Append wsArea
Set dbFile = wsArea.OpenDatabase(cDBName)
'
' create recordset objects
Set rsTable = dbFile.OpenRecordset(cTable, dbOpenTable)
Set rsDynaset = dbFile.OpenRecordset(cDynaset, dbOpenDynaset)
Set rsSnapshot = dbFile.OpenRecordset(cSnapshot, dbOpenSnapshot)
'
' enumerate recordsets for database
Me.Cls
For Each rsTemp In dbFile.Recordsets
Me.Print rsTemp.Name
Next
'
End Sub
The code in Listing 10.9 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 10.10.
The results of the OpenRecordset method.
You can use the Execute and ExecuteSQL methods on a database to perform SQL action queries. The only difference between Execute and ExecuteSQL is that the latter statement returns the number of rows affected by the SQL statement. The Execute method updates the RecordsAffected property of the Database object with the same information returned by ExecuteSQL. It is also faster and uses Microsoft JET resources more efficiently. You should use the Execute method whenever you need to perform an SQL action query on your database.
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 15, "Creating Databases with SQL."
Add a new command button to your project. Set its Name property to cmdExecute and its Caption property to E&xecute. Add the code in Listing 10.10 to the cmdExecute_Click event.
Listing 10.10. Coding the cmdExecute_Click event.
Private Sub cmdExecute_Click()
' on error resume next ' ignore errors
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim cSQL As String
'
' init vars
cDBName = App.Path + "\ch1001.mdb"
cSQL = "DELETE FROM NewAuthors WHERE Au_ID < 10"
'
' open db in default workspace
Set dbFile = OpenDatabase(cDBName)
'
' perform SQL action query
dbFile.Execute cSQL
'
' show number of records affected
MsgBox Str(dbFile.RecordsAffected), vbInformation, "Records Affected"
'
dbFile.Close
'
End Sub
The code in Listing 10.10 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 10.11.
The results of the Execute method.
Visual Basic 4 lets you create user-defined properties 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 database format. If you are not using Microsoft JET 3.0, you can't complete the example in this exercise.
Add a command button to CH1001.VBP. Set its Name property to cmdMakeUDP and its Caption property to &MakeUDP. Add the code in Listing 10.11 to the cmdMakeUDP_Click window.
Listing 10.11. Coding the cmdMakeUDP_Click event.
Private Sub cmdMakeUDP_Click()
On Error Resume Next
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim cUDPName As String
Dim nUDPType As Integer
Dim vUDPValue As Variant
Dim pDBAdmin As Property
Dim pProgrammer As Property
Dim pTemp As Property
'
' open db
cDBName = App.Path + "\ch1003.mdb" ' open version 3.0 JET db
Set dbFile = OpenDatabase(cDBName)
'
' add first UDP
cUDPName = "DBAdmin"
nUDPType = dbText
vUDPValue = "Joe DB Guru"
dbFile.Properties.DELETE cUDPName ' delete it if it's already here
Set pDBAdmin = dbFile.CreateProperty(cUDPName, nUDPType, vUDPValue)
dbFile.Properties.Append pDBAdmin
'
' add second UDP
cUDPName = "Programmer"
nUDPType = dbText
vUDPValue = "Fred Bitwise"
dbFile.Properties.DELETE cUDPName ' delete it first
Set pProgrammer = dbFile.CreateProperty(cUDPName)
pProgrammer.Type = nUDPType
pProgrammer.VALUE = vUDPValue
dbFile.Properties.Append pProgrammer
'
' enumerate the db properties
Me.Cls
For Each pTemp In dbFile.Properties
Me.Print pTemp.Name; "|",
Me.Print pTemp.Type; "|",
Me.Print pTemp.VALUE
Next
'
dbFile.Close
'
End Sub
The routine in Listing 10.11 adds two user-defined properties to the database. Notice that you attempted 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 10.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 three collections, five methods, and 10 properties.
The TableDef object has three collections:
Details of the Field and Index objects are covered later in this chapter.
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 10.12 shows the properties of a native Microsoft JET TableDef object.
Add another button to the CH1001.VBP project. Set its Name property to cmdTableDef and its Caption property to &TableDef. Add the code in Listing 10.12 to the cmdTableDef_Click event.
Listing 10.12. Adding the TableDef button.
Private Sub cmdTableDef_Click()
On Error Resume Next ' ignore errors
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim cTable As String
Dim proTemp As Property
'
' init vars
cDBName = App.Path + "\ch1001.mdb"
cTable = "BookSales"
'
' open db in default ws
Set dbFile = DBEngine.OpenDatabase(cDBName)
'
' open data table
Set tdTemp = dbFile.CreateTableDef(cTable)
'
' enumerate the tabledef properties
Me.Cls
For Each proTemp In tdTemp.Properties
Me.Print ">";
Me.Print proTemp.Name,
Me.Print proTemp.VALUE;
Me.Print "<"
Next
'
dbFile.Close
'
End Sub
The code in Listing 10.12 opens a database, creates a TableDef object using the CreateTableDef method, and then lists the properties to the form. Save and run the project. Click the TableDef button and compare your screen with the one in Figure 10.13.
Viewing the TableDef properties.
Five methods exist that you can apply to the TableDef object:
The code in Listing 10.13 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 10.13 to the cmdCreateTable_Click event.
Listing 10.13. Coding the cmdCreateTable_Click event.
Private Sub cmdCreateTable_Click()
On Error Resume Next
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim cTable As String
Dim fldTemp As Field
Dim cFldName As String
Dim nFldType As Integer
Dim proTemp As Property
'
' init values
cDBName = App.Path + "\ch100x.mdb"
cTable = "NewTable"
cFldName = "NewField"
nFldType = dbText
'
' erase it if it's already there
Kill cDBName
'
' create new database
Set dbFile = DBEngine.CreateDatabase(cDBName, dbLangGeneral, dbVersion20)
'
' create tabledef
Set tdTemp = dbFile.CreateTableDef(cTable)
'
' create field
Set fldTemp = tdTemp.CreateField(cFldName, nFldType)
'
' append objects
tdTemp.Fields.Append fldTemp
dbFile.TableDefs.Append tdTemp
'
' enumerate new table properties
Me.Cls
For Each proTemp In tdTemp.Properties
Me.Print proTemp.Name,
Me.Print proTemp.VALUE
Next
'
dbFile.Close
'
End Sub
The code in Listing 10.13 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 listed to the form. Save and run the project. Check your results against Figure 10.14.
The results of adding a new table.
You can add new fields or delete existing fields by using the Append or Delete methods on the TableDefs object. Add a command button with the Name property cmdModTable and a Caption property of M&odTable. Add the code in Listing 10.14 to the cmdModTable_Click event.
Listing 10.14. Coding the cmdModTable_Click event.
Private Sub cmdModTable_Click()
On Error Resume Next
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim cTable As String
Dim fldTemp As Field
Dim cFldName As String
Dim nFldType As Integer
Dim fldNew As Field
Dim proTemp As Property
'
' init vars
cDBName = App.Path + "\ch100x.mdb"
cTable = "NewTable"
cFldName = "NewField2"
nFldType = dbDate
'
' first create the database
' by calling the previous example
cmdCreateTable_Click
'
' let user see this
MsgBox "Click OK to continue..."
'
' now open that db
Set dbFile = DBEngine.OpenDatabase(cDBName)
'
' set tabledef object
Set tdTemp = dbFile.TableDefs(cTable)
'
' add a new field
Set fldNew = tdTemp.CreateField(cFldName, nFldType)
tdTemp.Fields.Append fldNew
'
' now display field list
Me.Cls
Me.Print "Added Field - Table Field List:"
For Each fldTemp In tdTemp.Fields
Me.Print fldTemp.Name
Next
'
' delete the new field
tdTemp.Fields.DELETE cFldName
'
' display field list again
Me.Print ""
Me.Print "Deleted Field - Table Field List:"
For Each fldTemp In tdTemp.Fields
Me.Print fldTemp.Name
Next
'
dbFile.Close
'
End Sub
In Listing 10.14, 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 10.15.
The results of adding and deleting fields.
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 cmdAttach and its Caption property to &Attach. Add the code in Listing 10. 15 to the cmdAttach_Click event.
Listing 10.15. Coding the cmdAttach_Click event.
Private Sub cmdAttach_Click()
On Error Resume Next
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim cAttName As String
Dim cAttDBType As String
Dim cAttDBName As String
Dim cAttSrcName As String
'
' init vars
cDBName = App.Path + "\ch1001x.mdb"
cAttName = "FoxAttached"
cAttDBType = "FoxPro 2.5;"
cAttDBName = App.Path
cAttSrcName = "fldtypes.dbf"
'
' call routine to create database
cmdCreateTable_Click
'
' now open db
Set dbFile = OpenDatabase(cDBName)
'
' create a new table in the MSJet database
Set tdTemp = dbFile.CreateTableDef(cAttName)
'
' now build attachment info
tdTemp.Connect = cAttDBType + "DATABASE=" + cAttDBName
tdTemp.SourceTableName = cAttSrcName
'
' append new attachment to the database
dbFile.TableDefs.Append tdTemp
'
' show list of tables in database
Me.Cls
For Each tdTemp In dbFile.TableDefs
Me.Print tdTemp.Name
Next
'
dbFile.Close
End Sub
The code in Listing 10.15 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 also 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 button, you see a few screens flash by. The final screen lists all the tables in the database. Notice that the FoxAttached table now appears. You can now manipulate this table like any native Microsoft JET data table object.
WARNING: 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 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 four methods.
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 10.16 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 10.16 to the cmdFields_Click event window.
Listing 10.16. Coding the cmdFields_Click event.
Private Sub cmdFields_Click()
On Error Resume Next
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim fldTemp As Field
Dim proTemp As Property
'
cDBName = App.Path + "\ch1001z.mdb"
'
' open db
Set dbFile = OpenDatabase(cDBName)
'
' get table definitions
Debug.Print String(10, "*") + "ch1001x.mdb"
For Each tdTemp In dbFile.TableDefs
Debug.Print String(5, "*") + tdTemp.Name
For Each fldTemp In tdTemp.Fields
Debug.Print String(3, "*") + fldTemp.Name
For Each proTemp In fldTemp.Properties
Debug.Print proTemp.Name,
Debug.Print ">";
Debug.Print proTemp.VALUE;
Debug.Print "<"
Next
Next
Next
'
' tell user to look at debug window.
MsgBox "Data was Written to the DEBUG Window."
'
dbFile.Close
End Sub
The code in Listing 10.16 creates the database and then loops through the collection to list the properties of every field and every table in the database. Because the field collection is a long list, you send the output to the Debug window instead of to the form. Save and run the project. When you click the Fields button, the program sends all the data to the Debug window. Because this process takes time, you send out a message when the job finishes.
Before you exit the program, click the Pause button on the main toolbar to bring up the Debug window. You see a lengthy list of the fields and their properties. Check your screen against the one in Figure 10.16.
The Field properties in the Debug window.
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 10.17 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 10.17 to the cmdIndex_Click event.
Listing 10.17. Coding the cmdIndex_Click event.
Private Sub cmdIndex_Click()
' on error resume next
'
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim idxTemp As Index
Dim idxField As Field
Dim cIdxName As String
Dim cIdxField As String
Dim proTemp As Property
'
' init vars
cDBName = App.Path + "\ch1001x.mdb"
cIdxName = "PKNewTable" ' name of index
cIdxField = "NewField" ' name of field
'
' call routine to create database
cmdCreateTable_Click
'
' now open created db
Set dbFile = OpenDatabase(cDBName)
'
' let's make an index!
Set idxTemp = dbFile.TableDefs("NewTable").CREATEINDEX(cIdxName)
' define field object for index
Set idxField = idxTemp.CreateField(cIdxField)
idxTemp.PRIMARY = True ' make it primary index
idxTemp.Required = True ' make it required
idxTemp.Fields.Append idxField ' add field object
' add whole thing to index collection
dbFile.TableDefs("NewTable").Indexes.Append idxTemp
'
' show index properties
Me.Cls
For Each idxTemp In dbFile.TableDefs("newTable").Indexes
For Each proTemp In idxTemp.Properties
Me.Print proTemp.Name,
Me.Print ">";
Me.Print proTemp.VALUE;
Me.Print "<"
Next
Next
'
dbFile.Close
End Sub
The code in Listing 10.17 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: Even though 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 10.17.
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 10.18 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 the Name property set to cmdQuery and the Caption property set to &Query. Add the code in Listing 10.18 to the cmdQuery_Click code window.
Listing 10.18. Coding the cmdQuery_Click event.
Private Sub cmdQuery_Click()
' on error resume next
'
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim rsTemp As Recordset
Dim qryTemp As QueryDef
Dim idxField As Field
Dim cQryName As String
Dim cQryText As String
Dim proTemp As Property
'
' init vars
cDBName = App.Path + "\ch1001x.mdb"
cQryName = "qryTestSelect" ' name of query
cQryText = "SELECT * FROM NewTable" ' text of query
'
' call routine to create database
cmdCreateTable_Click
'
' now open created db
Set dbFile = OpenDatabase(cDBName)
'
' now add querydef to database
Set qryTemp = dbFile.CreateQueryDef(cQryName)
qryTemp.SQL = cQryText
'
' now create a record set using query
Set rsTemp = dbFile.OpenRecordset(cQryName, dbOpenDynaset)
'
' now show us all the properties
Me.Cls
For Each qryTemp In dbFile.QueryDefs
For Each proTemp In qryTemp.Properties
Me.Print proTemp.Name,
Me.Print ">";
Me.Print proTemp.VALUE;
Me.Print "<"
Next
Next
'
dbFile.Close
'
End Sub
Save and run the project. Check your final screen against the one in Figure 10.18.
The results of creating a QueryDef 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.
NOTE: The details of defining relationships are covered in the chapters on Advanced SQL next week (Days 14 and 15). For now, remember that you can use the Relation objects 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 10.19 to the cmdRelation_Click event window.
Listing 10.19. Coding the cmdRelation_Click event.
Private Sub cmdRelation_Click()
On Error Resume Next
'
Dim dbFile As DATABASE
Dim tdTemp As TableDef
Dim idxTemp As Index
Dim fldTemp As Field
Dim relTemp As Relation
Dim proTemp As Property
'
Dim cDBName As String
Dim cTblLookUp As String
Dim cTblMaster As String
Dim cIdxLookUp As String
Dim cIdxMaster As String
Dim cRelName As String
'
cDBName = App.Path + "\ch1001q.mdb"
cTblLookUp = "ValidUnits"
cTblMaster = "MasterTable"
cIdxLookUp = "PKUnits"
cIdxMaster = "PKMaster"
cRelName = "relUnitMaster"
'
' erase datbase if it's already there
Kill cDBName
'
' create database
Set dbFile = CreateDatabase(cDBName, dbLangGeneral, dbVersion20)
'
' create list table
' this has the stuff to lookup
Set tdTemp = dbFile.CreateTableDef(cTblLookUp)
'
' add fields to the table
Set fldTemp = tdTemp.CreateField("UnitID", dbText, 10)
tdTemp.Fields.Append fldTemp
'
Set fldTemp = tdTemp.CreateField("UnitDesc", dbText, 30)
tdTemp.Fields.Append fldTemp
'
' add main index to ValidUnits table
Set idxTemp = tdTemp.CREATEINDEX(cIdxLookUp)
idxTemp.PRIMARY = True
idxTemp.Required = True
Set fldTemp = tdTemp.CreateField("UnitID")
idxTemp.Fields.Append fldTemp
tdTemp.Indexes.Append idxTemp
'
' append table def to database
dbFile.TableDefs.Append tdTemp
'
' now create master table
' this table will need a reference to lookup
Set tdTemp = dbFile.CreateTableDef(cTblMaster)
'
' now add some fields
Set fldTemp = tdTemp.CreateField("MasterName", dbText, 20)
tdTemp.Fields.Append fldTemp
'
Set fldTemp = tdTemp.CreateField("MstrUnitID", dbText, 10)
tdTemp.Fields.Append fldTemp
'
' add main index to master table
Set idxTemp = tdTemp.CREATEINDEX(cIdxMaster)
idxTemp.PRIMARY = True
idxTemp.Required = True
Set fldTemp = tdTemp.CreateField("MasterName")
idxTemp.Fields.Append fldTemp
tdTemp.Indexes.Append idxTemp
'
' append table to db
dbFile.TableDefs.Append tdTemp
'
' now set a relationship
Set relTemp = dbFile.CreateRelation(cRelName)
relTemp.TABLE = cTblLookUp ' table for lookups
relTemp.ForeignTable = cTblMaster ' table to check
Set fldTemp = relTemp.CreateField("UnitID") ' field to lookup
fldTemp.ForeignName = "MstrUnitID" ' field to check
relTemp.Fields.Append fldTemp ' add field object to relation object
relTemp.Attributes = dbRelationUpdateCascade ' for cacading updates
dbFile.Relations.Append relTemp ' book the completed relation object
'
' enumerate the relation object
Me.Cls
For Each relTemp In dbFile.Relations
For Each proTemp In relTemp.Properties
Me.Print proTemp.Name,
Me.Print ">";
Me.Print proTemp.VALUE;
Me.Print "<"
Next
'
Me.Print "Relation Fields:"
For Each fldTemp In relTemp.Fields
Me.Print "",
Me.Print "Name: ";
Me.Print fldTemp.Name,
Me.Print "ForeignName: ";
Me.Print fldTemp.ForeignName
Next
Next
'
End Sub
The code in Listing 10.19 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 on the Relation command button the program will create all the data objects, and then display the resulting Relation object on the form. Compare your results to the screen in Figure 10.19.
The results of adding 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 will automatically be updated 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. 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 will create 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 will execute 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 in the CustomerTypes table that are used in the Customers table.
