Most application programs need to store some kind of data. Visual Basic gives the application program developer many options in this area. With the rich VBA language, a programmer can create and read low-level disk files directly; or through API calls,
the programmer can read and edit INI files. But for more powerful and maintainable data storage and retrieval, it is Visual Basic's database management capabilities that come into play.
This chapter covers some Visual Basic database fundamentals. It starts with an introduction to the database concept and ends with some useful comments on keeping a database in good shape. Along the way, you will see some examples that create a database
and modify it.
In the simplest conceptual terms, a computer database is just an electronic filing cabinet. Unlike an old-fashioned filing cabinet, however, a well-designed database facilitates automated retrieval and management of the data stored in it.
In practice, a computer database is a collection of tables. Tables hold items that are described by the same list of characteristics. For example, the tblCandidate table in the database of Figure 31.1 contains people's names and other information. Each
person whose name is stored in the database has a record in that table that describes their first name, last name, middle initial, and a few other items. The point however, is that each person is described by the same list of characteristics—their
name, and so on. It is only the values of those characteristics that change from record to record. For example, the first name and last name of each person is stored, but all persons do not have the same name.
FIGURE 31.1. The tblCandidate table from the BTS (Best Team Selector) database. BTS is a sample program on the CD-ROM that accompanies this book.
In the language of SQL databases, a record also is known as a row, and a field also is known as a column. Figure 31.2 shows the data in the tblCandidate table, which shows why these terms so readily lend themselves. The records appear as rows, and the
fields are stacked as columns.
FIGURE 31.2. The tblCandidate table showing columns and rows.
Although a complex definition for a relational database exists, the practical concept is this: In a relational database, tables are related to other tables through their fields. In the BTS sample program, the tblCandidate table is related to the
tblCandidateSkill table through the CandidateKey field, which they both share. In other words, when the value of a field in one table record matches the value of a designated field in another table record, those records are related. Table relationships do
not happen automatically. It is up to the database designer to designate fields between tables that relate to each other.
Note
Sometimes pronounced sequel, SQL stands for Structured Query Language. It has become the de facto interface to relational database management systems and is a database interface available in most of today's development tools. Visual Basic 4.0 supports
SQL for its database operations.
NOTE
SQL is not a language in the same sense that Basic or C/C++ are languages. Basic and C/C++ are general-purpose languages used to build programs by creating loops and conditions that are executed in a procedural fashion. The XBase dialects (dBASE,
Clipper, and FoxPro) are in the same category as Basic and C/C++, although they are targeted at database management. A SQL statement, however, is not a sequentially executing list of instructions. Instead, each SQL statement is a complete query that tells
an RDBMS what results you want, not necessarily every detail about how to get those results. The RDBMS translates your query into whatever steps are necessary to fulfill your request.
Webster's New World Dictionary defines a query as "a question." That's pretty much what the word means in SQL. Instead of phrasing SQL questions in English, however, you phrase them using the SQL language. You can go a considerable distance
just by knowing how to apply the following keywords:
|
SELECT |
Specifies the table columns (fields) you want to know about. |
|
FROM |
Specifies the rows of data to which the query applies. (In the examples used in this chapter, this is where you specify the source table. In more complex examples, like those in Chapter 32, "Unleashing SQL," this is where you embed other SQL queries.) |
|
WHERE |
Specifies conditions that limit the number of table rows returned. |
Some simple examples showing how to apply these keywords follow; here, the Best Team Selector database is used. (See Chapter 36, "Optimization Selection Database System," for more information on this database. The database is available on the
CD-ROM that accompanies this book.)
The following code returns a list of all the candidates' first names:
SELECT tblCandidate.FirstName FROM tblCandidate;
To return the first name of every available candidate, use this code (availability is defined in the tblCandidate table by the Yes/No field "Available"):
SELECT tblCandidate.FirstName FROM tblCandidate WHERE ((tblCandidate.Available=True));
To return the first and last name of each available client, just modify the select portion of the SQL statement to get the following:
SELECT tblCandidate.FirstName, tblCandidate.LastName FROM tblCandidate WHERE ((tblCandidate.Available=True));
To return ALL fields of the available candidates, use the following code:
SELECT * FROM tblCandidate WHERE ((tblCandidate.Available=True));
You can try out these examples by adding a data control to a new form and placing the example SQL query into the RecordSource property. The DatabaseName property should be set to the MAKETEAM.MDB. You can see the result of the query by placing a DBList
on the form and setting the RowSource property to the name of your data control and the ListField property to the name of the field you want to display.
NOTE
There is also another class of SQL queries usually referred to as action queries. In the strictest sense, these are not really questions, although in most environments they return an answer with regard to the success or failure of the query:
|
ALTER TABLE |
Alters an existing database table. |
|
CREATE INDEX |
Creates an index for an existing database table. |
|
CREATE TABLE |
Creates a new database table. |
|
DELETE |
Deletes records from a table. |
|
INSERT INTO |
Inserts records into a table. |
|
UPDATE |
Updates records in a table. |
The CREATE TABLE, CREATE INDEX, and ALTER TABLE action query keywords are known as Data Definition Language statements, sometimes abbreviated as DDL. These are not supported by Visual Basic 4.0 on non-Jet engine databases. In practice, unless your
application needs to create or modify a database (other than at the record level) you will not use these commands. If you do need these actions, Visual Basic supports them through methods of the data control and through SQL Passthrough. In general, you may
want to create and edit your databases with other tools or at minimum with Visual Basic's Data Manager.
To create a simple database that contains one table by using the Visual Basic Data Manager, follow these steps:
Figure 31.3. The Add Table dialog box.
Figure 31.4. Adding records to a table.
The remaining action query commands—INSERT INTO, DELETE, and UPDATE are subject only to restrictions of the particular database or table to which they are applied. Some simple examples, which again use the BTS database, follow.
To create a record in the tblCandidate table with Bobby as the FirstName field and Demo as the LastName field, use this code:
INSERT INTO tblCandidate(FirstName,LastName)
VALUES ("Bobby","Demo");
This code deletes the Bobby Demo record from the tblCandidate table:
DELETE *
FROM tblCandidate
WHERE (tblCandidate.FirstName="Bobby")
AND (tblCandidate.LastName="Demo");
To change the last name of all Bobby Demo records in the table, use this code:
UPDATE tblCandidate
SET [tblCandidate.LastName] = "Ademo"
WHERE (tblCandidate.FirstName="Bobby")
AND (tblCandidate.LastName="Demo");
We've only touched on the SQL language here. For more comprehensive coverage, turn to Chapter 32.
Database back end is a common term these days that refers to the type of database used by a program. The back-end part of the phrase stems from the fact that the database is usually not something the users see directly on their desktops. They interact
with the database in whichever ways their programs enable them. The particular database in use by the program is of no particular intellectual importance to them if the program they use gives them what they want.
As with most modern Windows development tools, you are not limited to one type of database system when using Visual Basic 4.0—you can use Access, various XBase flavors, Excel and many others for database storage. Visual Basic's ODBC and OLE 2
support enables it to access any database compatible with the ODBC or OLE 2 standard. These databases include Microsoft SQL Server, Oracle, and Sybase. Oracle has both OLE 2 and ODBC drivers. Sybase is accessible through ODBC.
You can choose from two groups of database back ends: ISAM and client/server. ISAM stands for Indexed Sequential Access Method, and just about any popular database that is not configured as client/server falls into this category. ISAM databases include
the dBASE (DBF) types, Access (MDB), and many others. The distinguishing characteristic of ISAM databases is that they are directly manipulated in every way by the computer running the query program.
TIP
Client/server databases, on the other hand (as the name implies), reside on something manipulated at the most basic levels by an RDBMS server. The client interacts with the database by making requests to the RDBMS, which the server then carries out.
Oracle, Sybase, and Microsoft SQL Server are some popular client/server databases available today.
If you need a database scheme for use by one computer at a time, ISAM is usually an obvious choice. Among its benefits are more straightforward development and maintenance issues. Depending on the type of system hardware and software function, ISAM also
can respond faster than a client/server approach.
However, if you are developing a database application for use by several computers simultaneously and fault-tolerance is a very important factor, client/server schemes should be considered. In the ISAM implementations, generally any computer writing to
the database has the opportunity to physically corrupt it. In a multiuser environment, this can mean that everyone is exposed to the instability of just a few user computers. If a corruption can be fixed, all users may have to log out while the database is
repaired.
If the RDBMS server in a client/server scheme is well designed and maintained, physical database corruption is less likely. Only the server itself can physically corrupt the database, and if the server is well designed and maintained, this would be
unusual. Unlike user PCs, access to the configuration of the server usually is tightly controlled.
In general, the following rules apply when the user or users will be writing to a database in an application:
Database Situation Comment
|
Small, single-user database |
ISAM is the best choice. Simple to implement and can be the fastest solution. |
|
Small database with |
ISAM may not be a bad choice. See the later few simultaneous users section, "Keeping Your Database Healthy." |
|
Small database with |
No easy answer here. If the number of transactions many simultaneous users is small, ISAM may be adequate. However, if fault prevention is paramount, RDBMS client/server is the only real choice. |
|
Huge, single-user database |
Most RDBMS servers are optimized in ways that significantly enhance performance of the SQL queries. |
|
Huge database with |
A client/server RDBMS may be the only way to many simultaneous users implement this system with acceptable performance and reliability. |
The RDBMS nature of Visual Basic 4.0 database interaction goes a long way toward simplifying multiuser database access. On the surface, sharing client/server databases is just a matter of appropriate definitions at the server level by the Database
Administrator (DBA), and sharing ISAM databases is a matter of placing the database in a network directory and applying the OpenDatabase method without setting exclusive access to True. For quick and simple shared database applications, this can be enough
to share a database, because the Jet engine and the client/server RDBMS can handle multiple users behind the scenes.
Looking a little deeper however, there are some subtle issues that can have tremendous impact on the usability and performance of a shared database application. The following items are of particular relevance to both ISAM and client/server
implementations:
Every time a table is written to, its indexes must be updated. If there are many indexes or the indexes are very complex or large, the update can significantly impact the total number of operations and time dedicated to the transaction. In an ISAM
environment, this means more opportunity for a physical database corruption by the user's machine. In both a client/server RDBMS and an ISAM environment, it can mean slower processing. It is generally a good idea to keep indexes as few and simple as
possible, although the inclination is to index everything to cover all the "what if someone asks for..." situations. Ironically, the increased risk of corruption in an ISAM environment from excess indexes also translates into longer down time for
repairs. (Compacting an MDB and packing a DBF causes re-creation of every index associated with the affected tables. Compacting and packing of ISAM databases is explained in the "Keeping Your Database Healthy" section of this chapter.) In
general, smaller and fewer is better where indexes are concerned.
Although Visual Basic 4.0 does a good job of shielding the mundane issues of shared data access from the application developer, a fundamental fact cannot be ignored when multiple users read and write to the same database: Data cannot be assumed to stay
static. This means that it is a good programming practice to issue the Idle method periodically to update program record sets and release unneeded read locks.
Read locks are issued by some databases to ensure that related records are not altered. Sometimes these are set automatically by database management systems, and sometimes they are set at runtime by program code. The Idle method can be used to free
those locks sooner than they might otherwise be freed when issued with the dbFreeLocks parameter. (There is a detailed explanation of this method and what it does in the Visual Basic help file under the keyword "idle.")
All users of the shared database benefit when locks are removed, because a lock can interfere with another user's update request. The user issuing an Idle benefits by getting a refresh of the database. This is especially relevant if the user program is
displaying a list of table items and some of those items have been modified or removed in the time that the list was displayed originally.
Transactions help performance of a shared database application in two significant ways:
Transactions are implemented in Visual Basic 4.0 by the BeginTrans, RollBack, and CommitTrans methods. Start a transaction by applying the BeginTrans method to your database workspace. Until Visual Basic 4.0 executes a Rollback or CommitTrans
method on that workspace, it will not actually "write" any updates to the shared database. Instead, all updates are stored in RAM and in temporary files. If your application will update many records in a batch process, the shared database will be
impacted only once when the CommitTrans method is issued at the end, instead of during each iteration of the batch.
In the case of a batch update failure, an issue to the Rollback method on the database workspace prevents any of the pending updates from affecting the shared database. This is especially significant when changes to one part of a database are only
meaningful if updates in other parts take place simultaneously. A Rollback command can be issued via an On Error construction, as illustrated in Listing 31.1, to correct for unanticipated errors during processing.
Sub BatchDatabaseUpdate(MyDB as Database)
MyDB.BeginTrans 'Start the transaction block.
On Error Goto BatchDatabaseUpdate_Undo
...
... Batch Processing
...
MyDB.CommitTrans 'Commit all the batch processing.
Exit Sub
BatchDatabaseUpdate_Undo:
MyDB.Rollback 'Undo the transaction block.
End Sub
An important concept when dealing with multiuser database updates is that of pessimistic versus optimistic locking. A pessimistic locking scheme is one in which a database application assumes that unless it "locks out" other users from a
particular record, another user will try to modify it while the program needs to use it exclusively. (Read locking falls somewhat into the pessimistic locking umbrella.) An optimistic locking scheme applies the opposite philosophy: It cheerfully assumes
that no one will interfere with the record while it does exclusive processing. These two schemes are of particular significance in the case of a record edit. A pessimistic scheme would issue a lock on a record as soon as the Edit method is issued. This
lock would exist for the duration of the edit. In fact, the user could walk away from the machine after starting the edit, and unless the program has a built-in time-out feature, the lock would persist until the user comes back and issues the Update
method. The advantage for the user here is that there is no chance something will happen to the record while the user is editing it. Any defined relationships this record might have in the database are not going to change. This ensures that the user update
will not conflict with the database constraints when the database update is performed.
However, the downside to pessimistic locking is obvious: No one else can update that record or perform other exclusive operations on the database if they impact that record in any way. Visual Basic 4.0 is pessimistic by default for some databases.
The optimistic locking alternative is more multiuser-database friendly. Optimistic locking can be implemented in Visual Basic 4.0 by setting the LockEdits property of the database to False. A well designed, multiuser database that allows extended-use
edits of its data generally should employ optimistic record locking to reduce impact on other system users. Listing 31.2 shows a construction that implements optimistic locking on a modal form called frmEditRecord through a Visual Basic Data control called
Data1.
Note
———————————————————————————————————
'This subroutine just illustrates the concept of using optimistic
'record locking. In practice, unless a modal edit window
'is desired the DataChanged property check and logic would
'be on the OK or SAVE button of the edit form itself.
———————————————————————————————————
Sub FriendlyEdit()
Dim Done
Done = FALSE
Data1.LockEdits = FALSE
While Not Done
Data1.Edit
frmEditRecord.Show 1 'Modal edit form.
If Data1.DataChanged Then
'Not safe to write back - may overwrite someone else.
Beep
MsgBox "Someone changed the record data.",48,"Changed"
Data1.UpdateControls 'Load controls with new data.
Else
'Safe to write changes back.
Data1.Update
Done = TRUE
Endif
Wend
End Sub
A very subtle database-sharing issue lies in the physical type of locking that an RDBMS employs. There are two types: record-level locking and page-level locking. Record-level locking is implemented by the DBF types (dBASE and FoxPro, among others) and
some client/server RDBMSs (Oracle is one). A record-level locking scheme locks only those records that need to be locked in order for a particular exclusive operation to take place. Page-level locking schemes, on the other hand, lock entire sections of a
database to lock a record. The locked section is the page or pages of the database that contain the record. Pages do not usually correspond to records in size, so several records are included in a single page, or records can span pages. Microsoft SQL
Server, Access, and BTrieve are three page-locking databases.
The logic behind a page-locking implementation is that of speed: Less computing is required of the DBMSs to identify and mark the section of the database that contains the target record. For reasons beyond the scope of this chapter, page calculation is
computationally simple for the RDBMS. The downside of this scheme is the unnecessary and somewhat unpredictable locking of unrelated records. Page-locking systems, more than any others, need to ensure quick turnaround on locks. It can be quite unacceptable
in a large, multiuser page-locking database to employ pessimistic locking because it may lock neighboring records for significantly long periods of time. If you use a page-locking database, you may need to pay particular attention to the dynamics of your
application and the relative size of the table records to the table page sizes.
The subject of relational database design can be extremely complex. However, there are some fundamentals. A good place to begin is with database normalization.
Normalization is the process of removing redundancies in a database scheme. In the language of relational databases, there are five standard normal forms. The last two are rarely applied, difficult to describe, and difficult to understand. For this
reason, I will only discuss the first three in detail. The fourth and fifth forms are only listed here with limited explanation.
Normal Form |
Function |
|
1 |
Eliminates repeating groups. Different items belong in separate tables, and each table should have a primary key. |
|
2 |
Eliminates redundant data. If a field depends on only part of a multifield key, remove it to a separate table. |
|
3 |
Eliminates columns not dependent on a key. If fields do not contribute directly to the description of a key, move those fields to a new table. |
|
4 |
Isolate independent multiple relationships. |
|
5 |
Isolate semantically related multiple relationships. |
Each normal form builds on the one before it; in other words, a database that meets the requirements of third normal form also satisfies normal forms 2 and 1.
An example of a completely non-normalized database is a database consisting of one table containing the names of people with skills they possess (see Figure 31.5). Because this is a single table, a person with more than one skill appears more than once
in the table. In each record, the name and other information identifying the person is repeated. To normalize this database, you could remove the fields describing the skills to a new table. The new "tblPeople" table would now use the
"PeopleKey" as a foreign key into the new "tblPeopleSkill" table (see Figure 31.6). (Foreign key is just a fancy term for a field or group of fields that are used to "lookup" values in another table.)
FIGURE 31.5. A non-normal form database.
FIGURE 31.6. The first normal form tblPeople, and the tblPeopleSkill database.
The database in the above example is still not in second normal form, because the tblPeopleSkill table lists the name of the skill although the unique key into the table is PeopleKey + SkillKey and SkillName only applies to SkillKey. As a result, the
skill name is repeated in the table each time more than one person has a particular skill. To place the database into second normal form, you remove the SkillName field from the table and place it into a new table to which the SkillKey is a foreign key.
Figure 31.7 shows the result of changing the tables from Figure 31.6 as described.
FIGURE 31.7. The second normal form tblPeople, tblPeopleSkill, and the tblSkill database.
The database is now in second normal form, but not third because the tblPeople table contains fields that are not really about people. The LocationName field is an attribute independent of the PeopleKey field, so you can move it to its own table and
replace LocationName with LocationKey. Figure 31.8 illustrates this change.
FIGURE 31.8. The third normal form of our database. You now have four tables: tblPeople, tblPeopleSkill, tblSkill, and tblLocation.
Normalization can be a good exercise when designing a database because it forces a bit of rigor into the analysis. The elegance of normalization is that it reduces the data stored in the database to its smallest logical components. The cost, however, is
sometimes an excess of tables and indexes for a database. In general, fewer tables and fewer indexes can mean less database application overhead. Many databases eventually are denormalized to improve performance. It is not unusual to normalize an original
design to the third level and then denormalize it back to the first form to reduce disk storage needs and to improve access time.
One more fundamental relational database design element is that of the relationship definitions themselves. In general, there are three types:
There are variations on the preceding relationship types with respect to whether at least one record is required or not. In other words, some tools allow the specification that at least one record must exist in the many side of a one to many
relationship. By the same token, some tools allow a one to many relationship to have zero records on the many side. Figure 31.9 shows the generic symbols sometimes used to illustrate these relationships in database diagrams.
FIGURE 31.9. Generic database diagram relationship symbols.
If you are using a client/server RDBMS, these relationships can be defined in the database and enforced by the RDBMS. When using ISAM databases with Visual Basic 4.0, maintenance of relationships relies entirely on the program code.
Unless your program has to create a database at runtime, the best way to create your database for use with Visual Basic 4.0 is to use the Data Manager application or to use a database tool designed for that database. Following are some ISAM database
tools:
Database Type |
Database Tool |
|
Access |
Microsoft Access |
|
dBASE |
Borland Visual dBASE |
|
FoxPro |
Microsoft Visual FoxPro |
|
Paradox |
Borland Paradox for Windows |
Most client/server databases come with their own tools for use by the DBA and are of varying quality and usability. If you are working with client/server databases and do not like the tools that come with them, or you just want to take a step up in
relational database design, several third-party relational database tools are available that create databases for you after you design them interactively in their environment. Most of the larger mail-order software vendors carry a few tools.
If you choose to create a database from within Visual Basic 4.0 code, there are two ways to do it: You can use the Visual Basic 4.0 methods or if your RDBMS supports it, the SQL language directly. The Visual Basic 4.0 methods can create only Access-type
databases (MDB). Listing 31.3 creates an Access database with three tables similar to those of the BTS program found on the CD-ROM. Figure 31.10 shows the result of this code listing.
FIGURE 31.10. Diagram of tables created by Listing 31.3.
'———————————————————————————————————
'Sample code to create a three table Access database.
'———————————————————————————————————
Sub CreateTestDB()
'Define the variables we will use.
Dim MyDB As DATABASE, MyWs As Workspace
Dim tblC As TableDef, tblS As TableDef, tblCS As TableDef
Dim fldC(3) As Field, fldS(4) As Field, fldCS(5) As Field
Dim idxC As Index, idxS As Index, idxCS As Index
Dim fldTemp As Field
Dim DBName As String
'Create the database in the same directory as application.
DBName = App.Path + "\TEST.MDB"
Set MyWs = DBEngine.Workspaces(0)
Set MyDB = MyWs.CreateDatabase(DBName, dbLangGeneral, dbVersion20)
'Create the tables of the database.
Set tblC = MyDB.CreateTableDef("tblCandidates")
Set tblS = MyDB.CreateTableDef("tblSkill")
Set tblCS = MyDB.CreateTableDef("tblCandidateSkill")
'Create the fields of the Candidates table.
Set fldC(0) = tblC.CreateField("CandidateKey", dbLong)
fldC(0).Attributes = dbAutoIncrField
Set fldC(1) = tblC.CreateField("FirstName", dbText)
fldC(1).Size = 20
Set fldC(2) = tblC.CreateField("LastName", dbText)
fldC(2).Size = 25
'Create the fields of the Skills table.
Set fldS(0) = tblS.CreateField("SkillKey", dbLong)
fldS(0).Attributes = dbAutoIncrField
Set fldS(1) = tblS.CreateField("Name", dbText)
fldS(1).Size = 20
'Create the fields of the Relationship table.
Set fldCS(0) = tblS.CreateField("CandidateSkillKey", dbLong)
fldCS(0).Attributes = dbAutoIncrField
Set fldCS(1) = tblS.CreateField("CandidateKey", dbLong)
Set fldCS(2) = tblS.CreateField("SkillKey", dbLong)
'Now append fields to Candidate table and then to DB.
tblC.Fields.Append fldC(0)
tblC.Fields.Append fldC(1)
tblC.Fields.Append fldC(2)
MyDB.TableDefs.Append tblC
'Now append fields to Skills table and then to DB.
tblS.Fields.Append fldS(0)
tblS.Fields.Append fldS(1)
MyDB.TableDefs.Append tblS
'Now append fields to Relationship table and then to DB.
tblCS.Fields.Append fldCS(0)
tblCS.Fields.Append fldCS(1)
tblCS.Fields.Append fldCS(2)
MyDB.TableDefs.Append tblCS
'Now add an index to the Candidates table.
Set idxC = tblC.CREATEINDEX("idxCandidateKey")
idxC.PRIMARY = True
idxC.UNIQUE = True
Set fldTemp = idxC.CreateField("CandidateKey")
idxC.Fields.Append fldTemp
tblC.Indexes.Append idxC
'Now add an index to the Skills table.
Set idxS = tblS.CREATEINDEX("idxSkillKey")
idxS.PRIMARY = True
idxS.UNIQUE = True
Set fldTemp = idxS.CreateField("SkillKey")
idxS.Fields.Append fldTemp
tblS.Indexes.Append idxS
'Now add an index to the relationship table.
Set idxCS = tblCS.CREATEINDEX("idxCandidateSkillKey")
idxCS.PRIMARY = True
idxCS.UNIQUE = True
Set fldTemp = idxCS.CreateField("CandidateSkillKey")
idxCS.Fields.Append fldTemp
tblCS.Indexes.Append idxCS
'Create relationship C to CS
Dim relCtoCS As Relation
Set relCtoCS = MyDB.CreateRelation("CtoCS")
relCtoCS.TABLE = "tblCandidate"
relCtoCS.ForeignTable = "tblCandidateSkill"
Set fldTemp = relCtoCS.CreateField("CandidateKey")
fldTemp.ForeignName = "CandidateKey"
relCtoCS.Fields.Append fldTemp
MyDB.Relations.Append relCtoCS
'Create relationship S to CS
Dim relStoCS As Relation
Set relStoCS = MyDB.CreateRelation("StoCS")
relStoCS.TABLE = "tblCandidate"
relStoCS.ForeignTable = "tblCandidateSkill"
Set fldTemp = relStoCS.CreateField("SkillKey")
fldTemp.ForeignName = "SkillKey"
relStoCS.Fields.Append fldTemp
MyDB.Relations.Append relStoCS
End Sub
NOTE
As Figure 31.10 illustrates, Listing 31.3 creates three tables in the database and each has a defined relationship to one of the others. Because Visual Basic 4.0 does not support the creation of non-Jet engine databases, you have to use other tools to
create an initial database. In the case of some ISAM databases, this is just a matter of creating a blank directory and placing the individual database files into it.
NOTE
TIP
There are fundamentally two ways to modify the structure of existing databases within Visual Basic 4.0 if you choose not to use a dedicated database tool. One way is to use Visual Basic 4.0 database methods, and the other is to use SQL action queries.
The Visual Basic methods are the same ones employed in the CreateTestDB() function in Listing 31.3.
Visual Basic 4.0 does not support action queries that modify database structure, so any SQL commands you use for that purpose have to be handled entirely by your RDBMS. The SQL action queries would be of the Alter Table variety, but would depend on the
RDBMS you use.
Visual Basic 4.0 programs can work with database data through native Visual Basic methods, SQL action queries, or OLE 2. Although Oracle has released an OLE 2 interface to its RDBMS, ODBC and native Visual Basic methods are the more common ways to
interact with database data; those ways are focused on in this section.
If you have the professional version of Visual Basic 4.0, you can open database objects entirely through code. With both the standard and professional versions, you can open database objects through the data control. Details of the data control are
covered in Chapter 27, so I won't cover that here. Instead, let's jump right into working with the BTS database to create and edit tblCandidate records:
FIGURE 31.11. The Data control.
FIGURE 31.12. The final screen.
NOTE
To perform batch updates on your database, SQL is sometimes a good choice. Visual Basic 4.0 SQL is covered in detail in Chapter 32. Only a simple example is shown here to illustrate one way to execute such a query.
Follow these steps using the add/edit/delete/undo form created earlier. These steps will add SQL batch processing to the form:
Now when you run the form, you have a "Kill" button. Click on it to delete all the records in the tblCandidates table.
Whatever database scheme you implement, periodic backup is cheap insurance that can pay huge dividends should anything ever go wrong. Backup strategies depend on the type of database and the circumstances of the implementation.
If your application uses an ISAM database, backup can be as simple as a periodic PKZIP of the database files. In the case of a shared ISAM database, all users have to log out during this process. If you have the space for it, this is a good idea even on
systems that have tape backups, because backup tapes can be somewhat unreliable.
Client/server RDBMSs can be more complex to back up, and the process may be unique to the hardware on which your RDBMS resides. Although a well-designed RDBMS can be very stable, not keeping current backups can be foolish.
There are two types of database corruption: physical and logical. A physical database corruption is when the format of the database files are in some way adulterated. A logical corruption is when a relationship constraint is not preserved or a table
restriction is not adhered to. An example of a logical corruption is leaving a field blank when the database requires a non-null value. Examples of physical corruption are a malformed record or an improperly updated index.
As discussed earlier, shared ISAM databases are physically manipulated by the users' machines. In the case of Visual Basic 4.0 database programs, the manipulations are through the Jet Engine or an ODBC driver. If the user computer has a malfunction
during a write operation or some user software derails and writes inappropriate data to the shared database files, a database corruption can take place.
Some database corruptions can linger undetected for some time and cause other corruptions and user program crashes. A good practice for any ISAM database, and in particular any shared ISAM databases, is to periodically "repair" them. For
non-Access (MDB) databases, you need to use an appropriate utility for that database type or system. In the case of DBF type databases, a common repair function is a "pack." (Pack in the xBase world removes records marked for deletion and
rebuilds indexes.) Visual Basic 4.0 does not include a pack utility. It can, however, repair Access databases with the DBEngine.CompactDatabase method. There is no harm in repairing a healthy database, but there is much potential harm in letting a corrupt
database continue to serve application users.
Unless a logical database corruption contradicts a stored logical definition in the database itself, a general-purpose repair utility will not detect it. (An example of a logical requirement that is not defined in a database would be in the case of a
dBASE database where one table record depends on the existence of a record in another table. Because dBASE databases do not store any relationship information, a pack or third party repair program would have no way of knowing this requirement.) For this
reason, it is always a good idea to define all relationships and database constraints within the database instead of within your program code whenever possible. Some ISAM databases, such as the DBF type, do not support constraint storage, but the Access
(MDB) type does. All client/server RDBMS systems support constraint definition within the database.
This chapter introduced some database fundamentals, covered some practical considerations, and touched on the power of SQL. For more details on the SQL language and discussion on how to apply it, turn to Chapter 32.
