Often to satisfy business requirements there arises a need to change some value or values in a table or tables. Sometimes it may be to make a change to a large number of records, and sometimes the change will only be applied to individual records -- updating a person's last name or phone number, for example. Microsoft Access offers a variety of options for making updates to records.
Updating the data from a query in MS Access (MDB) may not always be possible. This is especially true if there are two or more tables involved in the update process. In general data may be updatable, updatable under certain conditions, or not updatable at all. Please read the Microsoft KB article link provided at the conclusion of this tutorial.
This tutorial shows various ways you can update the record in a single table. Future articles will consider cases where more than one table may be involved. This article uses a table created out of choosing only a few columns from a table in the Northwind database to keep the discussion simple without compromising the concept.
Creating the table used in the tutorial
In a previous article on action queries, the Make Table query was discussed. Using the procedure discussed in the tutorial, a TestProductQry was created using the following SQL View of the Make Table query.
TestProductQry
SELECT Products.ProductName, Products.QuantityPerUnit,
Products.UnitPrice, Products.UnitsInStock
INTO TestProducts
FROM Products;
The above query was run which created the TestProducts table. After the table was created a primary key was added so that a form and data access page based update can be described. The design view of the table TestProducts along with the original table is shown in the next picture.
You may edit a record using a form. Create a form as shown by using the Form Wizard. If you right click the little black rectangle at the top left corner, you can access the form's properties as shown. Here Allow Deletions and Allow Additions have been turned off by setting their values to No.
When you run the form you can change the values in the various columns except the primary key field. You may also make changes to multiple columns. You will be able to type the change in the text box; after you have made your edits, you just need to move to another record (movement can be forward or backward). The modification takes place immediately so that you can come back and verify.
Based on the form you may also create a data access page as shown, using which you can update as well. The page property is also shown alongside. It is clear that the data is updatable; the recordset is an updatable snapshot.
Similar to the procedure used in the form, just navigate to the item, edit its content, move to another record using the navigational keys and the update becomes effective. The displayed data access page is shown in the next picture.
MS Access provides action queries to modify data. Action queries are used to make changes to a large number of records with a single click. These queries can be created using the query designer tool. Creating an update query using the designer is really very simple. First create a select query as shown.
Right click the border between the design and grid to pop open the window with several menu items as shown. Click on the menu item labeled Update Query.
The grid changes somewhat, allowing to choose which column or columns you need in the table, and showing you where you can type in the changes as shown. Updating the query requires the table, the criterion for which the changes have to be made and the changes themselves.
You may also look at the SQL View of the query which is shown in the next paragraph.
UPDATE TestProducts
SET TestProducts.ProductName = "Milk Chocolate",
TestProducts.UnitsInStock = 500
WHERE (((TestProducts.ProdID)=48));
The value that exists before the Update Query is executed is shown in the next picture .
After making entries, when you try to close the query editor, you will be asked whether you want to save and you may give a name to the query.
Queries in the main screen of the access file can be easily identified as they are shown with different types of icons. You may run the Update Query you created by double clicking its icon. This immediately brings up the message window, letting you know about the changes that are going to be made.
If you click on the Show Help button you will reveal the following text.
The query you are about to run is an update query. Unlike most
queries that display data in Datasheet view, an update query
updates one or more fields in a specific table in a single
operation. If your intention is to perform a bulk update
operation, click Yes. To avoid being prompted when you run such
queries, on the Tools menu, click Options, then
click the Edit/Find tab. Under Confirm, clear the Action
queries check box. To exit without running the query, click No.
To find out more about what this query does, click No to close
this message, then open the query in Design view (in the
Database window, select the query, and click Design). Then do
one or more of the following: To see which fields will be
updated by the query, see the names in the Field row in the
design grid. The Table row shows the name of the table that
contains the field. To see how the values will be changed,
look at the expression in the Update To row. To see the
criteria, if any, for selecting records for the bulk operation,
see the Criteria and the or row, and any additional rows below
the or row that display selection criteria. If these rows are
empty, all rows in the table listed in the Table row will be
updated. To preview the records that will be updated, click View
on the toolbar. The data as it exists before the update operation
will be displayed in Datasheet view.
When you click on the Yes button, you will get another message from the program letting you know how many rows are going to be modified as shown.
Now when you click on the Yes button one more time the changes will be made to the table as shown in the next picture. As you can see, you can modify one or more columns at the same time. This was also seen in the SQL View earlier.
While the earlier methods discussed the options available in the MS Access program for updating the records, records can also be updated programmatically using ADO. This can be carried out within the MS Access application, or from an independent Visual Basic, or an ASP application using the SQL update statement whose syntax is shown here.
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}
In order to access the properties, methods and events of ADO you need to add a reference to the ADO library as discussed in several earlier tutorials. Now to go to the load event of an MS Access form, type in the code shown in the next paragraph. Relevant sections which need some explanation are commented. Basically you should use the recordset object which takes the SQL update statement similar to the one produced by the MS Access query designer. You also display the field values before you run the update and again after you run the update in order to display the changes made. These are displayed using the debug statements.
Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJayMy Documentscrud.mdb;" & _
"Persist Security Info=False"
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
conn.Open strg
MsgBox ("Open")
Dim strsql As String
strsql = "Select * from TestProducts where ProdID=10"
rst.CursorLocation = adUseClient
rst.Open strsql, conn, adOpenKeyset, adLockOptimistic
MsgBox ("Cursor Location: " & rst.CursorLocation)
Dim strresult As String
strresult = ""
strresult = strresult + "Prod ID= " & rst.Fields(0).Value & vbCrLf
strresult = strresult + "Product Name= " & rst.Fields(1).Value & vbCrLf
strresult = strresult + "Quantity Per Unit= " & rst.Fields(2).Value &
vbCrLf
strresult = strresult + "Unit Price= " & rst.Fields(3).Value & vbCrLf
strresult = strresult + "Unit In Stock= " & rst.Fields(4).Value & vbCrLf
'Printout the columns for ProductID=10 before updating the values
Debug.Print strresult
rst.Close
'Change product name to 'Japanese Tea'
strupdate = "UPDATE TestProducts SET ProductName= 'Japanese Tea'
WHERE ProdID=10"
MsgBox (strupdate)
'Update the table changing column using update statement
rst.Open strupdate, conn, adOpenKeyset, adLockOptimistic
rst.Open strsql, conn, adOpenKeyset, adLockOptimistic
'reselect the same columns to verify if the change has taken place
strnew = ""
strnew = strnew + "Prod ID= " & rst.Fields(0).Value & vbCrLf
strnew = strnew + "Product Name= " & rst.Fields(1).Value & vbCrLf
strnew = strnew + "Quantity Per Unit= " & rst.Fields(2).Value & vbCrLf
strnew = strnew + "Unit Price= " & rst.Fields(3).Value & vbCrLf
strnew = strnew + "Unit In Stock= " & rst.Fields(4).Value & vbCrLf
'Print out the new values after the update has been made
Debug.Print strnew
rst.Close
conn.Close
End Sub
When the form loads, the table gets updated and the result of the debug statements written to the immediate window are shown in the next paragraph.
Product Name= Ikura
Quantity Per Unit= 12 - 200 ml jars
Unit Price= 31
Unit In Stock= 31
Prod ID= 10
Product Name= Japanese Tea
Quantity Per Unit= 12 - 200 ml jars
Unit Price= 31
Unit In Stock= 31
Summary
The MS Access query designer is a convenient tool for creating update action queries. It is also a good learning tool for studying data modifying SQL statements although the syntax is different. Browsing the data view of the table and making changes, Form and Data Access Pages based updates are other options. ADODB offers a programmatic way of modifying data which can use both types of CursorLocation property. Updating records using ADO enables you to use a lot more features than discussed here. Before you want to work with update queries make sure you consult the online help in MS Access and this Knowledge Base article on the Microsoft site.
Microsoft Access 
/ 17 
Using the Recordset with MS Access and ADO
