Monday, January 11, 2010

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.


Anonymous said...

I have some append queries that keep appending new records to the very top of my tables, but I need them to be in order of entry, so they need to be at the bottom. Any suggestions?

Roger's Access Blog said...

Access tables have no intrinsic order. This is by design. A relational database should have no intrinsic order. Think of them as a bag of marbles.

If you need them to be in order, you should sort them on some field in a query.

dr.tom said...

How do you prevent appending duplicates. If the updated data source contains both the new data and old data, how do you get just the new data to append to the table?

Roger's Access Blog said...

dr. tom,

That's a really good question, but one that would be better answered in another blog post. I haven't posted anything new for a while, so I'll do that next. Stay tuned.

Roger's Access Blog said...

I've posted an answer to dr.tom's question here:
Append Queries: Avoiding Duplicates,

Anonymous said...

Does this work the same with Bound forms?
I have a from bound to a query and want to append the data to another table.

South Ashford Community Blogger, Kent said...

Hi there,

This is very useful, thank you.

I have a form, with a subform, the form has 2 combo boxes, both populated by sql queries (to pull in customer list & dates list), my subform, the user can enter items, I want them all saved back together in my "tblItems", so details/rows added in subform, with the associated customer and date, but I'm really stuck, hope someone can help, thank you, Clair

Nikkie said...

Hi, thank you, your blog has helped me create an append query for my database. The query updates my table with the 40 records but I want it to also display the recods on my subform, can you help?

Nikkie said...

Hi, your blog has helped me create an append query, thank you.

The query updates the 40 records to my table without a problem but I want it to display the records on my subform, can you help?