Search Tools Links Login

Using Collections in Visual Basic #2 - Jet Database Collections

This article attempts to explain the Microsoft Jet collections and how you can use them in really useful ways. If you don't know about Jet collections, this is well worth reading.

Original Author: Matt Roberts


Jet Collections

Using Collections in Visual Basic

Part 2 - Jet Database Collections


Most Visual Basic developers are familiar with the Jet Database Engine. While it receives a lot of flack from developers who work with more powerful systems such as Oracle or SQL Server, Jet has a lot of really good features that make it ideal for a desktop application. Besides, we VB Developers are used to sneers and comments from "hard core" language programmers. "What is that? A string parameter? Why, in C++, we don't pass strings! We pass pointers to memory addresses that contain null terminated string arrays! That's how real men handle strings!"

Yea, whatever.

Obviously we VB developers aren't interested in doing things the hard way, and Jet is a wonderful way to avoid it while still having a high level of control over your data. Before I go into the wonderful benefits of the Jet Database Engine, I think it is appropriate to point out that if you are in the habit of using data controls on your forms to access databases, you are greatly limiting your freedom to work with data, and you are bypassing many of the most useful things about Jet. At the risk of sounding like the C++ developer I was just making fun of, you really should take the time to learn DAO or ADO. If there is any interest in a general "This is how you use DAO/ADO" tutorial out there, let me know and I will work one up.

This tutorial won't go over how to use DAO or ADO for data access. Since DAO seems to be the most common method for accessing Jet data right now, I will give all of my code examples in DAO. If you don't know how to use DAO yet, maybe this article will convince you that it is worth learning. There have been entire books written on using the Jet Database Engine, so to try to cover the "how to" basics AND Collections here would get pretty long winded. So I am going to stick to collections.

Now, on with the tutorial:

Microsoft Jet is not actually a thing. It is more like a format. A Jet database consists of a single file with many internal elements. You Access Developers out there will be familiar with the concept of a single Access .mdb file containing many different objects. While it is useful in Access to have forms, macros, and reports in a single file, it is kind of pointless with Visual Basic. You have no way to use those objects from the VB environment, so they are just filler. Therefore, we are going to focus on the Table and Query objects. Don't get too hung up on how Jet stores all of these things in a single file and keeps up with it all, just trust that it does and go with it. For the technically curious, .mdb files are similar to a miniature file system within a single file "wrapper".

So, on to the meat of this thing. Jet Database Collections.

If you read my other tutorial on collections, or if you have worked with collections before, this will not seem totally new to you. If not, you can probably hang in there anyway. These examples aren't tough.

Microsoft Jet is, as mentioned, a collection of database objects in a single file. These objects have a hierarchy. This just means that there are top level and lower level members, and the top level ones "contain" lower level ones. In Jet, the highest level object is the Database object. It is, for all practical purposes, the file itself. Think of it as a big box. Within that box we see other objects. The ones we are concerned with are Tables and Queries.

When Jet stores a table or query, it actually stores a set of information that acts as a table definition. It describes the table to the Jet database engine, and the Jet engine creates it when it needs it. Think of it as a template. The names Microsoft chose to give these objects are a little puzzling unless you know that they are definitions. They are called TableDefs and QueryDefs. They are essentially identical from the collections point of view, so we will concentrate on tablesdefs and then wrap it up with querydefs.

So enough of this technical stuff, how about some code.

(NOTE: DAO requires a reference to the Data Access Object in the References of your project. For information on how to add a reference to DAO, see VB Help and search for "Creating a Reference to an Object))


Take this example:

Private Sub ShowCustomers()

Dim dbCustomers As Database

Dim rsCustomers As Recordset

' Create your data objects and open the table "Customers"

Set dbCustomers = OpenDatabase ("C:Program FilesCustomerInfoCustomers.mdb")

Set rsCustomers = dbCustomers.OpenRecordset ("Customers")

' List some information from the database to the debug window

Debug.Print rsCustomers!LastName

Debug.Print rsCustomers!FirstName

Debug.Print rsCustomers!PhoneNumber


' Always clean up after you are done!

set dbCustomers = Nothing

set rsCustomers = Nothing

End Sub

This sub simply opens a Jet database and displays three values from it. This is pretty easy and straightforward. But there is a problem with this type of code. You have to have prior knowledge of what is in the data file. You have to know the table name, and within the table, you have to know the field names. You may even need to know if those fields are number or string fields. Is PhoneNumber a string or Long datatype? How can you tell? Do we even care?

Answer: Probably not. Most of the time that we access databases, we already know the field names and datatypes. So what is my point? My point is, you may not know. I recently created a small project that would allow you to select a table from an Access .mdb file and view all of the data and in a grid. There is no possible way I could know what any random database file is going to contain. There could be any number of tables with any names, and each of those tables could have any arrangement of fields. Obviously there is a way to get to that sort of information in code without knowing it in advance. Either that or my project was a miserable failure, and I can tell you it wasn't...just a modest one. There is a way to examine any Jet database and determine its elements. This method is collections (FINALLY!).

If you remember, I said earlier that the highest level object in a Jet database is the Database object. That means that if we want to refer to anything within the database, you must reference it THROUGH this object. But how do you do THAT? We already have. Look at the code above and you will see this line:

Set rsCustomers = dbCustomers.OpenRecordset ("Customers")

This line tells VB to create a new Recordset object based on dbCustomers, using the table Customers. To refer directly to that table in code, you could use this syntax:


Because what you are really telling it to do is to look at the table named "Customers", which is part of the TableDefs COLLECTION in the database dbCustomers. The TableDefs collection contains all of the tables in the database...even the super-secret hidden ones that Jet uses internally to manage the data. Hidden tables will begin with mSys. You will see them later on.

But wait! In the example above, I just did it the hard way. I still had to know the name of the table...or did I? Although you can refer to the tables in the manner that I did, you don't have to. All collections in Visual Basic are enumerated. That means that they are basically a glorified array. And as you know, you can refer to the elements of an array with an index number. For example, to find out what the 5the element in a string array is, you could do this:

strTest = strTestArray(4)

(Remember, arrays are zero-based unless you specify the Option Base explicitly...just a reminder).

So to get the first element in the array, you could say this:

strTest = strTestArray(0)

Easy, right? Well then you have got the concept. You can reference tables in a Jet database the same way:

strTableName = dbCustomers!TableDefs(0).Name

Debug.Print strTableName

This will return the name of the table - Customers.

Wait! This is getting cool! That means that if you know the index number, you can get the name! But how can I know the index of a particular table? You can't. But as you will see, it doesn't matter, because you can use the For...Next command to go through them all.

Check this out:

Private Sub ListTables()

Dim dbTableList As Database

Dim intTableNumber As Integer

Dim strTableName as String

Set dbTableList = OpenDatabase ("C:program filescustomerinfo customers.mdb")

For intTableNumber = 0 To dbTableList.TableDefs.Count - 1

strTableName = dbTableList.TableDefs(intTableNumber).Name

Debug.Print strTableName

Next intTableNumber

End Sub

There are a couple of things to note here. The first is that I used 0 to dbTableList.TableDefs.Count -1. All collections have a built-in property "Count" which contains the number of elements in the collection. This is just like the Recordset's RecordCount property. If you have ever done this:

intRecords = rsCustomers.RecordCount

Then you have used the Count property. It always returns a number equal to the number of elements. If there are no elements, it will return 0.

The next thing to note is the use of the Name property. As with all object in VB, each element in the TableDefs collection can have an associated Name. This is exactly what you were referring to earlier when you said:



So now you see how you can get the names of tables without any prior knowledge of the database. You can also get other properties from them such as RecordCount. Take some time to explore all of the available may be surprised.

We now have a big part of the problem whipped. We can go into a table and list the table names in code. Cool. But what about fields? Trust me, it is EXACTLY the same concept.

For intFieldNumber = 0 to rsCustomers.Fields.Count - 1

strFieldName = rsFieldList.Fields.Name

Debug.Print strFieldName

Next intFieldNumber

This works because the TableDef object contains a Fields collection. You could combine the two examples and get a list of EVERY FIELD in EVERY TABLE in your database. Try it:

Private Sub ListAllTables()

Dim dbTableList As Database

Dim strTableName as String

Dim intTableNumber As Integer

Dim strFieldName As String

Dim intFieldNumber As Integer

Set dbTableList = OpenDatabase ("C:program filescustomerinfocustomers.mdb")

For intTableNumber = 0 To dbTableList.TableDefs.Count - 1

strTableName = dbTableList.TableDefs.Name

Debug.Print strTableName

For intFieldNumber = 0 To rsCustomers.Fields.Count - 1

strFieldName = rsFieldList.Fields.Name

Debug.Print strFieldName

Next intFieldNumber

intFieldNumber = 0

Next intTableNumber

End Sub

How about that! You can make it look a little neater by adding an indention for the fields. Just change this line:

Debug.Print strFieldName


Debug.Print & " " & strFieldName

Your debug window will contain something like this:











I could go on with examples, but I bet you get the idea now. I will get your curiosity up by telling you that the Field object also contains a Properties collection. It has such information as Data Type, Length, Name, etc. That is how you were able to get the name of the field. You can access this collection like this:

strFieldName = rsFieldList.Fields.Properties(2).Name

strFieldName = rsFieldList.Fields.Properties(2).Length

With that I will turn you loose to go experiment on your own. I am including the application that I wrote. It is VERY well commented, so maybe you can see how all of this database collections stuff is put to work.

By the way, I mentioned QueryDefs as well as TableDefs. Basically, the only difference is that you reference a saved query by referencing the QueryDefs collection instead of the TableDefs collection. Example:

Set rsCustomers = dbCustomers.QueryDefs("Customers")


Set rsCustomers = dbCustomers.QueryDefs(2)



Have fun!

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 96 times


Visual Basic 6

Posted: 9/3/2020 3:45:00 PM
Size: 6,677 bytes

Loading Comments ...


No comments have been added for this post.

You must be logged in to make a comment.