O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 4. Managing Tables, Fields, Indexes, and Queries

We use tables to store data, indexes to organize and order data, and queries to work with data. In a perfect world, when a database goes into production, our development effort is finished.

Alas, this is not a typical scenario. New requirements come along. Table structures have to be changed. New indexes are needed. New queries are called for to match the new schema. The recipes in this chapter address how to manage these things programmatically. If you have done much work with Access, you know how tedious it can be to manually create tables. Often, you have no choice but to manually create database objects, but when you're faced with having to create a number of tables or queries that are similar, having a way to automate their creation is a boon. Knowing how to programmatically add and delete fields—and create and use indexes—can also save you a lot of time.

Creating Tables Programmatically

Problem

Creating tables manually on the Tables tab of the database window is a tedious process, especially when you're designing several tables. What are the programmatic alternatives?

Solution

There are four good programmatic methods for creating tables: DAO, ADOX, SQL, and XML/XSD. Other methods do exist, such as getting a table definition from a web service, but the four options just mentioned are the most common.

This recipe will demonstrate how to use each of these methods to create a table with the following properties and fields:

  • The table name is Sales.

  • There is a SalesID field, which is an AutoNumber field, and serves as the primary key.

  • There is a SalesPerson field, which is a Text data type.

  • There is a SalesDate field, which is a Date data type.

  • There is a SalesAmount field, which is a Single data type (numeric with a decimal portion).

Using DAO to create a table

Data Access Objects (DAO) is a mature standard that has been around through many previous versions of Access. In Access 2003, DAO is still alive and kicking and enjoys a dedicated reference, i.e., you don't have to go out of your way to reference the library.

Tip

If you are not using Access 2003, you may have to set the DAO reference. To learn how to do so, read the next section, which explains how to set the reference for ADOX. The instructions are the same for DAO, except that you'll need to check the box for Microsoft DAO 3.6 Object Library in the reference list (your version number may be different, but that's fine).

The following code uses DAO to create the Sales table:

	Sub make_DAO_table( )
	  Dim tbl As DAO.TableDef
	  Dim fld As DAO.Field
	  Dim ndx As DAO.Index
	  Set tbl = New TableDef
	  With tbl
	    .Name = "Sales"
	    Set fld = .CreateField("SalesID", dbLong)
	    fld.Attributes = dbAutoIncrField
	    .Fields.Append fld
	    .Fields.Append .CreateField("SalesPerson", dbText)
	    .Fields.Append .CreateField("SalesDate", dbDate)
	    .Fields.Append .CreateField("SalesAmount", dbSingle)
	    Set ndx = .CreateIndex("PrimaryKey")
	    With ndx
	      .Fields.Append .CreateField("SalesID")
	      .Primary = True
	    End With
	    .Indexes.Append ndx
	  End With
	  CurrentDb.TableDefs.Append tbl
	  MsgBox "done"
	End Sub

Using ADOX to create a table

ADOX is an extension of ActiveX Data Objects (ADO). You'll need to add a reference to ADOX manually if you want to use this method of creating tables. In a code module, use the Tools →References menu option to display the References dialog box, shown in Figure 4-1. Scroll through the list and find "Microsoft ADO Ext. 2.7 for DDL and Security." (Your version number may be different; that's fine.) Check the reference, and click the OK button.

Setting a reference to ADOX

Figure 4-1. Setting a reference to ADOX

The following code uses ADOX to create the Sales table:

	Sub make_ADOX_table( )
	  'must set reference to
	  'Microsoft ADO Ext. 2.7 for DDL and Security
	  Dim cat As New ADOX.Catalog
	  Dim tbl As New ADOX.Table
	  Dim col As New ADOX.Column
	  cat.ActiveConnection = CurrentProject.Connection
	  With col
	    Set .ParentCatalog = cat
	    .Name = "SalesID"
	    .Type = adInteger
	    .Properties("Autoincrement") = True
	  End With
	  With tbl
	    .Name = "Sales"
	    .Columns.Append col
	    .Columns.Append "SalesPerson", adVarWChar, 100
	    .Columns.Append "SalesDate", adDate
	    .Columns.Append "SalesAmount", adSingle
	    .Keys.Append "PrimaryKey", adKeyPrimary, "SalesID"
	  End With
	  cat.Tables.Append tbl
	  Set cat = Nothing
	  Set col = Nothing
	  MsgBox "done"
	End Sub

Using SQL to create a table

Structured Query Language (SQL) contains a subset of statements collectively known as Data Definition Language (DDL).

Tip

Don't confuse DDL with DLL (Dynamic Link Library). DDL manages database objects. A DLL is a compiled procedure library.

SQL is the standard for querying and manipulating data. However, the DDL statements are used to manipulate database structures. The following SQL does just that, using the CreateTable construct:

	Sub make_SQL_table( )
	  Dim conn As ADODB.Connection
	  Set conn = CurrentProject.Connection
	  Dim ssql As String
	  ssql = "Create Table Sales (" & _
	  "[SalesID] AutoIncrement PRIMARY KEY, " & _
	  "[SalesPerson] Text (50), " & _
	  "[SalesDate] DateTime, " & _
	  "[SalesAmount] Real)"
	  conn.Execute ssql
	  MsgBox "done"
	End Sub

Note that ADO is used to execute the SQL statement. This has no bearing on the previous ADOX example. You may need to set a reference to the ADO library; to do this, follow the instructions in the preceding section for referencing ADOX. The ADO library is named Microsoft ActiveX Data Objects 2.1 Library (your version number may be different).

Using an XSD schema definition to create a table

An eXtensible Markup Language (XML) schema holds the definition of a data structure. Schema files have the .xsd (XML Schema Definition) file extension.

The following code deviates a bit from the previous examples. The small subroutine calls the built-in ImportXML Access method, which imports an external schema file:

	Sub make_schema_table( )
	  Application.ImportXML _
	      Application.CurrentProject.Path & "\sales.xsd", acStructureOnly
	MsgBox "done"
	End Sub

Access creates the Sales tables based on the instructions in the schema file, presented in Example 4-1.

Example 4-1. A schema file containing the definition for a table

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:od="urn:schemas-microsoft-com:officedata">
<xsd:element name="dataroot">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Sales" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
<xsd:attribute name="generated" type="xsd:dateTime"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="Sales">
<xsd:annotation>
<xsd:appinfo>
<od:index index-name="PrimaryKey" index-key="SalesID "
  primary="yes" unique="yes" clustered="no"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="SalesID" minOccurs="1" od:jetType="autonumber"
  od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes" type="xsd:int"/>
<xsd:element name="SalesPerson" minOccurs="0"
  od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="SalesDate" minOccurs="0"
  od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/>
<xsd:element name="SalesAmount" minOccurs="0"
  od:jetType="single" od:sqlSType="real" type="xsd:float"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

To create this schema file, create the Sales table using one of the previously described methods (or manually, for that matter), and export the table as XML. When doing so, select the option to export the schema, as shown in Figure 4-2.

Selecting to export the table design as a schema

Figure 4-2. Selecting to export the table design as a schema

Discussion

The methods just discussed illustrate four different approaches to creating the Sales table with the four required fields. The first field, SalesID, is created as an AutoNumber field, and is the primary key. One of the key differences between the DAO, ADOX, and SQL approaches is how this is handled. The DAO method creates SalesID as a Long data type, and then, to make it an AutoNumber field, sets the field's attribute to autoincrement:

	.Name = "Sales"
	Set fld = .CreateField("SalesID", dbLong)
	fld.Attributes = dbAutoIncrField
	.Fields.Append fld

AutoNumber fields are always Long, but with the functionality to increment the value as each new record is placed in the table.

Later in the DAO code example, an index is created and applied to the SalesID field, and the Primary property is set to True:

	Set ndx = .CreateIndex("PrimaryKey")
	With ndx
	.Fields.Append .CreateField("SalesID")
	.Primary = True
	End With
	.Indexes.Append ndx

In the ADOX example, the data type for SalesID is set to Integer. In ADO, the Integer type is the same as the Long type in Access. (The ADO SmallInt type is the equivalent of the Integer type in Access.) The Autoincrement property is then set to True. The result is the creation of an AutoNumber type for the SalesID field:

	With col
	  Set .ParentCatalog = cat
	  .Name = "SalesID"
	  .Type = adInteger
	  .Properties("Autoincrement") = True
	End With

The SalesID field is then set to be the primary key by using the Keys.Append method, and specifying the the name of the index, the type of key, and the name of the field. The type of key can be adKeyPrimary for a primary key, adKeyUnique for a unique key, and adKeyForeign for foreign keys. Note that when appending a foreign key, you will also have to specify the name of the table and column:

	.Keys.Append "PrimaryKey", adKeyPrimary, "SalesID"

The SQL example is simpler. The single line that specifies the SalesID field includes the parameters that make it both the primary key and an AutoNumber field:

	[SalesID] AutoIncrement PRIMARY KEY

Testing for the table's existence

It's a good idea to check whether a table exists before you try to create it. An efficient way of doing this is to wrap the table-creation routine inside a call to a function that tests all the existing tables to see if one has the name you are going to use for the new table. Here is a revision of the routine that uses SQL to create a table. The routine now includes a call to the DoesTableExist function, which is listed under the SQL routine in Example 4-2. The table name (Sales) is passed to the function. If the function does not find a Sales table, the table is created; otherwise, a message appears indicating that the table already exists.

Example 4-2. Testing to see whether a table exists before creating it

Sub make_SQL_table( )
  If DoesTableExist("Sales") = False Then
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    Dim ssql As String
    ssql = "Create Table Sales (" & _
    "[SalesID] AutoIncrement PRIMARY KEY, " & _
    "[SalesPerson] Text (50), " & _
    "[SalesDate] DateTime, " & _
    "[SalesAmount] Real)"
    conn.Execute ssql
    MsgBox "done"
  Else
    MsgBox "Sales table already exists"
  End If
End Sub

Function DoesTableExist(table_name As String) As Boolean
  Dim db As Database
  Dim tbl As TableDef
  Set db = CurrentDb( )
  DoesTableExist = False
  For Each tbl In db.TableDefs
    If tbl.Name = table_name Then DoesTableExist = True
  Next tbl
End Function

Which method should you use?

There is no definitive answer. If you're already comfortable with one of the methods, stick to it. Your application may call for table creation, but chances are you won't have to make a huge number of tables that often. Performance (speed) is therefore not likely to be a big issue, and all of these methods will leave manual table creation in the dust. On the other hand, if you don't need to create multiple tables, there isn't much sense in automating table creation.

Let's put automated multiple table creation to the test. Example 4-3 contains two routines: the make_a_bunch_of_tables routine repeatedly calls the make_a_table routine, each time passing a table name and a set of field names. This quickly makes a number of tables.

Example 4-3. Automated multiple table creation

Sub make_a_bunch_of_tables( )
  make_a_table "Cars", "CarID", "CarType", "PurchaseDate", "Amount"
  make_a_table "Tools", "ToolID", "ToolType", "PurchaseDate", "Amount"
  make_a_table "Hats", "HatID", "HatType", "PurchaseDate", "Amount"
  MsgBox "All Tables Made"
End Sub

Sub make_a_table(Table As String, F1 As String, _
    F2 As String, F3 As String, F4 As String)
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim ssql As String
  ssql = "Create Table " & Table & "(" & _
  "[" & F1 & "] AutoIncrement PRIMARY KEY, " & _
  "[" & F2 & "] Text (50), " & _
  "[" & F3 & "] DateTime, " & _
  "[" & F4 & "] Real)"
  conn.Execute ssql
  conn.Close
End Sub

The routines in Example 4-3 create three tables in an instant. The tables (Cars, Tools, and Hats) are structured the same, so only the table name and field names are passed to the make_a_table routine. However, if desired, you can add more arguments (for example, to accept data types and other properties). This gives you a lot of control over the automated table-creation process.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required