Featured Sample: ActionQueriesInCode.mdb

Action Queries in Code.mdb

by Roger Carlson

There are a variety of circumstances under which you might want to run a query in VBA code. You may want to just display the results of a Select query to the screen at the push of a button. You may want to run an Action Query in a code module as part of another process.

This sample illustrates how to suppress confirmation messages when running an Action query in VBA code, and is a perfect companion to the Action Queries Series.

Full documentation included.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=402.

.

Action Queries: Make-Table Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. In previous posts, I discussed the Delete Query, the Update Query, and the Append Query. In this last post of the series, I'll look at the Make-Table Query.

Make-Table Queries

A Make-Table Query is a shortcut method to create a table based on values in another table. In SQL, it's called a SELECT...INTO statement. The basic syntax looks like this:
SELECT ISBN, Title, PubID, Price INTO BooksTemp
FROM Books;
 In the Query Builder:

In properly normalized databases, there aren't a lot of uses for the Make-Table Query. It's most important use is for creating temporary tables. Sometimes, complex queries can be simplified or their performance improved by creating a smaller, temporary table. The Make-Table is ideal for this application.
However, make-table queries do have some disadvantages. First of all, they tend to bloat the database. Every time you make a temporary table, your database grows. When you delete it or over-write it, the database doesn't automatically shrink. This will require that you periodically compact your database.
Secondly, you don't have a lot of control over the structure of a table made with a make-table query. These tables automatically inherit the field datatypes and sizes from the parent table, but it does not inherit any other field properties (like validation rule or defaults), nor does it inherit any indexes.

Running a Make-Table Query

There are two buttons for running Action Queries: The View button displays the results of the query, that is, which records will be updated. The Run button actually appends the records to the target recordsource.
.

Action Queries: Append Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. In previous posts, I discussed the Delete Query, and the Update Query. This time, I'll look at the Append Query.

Append Queries

An Append Query, also called an INSERT INTO in SQL, adds records to a record source (table or query). It can either append individual record values or a dataset queried from another record source. To append values, use the VALUES keyword:

INSERT INTO Books(ISBN, Title, PubID, Price)
VALUES ("0-103-45678-9", "Iliad", 1, 23);

Unfortunately, this type of Append query cannot be made in the Query Builder. In fact, if you type it into the SQL View of a query and switch to the Design View, the query will be converted to this:

INSERT INTO Books (ISBN, Title, PubID, Price)
SELECT "0-103-45678-9" AS Expr1, "Iliad" AS Expr2, 1 AS Expr3, 23 AS Expr4;

This alternate syntax will still work in Access, but it's not standard SQL syntax. This type of Append query is most useful when used in embedded SQL, that is, SQL statements that are executed in a VBA module. One common use is with unbound forms. For example, suppose I have a form with unbound controls, like so:

Behind the Append Values button, I can have the following code:

Private Sub cmdAppendValues_Click()
Dim strSQL As String
strSQL = "INSERT INTO Books(ISBN, Title, PubID, Price)" & _
" VALUES ('" & Me.ISBN & "', '" & Me.Title & "', " & _
Me.PubID & "," & Me.Price & ");"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

Clicking the button will execute an SQL statement that appends the values from the unbound controls (i.e. Me.ISBN, etc.) in the form into a new record of the table.

For a working example of an unbound form using SQL statements, see my sample: UnboundSQL.mdb.

The second type of append query queries a dataset from one record source and appends it to another. This is most useful when importing data from an external source (say an Excel spreadsheet or a CSV file) to be added to an existing table. Suppose I have a table of new books (called AppendList) to be added to the Books table. I can use the following SQL statement:

INSERT INTO Books (ISBN, Title, PubID, Price)
SELECT ISBN, Title, PubID, Price
FROM AppendList;

Or as seen in the QBE grid:

Running an Append Query

There are two buttons for running Action Queries: The View button displays the results of the query, that is, which records will be updated. The Run button actually appends the records to the target recordsource.

Next up, the Make-Table Query.