Inserting Updating and Deleting Records with Plain Text SQL in Microsoft Access

Executing plain text INSERT, UPDATE, and DELETE statements uses virtually identical methodology. Therefore, you'll examine these action queries by inserting a new record, updating that record, and then deleting it, all within the same procedure. This, of course, is not normally something you would do. You can take this generic procedure, however, and create a single-purpose insert, update, or delete procedure by simply removing the sections that you don't need.

Use the Shippers table from the Northwind database in the next procedure. The first few columns of this table are shown in Figure 20-5.

1

Shippers

X

ID - Company

First Name -

Last Name -

Q Shipping CompanyA

+

2 Shipping Company B

+

3 Shipping Company C

*

(New)

Record: M |l of 3 | ► H n: \ ■ \ Search

Notice that the last row in the ID column contains the value (New). This isn't really a value; rather, it's a prompt that alerts you to the fact that values for the ID column are automatically generated by the Access database. This column is the primary key for the Shippers table, and AutoNumber fields are a common method used to generate the unique value required for the primary key. You don't (and can't) set or change the value of an AutoNumber field. If you need to maintain a reference to a new record that you've inserted into the table, you'll need to retrieve the value that was assigned to that record by the AutoNumber field. You'll see how this is done in the example that follows:

Public Sub InsertUpdateDelete()

Dim objCommand As ADODB.Command Dim rsData As ADODB.Recordset Dim lRecordsAffected As Long Dim lKey As Long

Dim sConnect As String On Error GoTo ErrorHandler ' Create the connection string.

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Files\Northwind 2007.accdb;" & _ "Mode=Share Exclusive"

' Create the Command object we'll use for all three queries. Set objCommand = New ADODB.Command objCommand.ActiveConnection = sConnect

,'111 INSERT a new record into the database ''''''''''''''''''' ' Load the SQL string into the Command object. objCommand.CommandText = "INSERT INTO Shippers" & _ "(Company, [Business Phone]) " & _ "VALUES('Air Carriers', '(205) 555-1212');" ' Execute the SQL statement.

objCommand.Execute RecordsAffected:=lRecordsAffected, _

Options:=adCmdText Or adExecuteNoRecords ' Check for errors. Only one record should have been affected. If lRecordsAffected <> 1 Then Err.Raise _ Number:=vbObjectError + 1024, _

Description:="Error executing INSERT statement."

' Retrieve the primary key generated for our new record. objCommand.CommandText = "SELECT @@IDENTITY;" Set rsData = objCommand.Execute(Options:=adCmdText) ' Check for errors. The recordset should contain data. If rsData.EOF Then Err.Raise _

Number:=vbObjectError + 1024, _

Description:="Error retrieving primary key value." ' Store the primary key value for later use. lKey = rsData.Fields(0).Value rsData.Close

''''' UPDATE the record we just created ''''''''''''''''''' ' Load the SQL string into the Command object. objCommand.CommandText = "UPDATE Shippers " & _ "SET [Business Phone]='(20 6) 546-0086' " & _ "WHERE ID=" & CStr(lKey) & ";" ' Execute the SQL statement.

objCommand.Execute RecordsAffected:=lRecordsAffected, _

Options:=adCmdText Or adExecuteNoRecords ' Check for errors. Only one record should have been affected. If lRecordsAffected <> 1 Then Err.Raise _ Number:=vbObjectError + 1024, _

Description:="Error executing UPDATE statement."

''''' DELETE our record from the database ''''''''''''''''''' ' Load the SQL string into the Command object. objCommand.CommandText = "DELETE FROM Shippers " & _

"WHERE ID = " & CStr(lKey) & ";" ' Execute the SQL statement.

objCommand.Execute RecordsAffected:=lRecordsAffected, _

Options:=adCmdText Or adExecuteNoRecords ' Check for errors. Only one record should have been affected. If lRecordsAffected <> 1 Then Err.Raise _ Number:=vbObjectError + 1024, _

Description:="Error executing DELETE statement."

ErrorExit:

' Destroy our ADO objects. Set objCommand = Nothing Set rsData = Nothing

Exit Sub

ErrorHandler:

MsgBox Err.Description, vbCritical Resume ErrorExit End Sub

Quickly review what you've done in this procedure. First you inserted a new record into the Shippers table. Then you retrieved the primary key that had been assigned to that new record by the database (more on this in a moment). Next you used the primary key of your new record to locate it and modify the telephone number in its Business Phone field. Finally, you used the primary key of the record to locate it and delete it from the database.

Important things to note about this procedure:

□ You used the same ADO Command object throughout the procedure. All that was required to execute different commands was to load new SQL statements into the Command.CommandText property.

□ The process of preparing and executing the command and then checking for errors upon completion was identical for all three types of action query.

□ After inserting a new record in the first part of the procedure, you needed to retrieve the primary key value that had been assigned to that record by the ID AutoNumber field. You did this by querying the value of the @@IDENTITY system variable. This is a variable maintained by the Access database that holds the value of the most recently assigned AutoNumber field. In most cases, you must be sure to query this value immediately after performing the insert. The @@IDENTITY variable is a database-wide variable, so your primary key value will be overwritten if any other user performs a similar insert before you query it. You prevented the possibility of this occurring in this example by opening the database in exclusive mode (note the Mode argument in the connection string).

Was this article helpful?

0 0

Post a comment