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.
<?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.
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.
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.
You can carry out all of these tasks manually, in the design of a table, or programmatically, using DAO, ADOX, or SQL. Either way, each of these actions comes with some considerations:
If you add an AutoNumber field to a table that does not already have one, the existing records will be filled in with the sequential numbering scheme in the new field. This is helpful.
The success of this action depends on the actual data types in question. For example, an alphanumeric value that contains letters will not convert to a number type. You can convert a Text type to a numeric type, but you will lose your data in the process.
Also, you can’t change a field to an AutoNumber type if there are any records in the table. The only way to get an AutoNumber field into a table with existing records is to add it as a new field. Then, if it makes sense, you can delete the field it was meant to replace.
Example 4-4 shows how to add and delete a field using DAO, ADOX, and SQL. There is a separate routine for each method that adds a Comments field to the Sales table and then deletes it. The Comments field is a Text data type, and is set at a size of 100 characters.
Sub field_DAO( ) Dim db As DAO.Database Dim tbl As DAO.TableDef Dim fld As DAO.Field Set db = CurrentDb Set tbl = db.TableDefs("Sales") With tbl 'add new field .Fields.Append .CreateField("Comments", dbText, 100) 'delete field .Fields.Delete ("Comments") End With MsgBox "done" End Sub Sub field_ADOX( ) 'must set reference to 'Microsoft ADO Ext. 2.7 for DDL and Security Dim cat As New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection With cat.Tables("Sales") 'add field .Columns.Append "Comments", adVarWChar, 100 'drop field .Columns.Delete ("Comments") End With Set cat = Nothing MsgBox "done" End Sub Sub field_SQL( ) Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssql As String ssql = "Alter Table Sales " & _ "ADD COLUMN Comments TEXT(100)" conn.Execute ssql ssql = "Alter Table Sales " & _ "Drop COLUMN Comments" conn.Execute ssql MsgBox "done" End Sub
Refer to Creating Tables Programmatically for instructions on how to create the Sales table, then use one of the approaches listed here to add and delete the Comments field. There’s one caveat: because the field is added and then immediately deleted, you will not see it when viewing the Sales table. To work around this, comment out the code line that deletes the field. For example, in the
field_ADOX example, put an apostrophe in front of the line that deletes the field. The line will then look like this:
SQL provides an
Alter Column construct that’s useful for changing a field’s data type. The following code shows how the Alter statement is used to change the existing Comments field to the Integer type (in Access, this appears as the Long data type):
Sub alter_field_SQL( ) Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssql As String 'alter field to be Integer (Long) ssql = "Alter Table Sales " & _ "Alter COLUMN Comments Integer" conn.Execute ssql conn.Close MsgBox "done" End Sub
Typically, you will be changing data types to accommodate larger data. For example, increasing the size of a text field from 50 to 100 characters makes sense, as does changing a data type from Long to Double to allow for large numbers with decimals.
Changing to a smaller data type can cause data loss. Make sure you have a justifiable need to alter a field to a smaller type or another type that will cause data loss, such as going from Text to a numeric type. Practical sense shows that being able to accommodate occasional larger pieces of data is better than trying to gain small optimizations by squeezing fields.
For example, you might expect a phone number field to need to accommodate only up to 14 characters for U.S./Canadian-style phone numbers in the format (111)-111-1111. Most of the time, that will be sufficient. But what happens if you need to enter a phone number that has an extension? The insert will bomb, or the data will be truncated. A lost phone number could cause more of a problem for a company than a tiny bit of extra required memory. With that in mind, it might make more sense to set the phone number field to a larger size—say, 30 characters—capable of accommodating occasional nonstandard phone numbers.
While in the design view of a table, use the View → Indexes menu option to display the Indexes dialog box. (In Access 2007, use the Table Tools → Design view of the Ribbon.) Figure 4-3 shows the dialog sitting over the table for which it is displaying index information.
A table can have up to 32 indexes, and each index can contain up to 10 fields. In this case, there is a single index named PrimaryKey, which is based on the ClientID field. Values in the ClientID field must be unique because the Unique property is set to Yes.
What if the tblClients table didn’t have this unique ClientID field? You could use a combination of other fields to ensure uniqueness among records. For example, combining the fields that contain the first name, last name, and address should ensure unique records. Providing a name and selecting these fields in the Indexes dialog box, as shown in Figure 4-4, creates the new index.
As shown in Figure 4-4, the Primary property for the Name_Address index is set to Yes. This means that when this table is opened in Datasheet view, the sort established in the Name_Address index will sort the records. Only one index at a time can be the Primary index.
A table can have multiple indexes. It’s good practice to provide indexes on fields that are often sorted on, but this is not a requirement, and it’s not generally necessary for tables that contain a small or moderate number of records. Still, knowing how to create and apply an index is handy when performance issues do pop up.
Indexes are also useful when working with DAO or ADO recordsets. In this situation, applying a predesigned index provides an immediate ordering of the table. Indexes are applied only to table-type recordsets.
Here is an example of opening a table-type recordset and applying an index:
Sub apply_index( ) Dim db As DAO.Database Set db = CurrentDb Dim recset As DAO.Recordset Set recset = db.OpenRecordset("tblClients") recset.Index = "Name_Address" '' 'perform processing here '' recset.Close db.Close Set recset = Nothing Set db = Nothing End Sub
Once the table-based recordset is opened, the index is applied, and the order of the records follows the sorting scheme of the index.
Indexes can be created programmatically. See Creating Tables Programmatically for examples.
Is there a way to programmatically delete a table?
First, let’s make clear the distinction between clearing out a table and removing a table. One action involves deleting the data from a table. A delete query (see Deleting Data) is the best bet for that. The other action involves removing the table entirely.
DoCmd.DeleteObject acTable, "tblTransactions"
When using the
DeleteObject method, you specify the object type, and then the name of the object to delete.
Sub drop_table( ) Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssql As String ssql = "Drop Table tblServices" conn.Execute ssql conn.Close End Sub
The SQL syntax is similar to that for the
DeleteObject method: the
Drop statement is followed by the object type, and then the name of the object to delete.
Regardless of which method you use, it is wise to ask for confirmation first. Deleting a table accidentally can be catastrophic. Here is a routine that prompts for confirmation before deleting a table:
Sub delete_table( ) Dim proceed As Integer proceed = MsgBox("Do you wish to delete the table?", _ vbYesNo, "Confirm Table Delete") If proceed = vbYes Then DoCmd.DeleteObject acTable, "tblTransactions" MsgBox "Table deleted" Else MsgBox "Delete canceled" End If End Sub
When the routine is run, the message shown in Figure 4-5 is displayed. Only a Yes answer will run the table delete.
You can easily create stored queries programmatically with either DAO or SQL. Figure 4-6 shows a query that was manually assembled and saved. It is a permanent object in the Access database, and appears under Queries in the database window.
The query contains three fields: the CustomerID field from the tblCustomers table, and the PurchaseDate and Amount fields from the tblSales table. There is a one-to-many relationship between these tables—each customer has zero or more sales records.
Here is DAO code that will create this query programmatically:
Sub create_querydef( ) Dim db As DAO.Database Set db = CurrentDb Dim qd As DAO.QueryDef Dim ssql As String ssql = "SELECT tblCustomers.CustomerID, " ssql = ssql & "tblSales.PurchaseDate, tblSales.Amount " ssql = ssql & "FROM tblCustomers INNER JOIN tblSales ON " ssql = ssql & "tblCustomers.CustomerID = tblSales.Customer_ID;" Set qd = db.createquerydef("DAO_Query", ssql) db.Close Set db = Nothing MsgBox "done" End Sub
In DAO, the
QueryDef object can either represent a saved query (of the
QueryDefs collection) or be used with the
createquerydef method of the DAO
Database object. In the preceding example, the SQL statement is assembled and used with the
createquerydef method. The name for the query to be saved as—
DAO_Query, in this example—is also supplied.
Alternatively, you can use SQL to create and store a query. The SQL term for a query is a view, and the
CreateView construct is used:
Sub createview( ) Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssql As String ssql = "Create View qryCustomerSales (CustID, PurchDate, Amt) As " ssql = ssql & "SELECT tblCustomers.CustomerID, " ssql = ssql & "tblSales.PurchaseDate, tblSales.Amount " ssql = ssql & "FROM tblCustomers INNER JOIN tblSales ON " ssql = ssql & "tblCustomers.CustomerID = tblSales.Customer_ID;" conn.Execute ssql conn.Close MsgBox "done" End Sub
The SQL statement, beginning with
Create View, is assembled, and the
Execute method of the ADO
Connection object applies the SQL statement and creates the query.
Let’s explore how the
CreateView SQL statement works. After the
Create View keywords, a list of field names is supplied. These names serve as aliases for the fields listed in the subsequent
Select statement. In other words, the aliases CustID, PurchDate, and Amt are provided for the CustomerID, PurchaseDate, and Amount fields. The SQL statement that is stored looks like this:
SELECT tblCustomers.CustomerID AS CustID, tblSales.PurchaseDate AS PurchDate, tblSales.Amount AS Amt FROM tblCustomers INNER JOIN tblSales ON tblCustomers.CustomerID = tblSales.Customer_ID;
The preceding examples created select queries. To create permanent action queries (e.g., update, append, or delete queries), use the DAO model. The SQL approach requires an inner
Select statement, which limits the query to being one that selects data. The DAO approach, on the other hand, simply stores whatever SQL statement it is handed.
Here is an example of creating a delete query:
Sub create_querydef_Delete( ) Dim db As DAO.Database Set db = CurrentDb Dim qd As DAO.QueryDef Dim ssql As String ssql = "Delete * From tblSales" Set qd = db.createquerydef("Delete_Sales", ssql) db.Close Set db = Nothing MsgBox "done" End Sub
In this code, the simple
Delete * From tblSales is stored as a delete query.
The following code uses DAO to create a make-table query (in this case, the query creates a table of sales records for customers from Texas):
Sub create_querydef_MakeTable( ) Dim db As DAO.Database Set db = CurrentDb Dim qd As DAO.QueryDef Dim ssql As String ssql = "Select tblCustomers.CustomerID,tblSales.PurchaseDate, " ssql = ssql & " tblSales.Amount Into tblTexasCustomerSales " ssql = ssql & "FROM tblCustomers INNER JOIN tblSales ON " ssql = ssql & "tblCustomers.CustomerID = tblSales.Customer_ID " ssql = ssql & "Where tblCustomers.State='TX'" Set qd = db.createquerydef("Create_Texas_Sales", ssql) db.Close Set db = Nothing MsgBox "done" End Sub
To be clear, running this routine simply creates the query and saves it within the Access database; it does not run the query. When the query is run, a table named tblTexasCustomerSales, populated with sales information for Texas customers, will be created.