Updating Records in MS Access

(Page 1 of 4 )

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.

Next: Form and Data Access Page based update >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

Comments On: Updating Records in MS Access

· There are more ways than one to update records in an Access database. This article...    · Jayaramji, Explanation of the topic is so nice and even novice can easily...   >>> Post your comment now!