Appending a single record with SQL

You can also use the SQL INSERT INTO statement to add a single record to a table. However, the syntax is a little tricky as are the rules that determine how you do it. For example, you can't append an entirely blank record to a table that contains required fields because the table won't accept the record until all requirements have been met.

The basic syntax for inserting a single record into a table in SQL is

Figure 7-8:

A sample append query in Access Query Design.

INSERT INTO tblName [(fldName [____]) VALUES (value [____])

where

1 tblName is the name of the table to which the record should be appended.

1 fldName is the name of the field that will be assigned a value. 1 value is the value you want to store in the field.

i [,... ] means that you can list multiple fields and values, if you wish, as long as you separate their names with commas.

The order of values being assigned to fields must match the order of the field names in the statement. For example, suppose the database contains a table named Stats that contains a Date/Time field named Submitted, a Yes/No field named Paid, and a Text field named Status (among other fields). The following SQL statement adds one record to that table, placing the current date in the Submitted field, False in the Paid field, and No Reply in the Status field:

INSERT INTO Stats ( Submitted, Paid, Status ) VALUES (Date(), False, 'No Reply')

To execute the statement from VBA, just put the whole SQL statement in quotation marks next to a DoCmd.RunSQL statement, as usual. Or you can build it from shorter lines, as follows:

Dim mySQL As String mySQL = "INSERT INTO Stats ( Submitted, Paid, Status )" mySQL = mySQL + " VALUES (Date(), False, 'No Reply')" DoCmd.RunSQL mySQL

0 0

Post a comment