Updating Records in MS Access

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.

{mospagebreak title=Form and Data Access Page based update}

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.

{mospagebreak title=Updating data using the Update action query}

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.

{mospagebreak title=Updating the column using ADO}

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.

2 thoughts on “Updating Records in MS Access

  1. There are more ways than one to update records in an Access database. This article discusses the various ways you can update records in a single table. Some are simple and effortless using the GUI, the others may need some coding. Modifying a SELECT query is another option. It is always good to know more than one way.

    Sorry for not providing the commetn as soon as the article was posted. I was away on a holiday. I take this opportunity to wish my readers a very happy and evenful 2007. Looking forward to your comments. Jay

  2. Jayaramji,

    Explanation of the topic is so nice and even novice can easily understand the concept. Thanks. keep it up.

    Rama Mohana Rao P
    Hyderabad

[gp-comments width="770" linklove="off" ]