Query to append one record

You can create a query that appends a single record to a table, although the way you create the query is a little weird. The resulting SQL statement doesn't exactly match the syntax that we described earlier, either. But it all works and would definitely be easier than trying to write a lengthy SQL statement by hand.

The trick is to create a new query that doesn't have any tables at the top of the Query design window. Or, if a table is at the top of the query, right-click the table and choose Delete so that no tables are at the top. Click the Append button on the (Query Tools) Design tab to change the query to an append query, and specify the name of the table into which the query should append its record.

In the Field row of the QBE grid, you need to provide a value for at least one field in the table. The syntax is name: value, where name can be any name, and value is the value that you want to store in a field. Then, in the Append To row, choose the field into which you want to place the value. For example, the query in Figure 7-9 appends a single record with the current date in the Submitted field, False in the Paid field, and No Reply in the Status field. The figure also shows the SQL statement for the query.

Figure 7-9:

Sample append query and its SQL view.

Field Table

Submitted: DateO

Paid: False

Status: "No Reply"



Append To



Status ¡v




^ ApperrdCineRecord m&EPT :MT'> [ submitted. Paid, :tatj;)

kaECT T-- J False AS Paj4'"No Reply" ^fltatus;

^ ApperrdCineRecord m&EPT :MT'> [ submitted. Paid, :tatj;)

kaECT T-- J False AS Paj4'"No Reply" ^fltatus;

Even though the syntax of the SQL statement for the query doesn't look like the syntax that we describe earlier, the statement executes just fine in VBA. Here's how you can write the code to execute that statement (and temporarily turn off warning messages):

Dim mySQL As String mySQL = "INSERT INTO Stats ( Submitted, Paid, Status )" mySQL = mySQL & " SELECT Date() AS Submitted, False AS Paid," mySQL = mySQL & " 'No Reply' AS Status"

'Note single quotation marks inside double quotation marks above.

DoCmd.SetWarnings False DoCmd.RunSQL mySQL DoCmd.SetWarnings True

Was this article helpful?

0 0


    Can I create an append query in vba?
    8 years ago

Post a comment