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.

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.

Example 4-4. Three methods to add and delete fields
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:

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

Problem

Tables usually have a primary key. How can other table indexes be created and used?

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.

A table index
Figure 4-3. A table index

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.

A new index is created
Figure 4-4. A new index is created

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

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 Sub

When the routine is run, the message shown in Figure 4-5 is displayed. Only a Yes answer will run the table delete.

Confirming a table delete
Figure 4-5. Confirming a 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.

A saved query
Figure 4-6. A saved query

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

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