NOVEMBER, 1996
by
OK, so you have Visual Basic on your machine and have written a couple of applications. No big deal so far, but you would like to begin taking advantage of some of the nice features of VB.
Where shall we start? How about databases? If you have the Professional or Enterprise editions of VB, then this capability has been at your fingertips all along.
The remainder of this article will take us through the process of building a simple database application. We will take this in reasonably small steps and sequentially add code to the subroutines and functions. I find this a better way to instruct than to provide a long code listing and have to explain it line by line. Most of us do not program sequentially. We start with a framework and add modules as they are required.
All code was developed in VB3 Pro. It should work on VB4.
The emphasis here is on a simple database application. Database programming is an extremely complicated subject about which we could go on a length. This will only get you started. I suspect, though, that you will easily find more application for database programming in your environment.
For those of you who have already done database programming, skip ahead five paragraphs. For the rest of you, a couple of quick words about databases are in order.
Most applications retrieve data, process it, and store it for possible additional processing later on. In reasonably simple applications you could use a flat, ASCII file and have an application which sequentially reads through the data. If the data is in mixed formats (e.g. strings and integers and currency) then you may use a typedef statement to organize information into "records" and use a get or put call to read or write your data.
For more complicated applications or if you have large amounts of data, you may want to employ a database to manage the underlying storage and retrieval of the data. Databases allow you to define what the data is to look like (text, integers, long, etc.), and what the relationships are between the data. This is all done without you having to worry about how the data is stored or any of the minor details of data organization.
Databases allow you to process data in two different ways. First, the data may be accessed "sequentially". For example, list all customers by customer number. Secondly, the data may be accessed in the aggregate, or in some way that applies to all the data. For example, tell me the average batting average of all left fielders. Obviously, these two "methods" can be combined to get all customers and their addresses along with the amount their credit balance differs from the average credit balance of all customers.
There are many good database packages available on the market. The choice of a particular database depends on the need for some specific functionality within a specific database, requirements made by a customer, and on what you have available. VB comes with the same database engine used in Microsoft Access ®.
For our example program, we will create a VB program which will act against an Access database. We will create code to open and close the database, access records, add, update, and delete. If you want to use a different database (like dBASE, FoxPro, or something else), you will have to make modifications in some of the code which follows. I refer you to the manuals. The examples we use are taken from a larger database application I recently wrote. In some cases the code may not be the best, but it does properly illustrate the discussion.
Very little error checking is included in our examples. In the real world, you would obviously put in logic to determine and possibly correct errors. It is important. It is also outside the scope of this discussion.
After every time we add code to our program, you are encouraged to re-run the program. This allows you see sequentially what has been added and what functionality it adds. The code is not particularly difficult and is well within the reach of most programmers.
To start, you must create a database. If you already have a copy of Access installed, use it. If not, compile a copy of the VisData program. It should be in the samples section of VB. Create a database called "MyDB.mdb" as an Access database. Create two tables in the database using the following information:
1 2 Table Name RootTbl TypeTbl Item1 Name RootCode TypeCode Item1 Type Text Integer Item1 Length 4 --- Item2 Name RootName Type Item2 Type Text Text Item2 Length 30 20 Index Name RootIndex TypeIndex Index On RootCode TypeCode
Enter some data into the two tables. The first contains a list for codes for Internet root domains (com, net, etc.) while the second contains a type code along with an associated textual description.
As a minimum, put at least this much information into the tables. You may make different specific records if you prefer. These will be used in our discussion.
RootTable com Commercial net Network Provider edu Educational Institution TypeTable 1 International 2 US Only 3 Eastern US 4 Local
In VB, create a form. The specific layout is not important at this time. Pick something pleasing to the eye.
This form should have three input boxes, InputRootCode, InputRootName, and InputTypeCode. If you wish, create text boxes to act as labels for these input boxes. The form should also have nine buttons BtnFirst, BtnLast, BtnNext, BtnPrevious, BtnAdd, BtnDelete, BtnUpdate, BtnFind, and BtnExit. Lable the captions on the buttons appropriately.
First, add this code to BtnExit_Click().
End
This gets us out of the running program. Give it a shot with the famed F5 method.
Obviously, this doesn't really get us very far. We need to be able to actually get to the data. In the declarations section of the form, add
Dim DB as Database Dim RTable as Table Dim TTable as Table
This creates variables to assign the results of database operations.
In the Form_Load() subroutine, add the following code:
Set DB = OpenDatabase("MyDB.mdb") Set RTable = DB.OpenTable("RootTbl") Set TTable = DB.OpenTable("TypeTbl")
This 1) opens the database, 2) opens the RootTbl table, and 3) opens the TypeTbl table. It doesn't do anything with them, but you cannot do anything further with database files and tables until you open them. For those of you who used something other than Access, you will need to modify the OpenDatabase statement. I refer you to the OpenDatabase command in the manual.
In order to keep things tidy (and because it's good practice), add this code to the BtnExit_Click() event:
Sub BtnExit_Click() TTable.Close RTable.Close DB.Close End End Sub
You must do it in this order. First close the tables, then close the database. The tables can be closed in any order, as long as it's before the database is closed. If you forget to close the tables first, you will get an error on the database close statement.
Again, the program should run at this point. It doesn't do anything very interesting to the untrained eye, but it does run.
Now we need to make it do interesting things.
Recall from above that databases allow you to process data either sequentially or in the aggregate. We will be dealing with the data in a sequential order, one record at a time. This is not to say we will not be able to randomly get to different records, only that we won't be interested in gross data. That is left to a further discussion.
In reading through a database, we introduce the concept of a "current record". That is just a fancy phrase for the specific data I am dealing with right now.
In BtnFirst_Click(), add the following code
RTable.FindFirst Call Get_Fields
In BtnLast_Click(), add
RTable.FindLast Call Get_Fields
In BtnNext_Click(), add
RTable.FindNext Call Get_Fields
In BtnPrev_Click(), add
RTable.FindPrevious Call GetFields
In Form_Load(), add
Call BtnFirst_Click
Add the Call statement in Form_Load() after the tables have been opened.
Create a new subroutine
Sub GetFields() InputRootCode.Text = RTable.Fields("RootCode") InputRootName.Text = RTable.Fields("RootName") End Sub
What we have done is to associate actions with the direction buttons (first, last, next, and previous). The program will issue a call to get the proper record then call a subroutine to display the data onto the screen (the GetFields subroutine). There we get data from one of the fields of the table and assign it to an input box on the form.
It should be noted at this point that we could have as easily extracted data from RTable.Fields(1) and RTable.Fields(2). That method says to get information from the first and second fields in the table. This requires you to know in exactly what order the fields in the database were created. If you get a database file from someone else and they didn't define the data in exactly the order you thought they did, you will get erroneous results. This is one of the advantages of a good database driver. It frees you from having to know everything about the underlying data structure.
The call to BtnFirst_Click() in the Form_Load() event gets us to the first record in the table and essentially "seeds" the display.
But, when you run this program, you will notice a problem. As you click on BtnNext, records appear to come out in random order. In fact, they are being accessed in the order in which you input them. To have them come out in a sorted order, we must add an index to the table. In the Form_Load() event, enter this code
Set TTable = DB.OpenTable("TypeTbl") RTable.Index = "RootIndex" TTable.Index = "TypeIndex" Call BtnFirst_Click
Data now comes out in the "proper" order.
What we have now will in fact work, provided we push buttons in the proper order. If we do not, we will generate an error.
For example, if you click on BtnLast you get the last record in the table. Hitting BtnNext to get the subsequent record takes you past the last record or past the end of the file. This is considered a database faux pas and is frowned upon.
Add the following code to BtnPrev_Click(),
RTable.FindPrevious If RTable.BOF Then RTable.FindFirst Call GetFields
In BtnNext_Click(), add this code
RTable.FindNext If RTable.EOF Then RTable.FindLast Call GetFields
What we have done is to say that if we have reached the beginning of the file (RTable.BOF), then we should actually go and get the first record. If we have reached the end of the file, we should instead get the last record.
Obviously, we could make this a lot fancier. If we click on BtnFirst, we position ourselves at the beginning of the file. We know there is no previous record, so we declare BtnPrev.Enabled = False. Similar logic works for BtnLast. If we click on BtnPrev then we know there must be a next record, so we would declare BtnNext.Enabled = True. If you draw this on paper, you will see that this logic works even if you only have a single record in the table.
This is not a direct part of this example set and is left as an exercise for the reader. However, it should not be easily ignored. End users like applications which help them get through the flow of an automated process. If a button is disabled, obviously they can't hit it. If I, as an end user, hit the "Previous" button and keep getting the same (first) record, it is slightly irritating. Eventually I will figure it out, but why make the software sale any more difficult? With four lines of code, you end up with happier users.
As an aside, you eventually will have to take care of the case where you have zero records in a table. In that case both the BOF and EOF conditions will be true. Hitting one of the direction buttons will generate an error. I recommend you investigate the RecordCount property of a table.
So far we have taken care of five of our nine buttons. Let's do a sixth.
The easiest to do is deletion. Add this code into BtnDelete_Click(),
RTable.Delete Call BtnNext_Click
That's all there is to it. The current record is deleted then the next record is retrieved (and displayed). It is important we make an explicit call to get the next record. Otherwise, what the program believes to be the current record will be pointing to an area in memory which no longer exists. This would cause us major problems if we were to try to update information.
By means of the work above, the remaining details such as actually putting the information from the current record onto the form are handled properly. Additionally, if we deleted the last record then the penultimate record becomes the current record. There was in fact a method to our madness.
Having learned how to delete data from our tables, let's learn how to add a data to our database.
At this point we run into a minor shortcoming in our form design. We have an "Add" button. When the user clicks on it, they need to be given a blank form to fill out. When they have done this, how do they tell the program they have completed filling out the form and want to actually add the date to the database. There are several ways around this problem. We will take an easy way out of the problem and make the user hit the "Add" button at both the beginning and the end of the addition process. This means we have to keep track of if we are or are not already in the addition process. To do this, we will declare a global variable and let it keep track of things.
Add this definition to the declaration section of the form.
Global IsAdding as Integer
Now add this code the BtnAdd_Click() subroutine
Sub BtnAdd_Click() Dim Results as Integer Results = Verify_Data() If Results = False Then MsgBox "Invalid data" Goto Tag1 End If If IsAdding = True Then ' already adding RTable.AddNew ' BUG - doesn't trap for dupes RTable("RootCode") = InputRootCode.Text RTable("RootName") = InputRootName.Text RTable.Update End If InputRootCode.Text = "" InputRootName.Text = "" InputRootCode.SetFocus IsAdding = True Tag1: End Sub
And add this new function
Function Verify_Data() as Integer Dim RetCode as Integer RetCode = True If Trim$(InputRootCode.Text) = "" Then RetCode = False End If If Trim$(InputRootName.Text) = "" Then RetCode = False End If Verify_Data = RetCode End Sub
At this point we can add new records to our database. But before you hit the F5 key, let us look at what we just did.
In BtnAdd_Click() we first made a call to Verify_Data(). This does some minimal checks to make sure the data on the form is reasonable. We do it at the very beginning of the subroutine. Obviously, the first time through, it will verify the current record. That is OK because an already existing record will pass through our elementary checks.
Next, we check to see if we are already in add mode (IsAdding = True). If so, we issue a call to add a new record to the table (RTable.AddNew). Then we add data for the two fields in the record. Finally, we issue a call to update the table. If you add a new record to a table, we have to tell the table to incorporate the new data into the data stream.
Finally, we clear out the fields on the form to allow the user to input (more) data.
The Verify_Data() subroutine only makes minimal checks. Obviously, we should check to make sure we have not added duplicate data and other things of that nature. By the time you finish this article, this capability should be well within your reach.
OK, add some data now.
But, we have an ancillary problem. Notice the IsAdding variable is never set to False. This means we are apparently always in add mode. In truth, due to the way we have defined our algorithm, we are in add mode only until we hit some other key. Therefore, in all the other "direction" subroutines, we need to turn off IsAdding.
In all of BtnFirst_Click(), BtnLast_Click(), BtnNext_Click(), and BtnPrev_Click(), add this line at the top of the subroutine:
IsAdding = False
In BtnDelete_Click(), we should now ignore the delete operation if we are in add mode. Add this code in that subroutine.
If IsAdding = False RTable.Delete Call BtnNext_Click End If
Now, things should work properly.
Updating records (our eighth button) is equally easy.
Make BtnUpdate_Click() look like this:
Sub BtnUpdate_Click () Dim Results As Integer If IsAdding = True Then Call BtnAdd_Click Else Results = Verify_Data() If Results = True Then RTable.Edit RTable.Fields("RootName") = InputRootName.Text RTable.Update End If End If End Sub
We have done some interesting things with our algorithm.
If we are in add mode (IsAdding = True) then the user was adding data and hit the "Update" button instead of the "Add" button. Rather than penalize them for an admittedly minor mistake, we presume they want to keep adding data so we just act as if they really hit the "Add" button.
If they had hit "Update" button at some other time, we tell the database to get ready to change data (RTable.Edit). Then we update the data field itself. Finally, we tell the database to flush its buffers and actually make the changes to the table.
You will notice we only changed the data field. We did not modify the key field. This was done by choice and was not a requirement of a database program. Rather than allow the user to change the key field, we require them to delete the old record and go into the add cycle separately. If you want to get around this restriction, add another RTable.Fields line in BtnUpdate_Click().
And now our last button, the one labeled "Find". This will allow us to get to some random record rather than having to go through the file sequentially.
Add this code to BtnFind_Click()
Sub BtnFind_Click () Dim cSeek As String Dim BkMrk As String cSeek = InputBox$("Enter Code To Find", "Look for code") If Len(cSeek) <> 0 Then BkMrk = RTable.Bookmark RTable.Seek "=", cSeek If RTable.NoMatch = False Then Call GetFields Else MsgBox "can't find record", 1, cSeek If IsAdding = True Then Call BtnFirst_Click Else RTable.Bookmark = BkMrk Call GetFields End If End If End If IsAdding = False End Sub
Again, what we put in was not rocket science.
An InputBox$() function is used to get some string for which to search. If we entered something (Len(...), we make a notation of the current record. This is done via a database construct known as a "bookmark". This allows us to quickly go back to some specific place in the database. We then use a seek method to look for a record that which the string you just entered as its key field.
If we don't find something during a seek, the NoMatch property of the table will be set to True. Conversely, if we find something, the NoMatch property is set to False. In this case, we simply display the data (the first GetFields line). There is no explicit Match property. A match is defined as a false NoMatch. Perhaps a logical inversion but not an unreasonable one.
If we didn't find a record (the Else case), we output a message to the user. At this point we need to find someplace to get data for the display area. If we were in add mode, we simply get the first record in the table. This is strictly a personal thing. We could have as easily gone to the last record or wherever you wanted. But, if we were NOT in add mode, we want to go back to the record which had been previously displayed. This is the record we had bookmarked at the top of the subroutine. Either way, we display the data (the second GetFields line).
At the end of the subroutine, we exit add mode. This has to be done at the end of the process because we are relying on an add mode test during the subroutine.
Some of you will obviously complain that we only looked for a key string (and a complete one at that!). What if I want to look for something in the data field or look for a sub-string of the key field? Well, actually that is a more difficult undertaking and is outside the scope of this discussion. Until then I refer you to the manuals.
At this point we have done all of the basic operations to operate against a database from within VB. We can add, delete, and access information within the files. These are enough to get you started.
But, to make sure we have successful, it's time for the final exam.
At the beginning we had you create and populate two tables in our example database. So far, all of our activity has been against only one of these tables, the RootTable. We have totally ignored the TypeTable. In addition, we defined three input boxes but so far have only used two of them. Have we completely forgotten about the InputTypeCode box?
No, we hadn't forgotten anything. We saved them for now.
Let us make an addition algorithmic definition at this point. For the user to successfully add a new record, they must enter a valid type code as stored in the TypeTable into the InputTypeCode box. The type code will only be used to verify. It will not be stored in the RootTable Table.
For your final exam, add the code to accomplish this. You have all of the information you need to do this (actually we have effectively done this already.) When you have successfully done this, come back and read to the end of the article.
OK, go away now and finish your exam.
...
OK, come back.
Did it work?
There are several ways to do this task. I will show you my way. If your way differs from mine, that is fine. Code doesn't have to be elegant, it just has to work.
Our exam is actually just a special case of the "Find" operation. The main difference is that we don't care about a current record in the TypeTable. We just care about the NoMatch information.
Add code to Verify_Data() to make it look like:
Function Verify_Data() as Integer Dim RetCode as Integer Dim i1 as Integer ' NEW STUFF RetCode = True if IsAdding = True Then ' NEW STUFF i1 = Val(InputTypeCode.Text) ' NEW STUFF TTable.Seek "=", i1 ' NEW STUFF If RTable.NoMatch = True Then ' NEW STUFF MsgBox "Invalid type code" ' NEW STUFF RetCode = False ' NEW STUFF End If ' NEW STUFF End If ' NEW STUFF If Trim$(InputRootCode.Text) = "" Then RetCode = False End If If Trim$(InputRootName.Text) = "" Then RetCode = False End If Verify_Data = RetCode End Sub
Nothing major. Only 9 lines of new code.
The code is added to Verify_Data() because that is an easy place to put it. We could have added it to BtnAdd_Click().
Define an new integer, i1. We need an integer rather than a string because the key field in the TypeTable is an integer. We will need an integer for our search value.
If we are in add mode (IsAdding = True), then we will "verify" the type code information. If you recall from above, there are several ways we might have gotten into this subroutine (or we might want to reuse this code later). We only want to check if we are adding. Change this if you want to redefine the addition algorithm.
Next we look up the type code in the Type table. If the NoMatch property is True, we didn't have a valid type code. We output a MsgBox and set the return code (RetCode) to False. If we found a match we just go on.
Did you pass the final? Mark your grade in the grade book.
As I said above, this isn't rocket science. We have learned some new constructs and had to do a couple of new logic things which might not occur in a non-database application. But we really haven't tossed that much at you. Beginning database programming should be well within the reach of any reasonably competent VB aficionado.
The example program we produced had some shortcomings.
For example, we do not check that the input from the InputTypeCode
box is in fact numeric. Neither do we check for an existing record
when we add a new record. Some of the problems
are fairly obvious and are noted in the code. Others won't become
obvious until you have played with the code for a while. Many of the
problems can easily be fixed with ordinary (non-database) VB code.
Some will require database code. Go through and try to make the code
more bombproof. You will learn more by doing than you will by reading
this paper.
Look at the VB manuals and see if you can get further along. Book 2 of the "Professional Features" manual contains a discussion on database programming. It should give you additional information.
Look at "Language Reference Manual" for specific information on the constructs we used in our examples. Follow the links in the See Also sections to gain more information and to see where that command, method, or object fits into the overall scheme of things.
There are a variety of VB manuals available at most reasonably stocked
technical book stores. Unfortunately, most manuals do not discuss
database programming in great detail. Look for books which contain
explicit sections about or which are devoted to VB database programming.
This is a list of the new constructs and concepts we used in our discussion above. Some are logical extensions of commands, methods and objects you probably already use. Our examples have used a limited subset of database programming methods. Some of them operate against a variety of objects. Again, you are referred to the manuals for more information.
Database object - a collection of data related to some topic or purpose. A database can contain tables, queries, and indexes.
Table object - a type of recordset that is part of a database that contains data about some subject.
Current record - the data being dealt with right now.
Set - assign an object reference to a variable
OpenDatabase(databasename) - open an existing database
database.OpenTable(tablename) - open an existing table
table.Close - close a table object
database.Close - close a database object
table.FindFirst - locate the first record in a recordset (go to beginning)
table.FindLast - locate the last record in a recordset (go to end)
table.FindNext - locate the next record in a recordset (read forward)
table.FindPrevious - locate the previous record in a recordset (read backwards)
table.Fields(Fieldname) - the data in a record (and how you get to it).
table.Index = indexfield - set the current index for a table.
table.BOF - a value which is set to indicate whether the beginning of file has been reached.
table.EOF - a value which is set to indicate whether the end of file has been reached.
table.AddNew - clear the copy buffer in preparation for creating a new record.
table.Delete - delete the current record.
table.Update - save the copy buffer to a specified table.
table.Edit - open the current record in a specified recordset for editing by copying it to the copy buffer.
table.Bookmark - determines/sets the current record.
table.Seek - locate a record in an indexed table.
table.NoMatch - indicates whether a find or seek method was
successful.
The author is an admitted Unix bigot who uses VB on his home DOS box because, as he puts it, "It does what I need it to do." He has been doing VB since 1992 and has produced a variety independent applications for a number of clients. He works as a sysadm for a three letter phone company.
Click here to go back to the November '96 Article Index
Copyright © 1996 VB Online and VB'ers Electronic Magazine. All rights reserved.