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 SubUsing 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.
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 SubUsing 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 SubNote 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.
<?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.
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 fldAutoNumber 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 ndxIn 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 WithThe 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.
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 FunctionWhich 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.
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 SubThe 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.
Altering the Structure of a Table
Problem
How can I programmatically change the structure of an existing table? How do I add fields, drop fields, or just change the data types for existing fields?
Solution
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:
- Adding new fields
The only restriction is that you cannot add an AutoNumber field to a table that already has such a field. Only one AutoNumber field is allowed per table.
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.
- Deleting fields
Aside from any issues involved in deleting data from a table that participates in a relationship, the obvious caution to heed is that you will permanently lose the data in the deleted fields.
- Changing a field type
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.
Programmatically adding and deleting a field
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 SubRefer 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:
'.Columns.Delete ("Comments")Changing a field’s data type
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
Discussion
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.
Creating and Using an Index
Solution
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.
Discussion
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 SubOnce 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.
Programmatically Removing a Table
Problem
Is there a way to programmatically delete a table?
Solution
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.
There are two useful approaches for this. One option is to use the DoCmd object with the DeleteObject method:
DoCmd.DeleteObject acTable, "tblTransactions"
When using the DeleteObject method, you specify the object type, and then the name of the object to delete.
The other method uses SQL and the Drop statement:
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.
Discussion
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 SubWhen the routine is run, the message shown in Figure 4-5 is displayed. Only a Yes answer will run the table delete.
Programmatically Creating a Query
Problem
It’s one thing to assemble SQL statements in code and run them. But how do you create permanent queries with programming code that will then appear on the Queries tab?
Solution
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.
Discussion
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;
Creating action queries
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 SubIn 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 SubTo 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.
Get Access Data Analysis Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
