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

Featured Sample: Unbound SQL

UnboundSQL.mdb

by Roger Carlson

This sample illustrates how to use UNBOUND controls to add, delete, and edit records in a table using SQL statements. Companion sample for the Action Queries Series.

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

.

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

Featured Sample: Archives Manager

Archives Manager

by A. D. Tejpal

This sample db demonstrates management of personal knowledge base (e.g. notes / articles / extracts etc). Copies of important messages from various discussion groups can also be stored.

Self generating cascaded lookup lists for category / subject / topic facilitate data entry as well as search & retrieval through preview or print.

The db is in Access 2000 file format, developed on Access-XP installation.

You can download this sample for free here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=140.

.

Action Queries: Update Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but make 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. Last time, I discussed the Delete Query. This time, I'll look at the Update Query.

Update Queries

An Update Query will make bulk changes to records in the record source. . The record source must be updateable. (See
This Recordset Is Not Updateable. Why? For details about updateability.) At its simplest, an Update Query will make changes to every record. For instance, suppose I want to raise the Price of every book in the Books table by 10%. I could create a SQL statement like this:

UPDATE BOOKS SET Price = Price*1.1;

In the Query Builder, it looks like this:

But it's not very often you want to update every record. Usually, you want to update just certain records. To do that, you add a Where condition just as you would in a Select Query (see: Select Queries Part 2: Restricting Rows - the Where Clause). Suppose I wanted to change every book whose price was $25 to $24.95. My SQL statement would be as follows:

UPDATE Books SET Price = 24.95
WHERE Price=25;

Which looks like this in the Query Builder:

Of course, you could also raise the price by 10% for a particular publisher:

UPDATE BOOKS SET Price = Price*1.1
WHERE PubID=2;

Updating from a List

But what if, as I did with the Delete query, you are given a list of ISBN numbers and associated new prices? How could you update your Books table from this list? You SHOULD be able to use a subquery. Something like this:

UPDATE Books SET Books.Price =
(SELECT price FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN)
WHERE ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN);

Unfortunately, while you could do this in almost any other SQL implementation (SQL Server or Oracle, for instance) you cannot do this in Access SQL. It will give you the "Operation Must Use an Updateable Query" error.

Fortunately, there are a couple of workarounds. One is to use a Join and the other is to use a Domain Aggregate function.

Workaround 1 - the Join

This work around requires you to Join the Books table with the UpdateList table, like this:

UPDATE Books INNER JOIN UpdateList
ON Books.ISBN = UpdateList.ISBN
SET Books.Price = [Updatelist].[price];

In the QBE:

One caveat here is that the Join MUST be an updateable dataset, so there must be a unique index on the Join field (in this case ISBN).

Workaround 2 - DLookup

This work around uses a subquery in the Where clause, but uses a DLookup function in the Set clause:

UPDATE Books SET Books.Price =
DLookUp("Price","UpdateList","ISBN ='" & [ISBN] & "'")
WHERE Books.ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = updateList.ISBN);

This solution does not require a unique index, but it is much slower than the Join solution.

Running an Update 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 updates the records.

Next up, the Append Query.