You design databases using special tools and VisualData Manager is one of the simplest. These tools let you examine and modify the structure of the database, and in some cases, enter data. You can also access the structure of a database from within an application through a series of objects.
In this section we will develop a utility that maps the structure of any data~ base, and in the’process, you’ll see how these objects are used. These objects form. a hierarchy, starting with the Database object at the top, and each element in the database is-represented by a unique object that can be accessed as a property of the Database object.
The Database Object
In the previous sections, we focused on the contents of a database. Now let’s shift our attention to the structure of the database. Even if you know the structure of a database, in some situations you must access its table fields and indices from within your code: For example, you might need to know how many fields a particular table
contains or how an index file is defined.
The Database Structure application, Shown in Figure 17.24, demonstrates all the objects discussed in this section. First, I’ll explain the objects that make up the data, base, and then we’ll look at the implementation of the Database Structure application.
The top-level object is the Database object, which represents the database. To create a Database object and assign a database to it, declare an object variable with a statement like the following:
Dim DB As Database
The DB variable is assigned a Database object with the Open Database function, which accepts the path Mine of an existing database as argument and returns a Database object, as in the following:
Set DB = OpenDatabase(dbName) .
The dbName argument is the path name of the database and could be the FileName property of-the Common Dialogs control. All the objects we explore in the following sections are properties of the Database object.
Each table in a database is represented by a TableDef object, and a query is represented by a QueryDef object. Each of these objects has its own properties, such as the Name property (which is the table’s or query’s name) andthe Count property (which returns the number of tables in the TableDef object and the number of queries in the QueryDef object) .
The TableDef Object: The Tables Definitions
A TableDef object represents the definition of a table in ‘the Database object. All TableDef objects from a collection, the TableDefs collection. The number of tables isgiven by the property TableDefs.Count.
Each table in the database is represented by a TableDef object, whose most important properties are the following:
- Count returns the number of rows in the table.
- Fields is another collection of Field objects. Each. Field Object represents a field in the table.
- Indexes is another collection of Index objects. Each Index object represents an index of the table.
Assume that you have declared and opened a Database object with the following statements:
Dim DB As Database
,Set DB = OpenDatabase(dbName)
To access the database table definitions use the expression DB.TableDefs, which is a collection of objects, one for each table. Through the members of this collection you can access the properties of the tables in the database. The simplest way to access each table is to first declare a TableDefvariable, as follows:
Dim tbl As TableDef
and then scan each element of the DB.TableDefs collection with a For Each … Next structure, like this:
For Each tbl In DB.TableDefs
Debug.Print tbl.Name
Next
This loop displays the names of the tables. In addition to the data tables, Access .databases contain a few system tables. These tables have names that start with MSYS, and you should skip them. For more information on’how to detect and t ignore system tables, see the section “VB6 at Work: The DBS tructure Project,” later .in this chapter. You can also access a table’s indices, as explained next.
A Table’s Indices
Most tables in a database have at least one associated index file, sometimes more. To access the indices of a table, you use the Indexes property of the TableDefs object. Indexes is another collection of objects, one for each index of the table. ‘The simplest way to access each index in a table is to first declare an Index object:
Dim idx As Index
and then scan each member of the Indexes collection with a For Each … Next structure:
For Each idx In tb1:Indexes
Debug.Print idx.Name
Next
The tbl entry is a TableDef object, and its declaration was explained in the previous section. The following nested loop displays the names of all tables in the database, and under each table it displays the names of its indices:
You can request the definition of each index with the Fields property, and you use the Unique property to determine whether a given index requires unique keys.
A Table’s Fields
The most important property of the TableDefs object is the Fields property, which is another collection of objects, one for each field in the table. To access a table’s fields, you first specify the table in the database that you want. You specify tables with an index value, which is 0 for the first table, 1 for the second table, up to DB.TableDefs .Count-1 for the last table. You can access the fields of the first table in the database through the object:
DB.TableDefs(0):Fields
Alternatively, you can use the table’s name in place of the index. The following object represents a.ll the fields in the Titles table of the BIBLIO database:
DB.TableDefs(“Titles”).Fields
To access each individual field in the Fields collection, declare a Field object variable, and then scan the collection’s members with a For Each … Next structure. The following code segment displays the names and types of each field in the first table of the database:
Dim fld As Field
For iach fld In DB.TableDefs(0).Fields
Debug:Print fld.Name, fld.Type
Next
The Type property returns an integer value that represents the type of the field. You must provide. a short procedure that coverts this number to ‘8 string such as Integer”, “String”, and so on.
- OrdinalPosition The order of the fields in the table
- AllowZeroLength A Boolean property indicating whether the field can be set to an empty string
- Size The size of the field in bytes
- Value The current value of the field
