FunctionX Tutorials - Lesson 26: Records Maintenance

	Records Maintenance

Creating Records on a Form

Introduction

Editing or Updating a record consists of changing an existing value. Both Microsoft Access and the SQL provide various tools and techniques to create and maintain records.

Allowing Record Addition on a Form

To make data entry possible, the form is equipped with a Boolean property named AllowAdditions. The default value of this property is True. If you want to prevent the user from adding a new record to a form, set its AllowAdditions property to False. Here is an example:

Private Sub cmdRecordAddition_Click()
    AllowAdditions = False
End Sub

The user would be able to change existing records, even possibly to delete an existing record but not create a new record.

Required or Null Fields

When performing data entry, you can expect the user to skip any column whose value is not available and move to the next. In some cases, you may want to require that the value of a column be specified before the user can move on. Such a field is referred to as required. If you are creating the table in the Design View, to require that the user enter a value for a particular column, in the lower section of the window, use the Required Boolean property.

If you are programmatically creating the column using SQL, if you want to let the user add or not add a value for the column, type the NULL keyword on the right side of the data type. If you want to require a value for the column, type NOT NULL. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "FirstName TEXT NULL, " & _
                 "LastName VARCHAR NOT NULL);"
End Sub

In this case, when performing data entry, the user must always provide a value for the LastName column in order to create a record. If you omit to specify the nullity of a field, it is assumed to be NULL.

Data Entry and the Uniqueness of Records

The Unique Option

In a relational database, one of the primary concerns of records is their. If you are visually creating or managing a table, to make sure the values of a field are unique:

If you are working in the Datasheet View of the table, click any cell of the column. On the Ribbon, click Fields. In the Field Validation section, click the Unique check box
If you are working in the Design View of the table:
- Make the column the primary key of the table
- Click the column in the top side of the window. In the bottom part, set the Indexed property to Yes (No Duplicate)

To let you create a column whose values are unique, the SQL provides the UNIQUE keyword. To apply it on a column, after the data type, type UNIQUE. Here is an example:

CREATE TABLE Students
(
    StudentNumber int UNIQUE,
    FirstName varchar(24),
    LastName varchar(24) NOT NULL
);

When a column has been marked as unique, during data entry, the user must provide a unique value for each new record created. If an existing value is assigned to the column, this would produce an error:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Students" & _
                 "(" & _
                 "  StudentNumber int UNIQUE," & _
                 "  FirstName varchar(24)," & _
                 "  LastName varchar(24)" & _
                 ");"

    DoCmd.RunSQL "INSERT INTO Students VALUES(24880, 'John',  'Scheels');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(92846, 'Rénée', 'Almonds');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(47196, 'Peter', 'Sansen');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(92846, 'Daly',  'Camara');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(36904, 'Peter', 'Sansen');"
End Sub

When the fourth record is entered, since it uses a student number that exists already, the database engine would produce an error.

An Automatic Long Integer

In a database, one of the most regularly used data type for primary keys is the natural number (Integer or Long). Instead of manually adding numbers to a field, Microsoft Access can automatically generate unique numbers for each record. To automatically have a unique identifier associated with each new record in the database, visually create a field whose data type is AutoNumber. There are two main ways you can apply the AutoNumber type to a column:

Start a new table in the Datasheet View. In this case, a new field named ID is automatically created. Behind-the-scenes, the data type applied to this column is the AutoNumber type (you shouldn't try to change this type)
Start a new table in Design View. Create a field and set its data type to AutoNumber

To support automatic numbers, the SQL provides data types named COUNTER and AUTOINCREMENT. Here is an example of using it:

CREATE TABLE TimeSheets
(
    TimeSheetID COUNTER,
    FullName CHAR(50)
);

If you create a field that uses either the AutoNumber data type in the Datasheet View or the Design View of a table, or the COUNTER or AUTOINCREMENT type in SQL, when performing data entry, the database engine would automatically generate a unique number for that field. This means that you don't provide a value for that field. Here is an example:

INSERT INTO TimeSheets(FullName) VALUES('Joan Sons');

Every time you create a new record, the value of the automatic number-based field is incremented by 1. Here is another example:

INSERT INTO TimeSheets(FullName) VALUES('Peter Mukoko');

Unlike the AutoNumber type that starts the records at 1, the COUNTER and AUTOINCREMENT types allow you to specify by what value to start counting. To provide this information, add the parentheses to the COUNTER or AUTOINCREMENT type. In the parentheses, enter the value by which to start. Here is an example:

CREATE TABLE Payrolls
(
    PayrollID counter(1001),
    EmployeeNumber text(10)
);

In this case, the first record will have a value of 1001 for the field. The second record will be 1002. Here are examples:

INSERT INTO Payrolls(EmployeeNumber) VALUES('74-085');
INSERT INTO Payrolls(EmployeeNumber) VALUES('38-475');

Instead of incrementing the records by 1, the COUNTER and the AUTOINCREMENT types allow you to specify by what range to increment. To provide this information, in the parentheses of COUNTER() or AUTOINCREMENT(), after the starting value, add a comma and the desired value. Here is an example:

CREATE TABLE Stations
(
    StationNumber counter(10070, 25),
    StationName varchar(50)
);

This time, the first record will have a value of 10070 for the field. The value of the second record will be incremented by 25, and so on.

Editing or Updating Records

Allowing Editing of Records on a Form

To allow you to control whether the user can edit a record, the form is equipped with a Boolean property named AllowEdits. By default, this property is set to True. If you want to prevent the user from changing the values of a record, set this property to False.

Editing/Updating a Value With SQL

To edit a value using SQL, start with the UPDATE keyword and follow this formula:

UPDATE table-name
SET column-name = new-value

In our formula, the table-name must be the name of a table in the selected or current database. If you provide a name of a table that cannot be found on the database, you would receive a 3078 error:

If you provide a name of a table that cannot be found on the database, you would receive a 3078 error

The column-name must be a valid column in the table-name table. If you provide a column that is not found on the table, you may receive a 3073 error. The new-value of our formula is the value that will replace the old one. If you provide a value that is not compatible with the data type of the column-name, you would receive a message that states "Microsoft Access didn't update the field due to a type conversion failure...". If you use this formula, all records of the column-name would be given the new-value. Here is an example:

Private Sub cmdSetEmployeesSalaries_Click()
    DoCmd.RunSQL "UPDATE Employees SET HourlySalary = 20.00;"
    MsgBox "The salaries of all employees have been set to $20.00.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub

In some cases, you may want to change only one or more particular values. To do this, you must specify a condition that would be used to identify a record. The formula to use is:

UPDATE table-tame
SET column-name = new-value
WHERE condition

The condition specifies how the value will be located. Here is an example:

Private Sub cmdSetEmployeesSalaries_Click()
    DoCmd.RunSQL "UPDATE Employees " & _
                 "SET HourlySalary = 15.50 " & _
                 "WHERE Title = 'Accounts Associate';"
    MsgBox "The salaries of all employees have been set to $20.00.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub

Form Events Related to Record Maintenance

Signaling an Upcoming Record Change

When a user comes to a record and finds out that there is a missing or an incorrect value, she may want to make a correction. This can be done by clicking in a text-based field and use the keyboard to make the change. A user can also click something to make a selection

To signal to you that a record is going to be changed, the form has an event named Dirty. The structure of this event is:

Private Sub Form_Dirty(Cancel As Integer)

End Sub

This event runs as soon as the user makes a change to the value of a control. The event runs before the change is actually applied. This event carries one piece of information: A Boolean argument. If you want to let the user make changes on records, either ignore (don't use) the argument or set the Cancel argument to False or 0. If you want to prevent the user from making changes, set the argument to either True or any numeric value other than 0 (positive or negative).

Refreshing a Form

When the values in the controls of a record have been changed, the form doesn't automatically update the controls. To take care of this, you can refresh the record. To let you do this programmatically, the Form class is equipped with a method named Refresh. Here is an example of calling it:

Private Sub cmdRefresh_Click()
    Refresh
End Sub

An alternative is to use the DoCmd object that is equipped with a method named RefreshRecord. Here is an example of calling it:

Private Sub cmdRefreshRecord_Click()
    DoCmd.RefreshRecord
End Sub

Re-Querying a Form

You might have specified the record source of a form. When the records in that source change, the form does not automatically update itself. To let you take care of this, the Form class is equipped with a method named Requery. Here is an example of calling it:

Private Sub cmdQuery_Click()
    Requery
End Sub

As an alternative, the DoCmd object is equipped with a method named Requery. Here is an example of calling it:

Private Sub cmdRefreshRecord_Click()
    DoCmd.Requery
End Sub

Deleting Records

Allowing Record Deletions on a Form

Deleting a record consists of removing it from the database. To give you the ability to delete a record, the form is equipped with a Boolean property named AllowDeletions. By default, this property is set to True. If you want to prevent the user from deleting a record, set this property to False. Here is an example:

Private Sub cmdRecordDeletion_Click()
    AllowDeletions = False
End Sub

The user will still be able to perform any operation on a record as long this does not include removing the record.

Deleting Records in SQL

To programmatically delete a record using SQL, you combine the DELETE operator in the following primary formula:

DELETE FROM table-name

When this statement executes, all records from the table-name table would be removed. Here is an example:

Private Sub cmdClearCustomers_Click()
    DoCmd.RunSQL "DELETE FROM Customers"
End Sub

In this case, all customer records from a table named Customers in the current database would be deleted. An alternative to the above formula is:

DELETE [ALL] *
FROM table-name

In this formula, you use either ALL * or * as the column placeholder. You can replace it with one or more names of columns but it doesn't matter because the DELETE operator signifies that the whole record will be deleted, regardless of the column name. The table-name must be a valid name of a table. Here is an example:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE ALL * FROM Employees;"
    MsgBox "All employees have been dismissed because the company exists no more.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub

If you execute this type of statement, all records from the table would be deleted. You can specify what record to remove from a table. To do this, use the following formula of the DELETE operator:

DELETE *
FROM table-name
WHERE Condition

This time, the condition allows you to set the condition that would be applied to locate the record(s). Here is an example of specifying a condition to delete a record:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE ALL * FROM Employees WHERE EmployeeNumber = '597740';"
    MsgBox "The employee has been thanked and sent home.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub

Indexes

Introduction

An index is a list of words that makes it easy to locate information. When it comes to a database, you can ask the database engine to use the values of one or more columns as the basis of an index.

Creating an Index

To visually create an index:

If you are working in the Datasheet View of the table, click any cell of the column. On the Ribbon, click Fields. In the Field Validation section, click the Indexed check box
If you are working in the Design View of the table, click the column in the top side of the window. In the bottom part, set the Indexed property to a value other than No

To programmatically create an index in SQL, use the following formula:

CREATE [UNIQUE] INDEX index-name ON object-name(Column(s) Options);

The CREATE INDEX expression and the ON keyword are required. Like every object, an index must have a name, in this case object-name. You must also specify the table or a query that holds the list whose index you want to create. You must also specify the column(s) that has(have) the values used for the index. Here is an example of creating an index:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors" & _
                 "(" & _
                 "      ContractorCode int, " & _
                 "      FirstName varchar(20)," & _
                 "      LastName varchar(20) NOT NULL," & _
                 "      Title varchar(50) NULL," & _
                 "      HourlySalary double" & _
                 ");"
End Sub

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX LocateContractors ON Contractors(ContractorCode);"
End Sub

In most cases, you use only one column as the basis of your index. You can also use more than one column. In this case, in the parentheses of the name of the table, list the columns separated by commas. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX FindContractors " & _
                 "ON Contractors(LastName, FirstName);"
End Sub

By habit or pure tradition, most people start the name of an index with either idx or IDX_.

Options on Indexes

There are many characteristics to apply to an index.

If you create an index on a column that includes null values, the index may end up with null values. If you want the database engine to exclude null values, add a WITH DISALLOW NULL clause. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH DISALLOW NULL;"
End Sub

Another option is to ask the databsae engine to ignore null values in the index you are creating. To do this, add a WITH IGNORE NULL clause. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH IGNORE NULL;"
End Sub

In most cases, you want to make sure that all words used in an index are unique (an index with a repeating word can be confusing). Normally, this is the default characteristic of an index. Still, if you want to enforce it, you can precede the word INDEX with UNIQUE. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode);"
End Sub

In most cases, an index is created on the column(s) used as the primary key. Normally, if your table formally includes a primary key, that (those) column(s) is(are) used as the index. Otherwise, when creating an index, if you want to indicate that it is based on the column(s) used as the primary key, add a WITH PRIMARY clause to it. Here is an example:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors" & _
                 "(" & _
                 "      ContractorCode int, " & _
                 "      FirstName varchar(20)," & _
                 "      LastName varchar(20) NOT NULL," & _
                 "      Title varchar(50) NULL," & _
                 "      HourlySalary double" & _
                 ");"
End Sub

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH PRIMARY;"
End Sub

Deleting an Index

To delete an index, use the following forrmula:

DROP INDEX index-name ON table-name;

Here is an example:

Private Sub cmdDeleteIndex_Click()
    DoCmd.RunSQL "DROP INDEX IDX_Contractors ON Contractors;"
End Sub


Previous	Copyright © 2016-2022, FunctionX, Inc.	Next