Using Collections in Visual Basic #2 - Jet Database Collections

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.

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.

?á

Take this example:

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

?á

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!).

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:

dbCustomers.TableDefs("Customers")

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:

End Sub

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:

?á

dbCustomers.TableDefs("Customers")

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 properties...you 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.

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:

End Sub

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

to

Your debug window will contain something like this:

Customers

LastName

FirstName

PhoneNumber

Orders

OrderNumber

Amount

Date

....

?á

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:

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:

Or

Have fun!