Action queries CRUD with SQL

Section 5.6 explains how the program can Create, Read, Update and Delete records in the database by means of recordsets. In this section we will see how the program can make such CRUD operations by means of SQL.

We have four kinds of SQL queries available: INSERT INTO . . . (Create)

This query inserts new records into a table. SELECT . . . FROM . . . (Read)

This query extracts data from the tables. It is the query we have used in the rest of the booklet (e.g. sections 4.1 to 4.4). In many cases it also allows the user to edit the data. We will not explain it further in this section.

UPDATE . . . (Update)

This query changes records in a table. DELETE FROM . . . (Delete)

This query deletes records from a table. The term action query means INSERT, UPDATE or DELETE - the queries that change something in the database.

We will explain the mechanisms through the examples in Figure 7.1. To execute an action query, we use this VBA function:

CurrentDb.Execute ”INSERT . . . ", dbFailOnError

CurrentDB is the database currently used by the program. We ask it to execute the INSERT query. Usually the query statement is a computed string that includes dialog variables, for instance the customer the user works with.

The last parameter dbFailOnError is optional. It asks the database to give an error message if something goes wrong during the query, for instance if we try to insert a record that violates referential integrity. If we omit it, there is no response in case of errors - nothing changes in the database, and the program doesn't know about it. Include dbFailOnError, particularly while you are testing the program.

INSERT

The figure shows a full INSERT statement. It inserts a single record into tblRoomState. It sets the roomID to 14, the date to 27th Oct 2002, and the state to 1. The

remaining fields of the record become Null. Note how we have put date in brackets. If we omit the brackets, the database engine believes we try to use the built-in

date function and reports it as a syntax error (inde-

pendent of whether we have included dfFailOnError). The INSERT statement can also insert a bunch of records extracted from another table or query. The bottom of the figure shows an example (explained below).

AutoNumber

Assume you have a table with a field of type Auto-

Number. When you insert a record into the table,

Access will automatically fill in the AutoNumber field. However, if you explicitly specify a value for the field, Access will accept it if it is unique (different from other values of this field).

When Access generates the number automatically, it will use the highest used number plus one. (The highest used number may have been used by a record now deleted.) As an example, assume we want the booking numbers in the hotel to consist of a year number and a sequence number, for instance 20040001, 20040002 etc. We can then generate the first booking of the year with this query:

INSERT INTO tblStay (stayID) VALUES (20040001)

New stay records will then be auto-numbered from this value and on. Deleting some of the records still makes the number grow sequentially.

UPDATE

The UPDATE statement sets room state to 2 and personCount to 1 for all roomState records where roomID=14 and date=27/10/2002. In this case there is only one such record because roomID and date make up the primary key for the table.

Confused about the dates? Access SQL uses American dates, while the explanations in the booklet use middle European dates. See more in section 6.6.

DELETE

The DELETE statement deletes all roomState records where roomID=14 and date=27/10/2002. In this case there is only one such record.

7.1.1 Temporary table for editing

The user (or the program) can edit the fields in a simple query. However, if the query contains a GROUP BY,

an outer join, or another complex query, the result can not be edited. From a usability perspective, there is often a need to edit such a query anyway. It can either be done through a dialog box (low usability), or through a temporary table that holds a copy of the GROUP BY result. The user will edit the temporary table directly. The bottom of the figure shows an example of this. Let us see how it works.

In the hotel system the user sees a single record for each room booked by the guest. In the database there is a roomState record for each date the room is booked, but on the user interface we aggregate it to a single line with start date and number of nights. The query

qryStayRooms does this, but the user can not edit the

result directly.

The solution is to have a temporary table tblTemp with the same fields as qryStayRooms. The first statement deletes the present records in tblTemp. The next statement copies all the query records that belong to the current stay into tblTemp. Note how the program

computes the SQL statement from several parts, one of them being me.StayID, the current stay number the user is working on. Since the SQL statement is very long, it is split into two lines with an underscore at the end of the first line.

Note also the SELECT * part that extracts all fields of the query. This only works if tblTemp has the same fields - and with the same names. It is possible to extract only some of the fields - or rename them - in order to match the fields in tblTemp. We could for instance extract roomID (renamed to Room) and Nights:

INSERT INTO tblTemp SELECT roomID AS Room, Nights . . .

After the INSERT, the program can show tblTemp in a form so that the user can edit it. The program may either update the real database each time the user changes something in tblTemp, or it may update the whole thing when the user closes the form.

Fig 7.1 Action queries - CRUD with SQL

CurrentDb.Execute ”INSERT . . . ", dbFailOnError

CurrentDb.Execute ”DELETE FROM tblTemp;", dbFailOnError

CurrentDb.Execute ”INSERT INTO tblTemp SELECT * FROM qryStayRooms “ _ & “WHERE stayID=” & me.stayID & “;” , dbfailonerror

Execution of an action query.

Insert one record in tblRoomState. Only these three fields get a value. (The rest become Null.)

Note the brackets on name to distinguish from the built-in name function

Show a message in case of errors

UPDATE tblRoomState SET state=2, personCount=1 WHERE roomID=14 and [date]=#10/27/02#;

DELETE FROM tblRoomState

WHERE roomID=14 AND [date]=#10/27/02#;

Set state to 2 and personCount to 1 for all RoomStates with roomID=14 and date=27-10-2002 (there is only one)

Delete all RoomStates with roomID=14 and date=27-10-2002 (there is only one) INSERT INTO tblRoomState (roomID, [date], state)

VALUES (14, #10/27/02#, 1);

Delete all records from tblTemp. Then copy all query records from the current stay to tblTemp

Allow user to edit a GROUP BY query: Make a temporary copy