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