When a table contains records, you can view and manipulate them as you see fit. While examining a table, you may come to a record whose values are not appropriate. Record maintenance consists of editing or deleting values of records of a table.

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

UPDATE TableName

SET ColumnName = NewValue

In our formula, the TableName factor must be the name of a table in the selected or current database. The ColumnName must be a valid column in the TableName table. The NewValue factor of our formula is the new value that will replace the old one. If you use this formula, all records of the ColumnName would be given the NewValue. Here is an example:

Private Sub btnChangeRating_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnChangeRating.Click

        Dim conVideos As New ADODB.ConnectionClass



        conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\VideoCollection.mdb'", _

                       Nothing, Nothing, 0)

        conVideos.Execute("UPDATE Videos SET Rating = 'R';")

        MsgBox("The rating of each record has been set to R.")

        conVideos.Close()

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 TableName

SET ColumnName = NewValue

WHERE Condition

The Condition factor specifies how the value will be located. Consider the following table of records:

From this table, imagine that you want to replace Adryan Line with Adrian Lynn. The TableName is Videos. The column that holds the value is named Director. The NewValue is Peter Segal. The Condition can be that the shelf number is DA-4093. Based on this, to edit the value, you can use the following code:

UPDATE 	Videos

SET 	Director = "Adrian Lynn"

WHERE 	ShelfNumber = "DA-4093"

This can be done in code as follows:

Private Sub btnChangeDirector_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnChangeRating.Click

        Dim conVideos As New ADODB.ConnectionClass



        conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\VideoCollection.mdb'", _

                       Nothing, Nothing, 0)

        conVideos.Execute("UPDATE Videos " & _

                    "SET Director = 'Adrian Lynn' " & _

                    "WHERE ShelfNumber = 'DA-4093';")

        

        conVideos.Close()

End Sub

In the same way, in the list of videos, notice that the videos titled "The Bone Collector" and "Clear And Present Danger" have a name of director that looks the same. After checking the videos, you find out that they were actually directed by the same person. So you decide to programmatically edit it. You can write the SQL statement as follows:

UPDATE 	Videos

SET 	Director    = "Phillip Noyce"

WHERE 	ShelfNumber = "DG-9048"

Before executing the statement, you check the table again but find out that, either by mistake or something else, another video is using the same shelf number. This means that if you execute the statement, any video that uses the same shelf number would have its director replaced, including those videos that don't have the same error. The solution to this is to make sure that you always have a way to distinguish each video from another. This is why a column with a COUNTER data type can be highly helpful because each record would have a unique value in this column. Using this feature, the problem would be solved as follows:

UPDATE 	Videos

SET 	Director = "Phillip Noyce"

WHERE 	VideoID  = 3

This would be done programmatically as follows:

Private Sub btnChangeRating_Click(ByVal sender As System.Object, _

                    ByVal e As System.EventArgs) Handles btnChangeRating.Click

        Dim conVideos As New ADODB.ConnectionClass



        conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\VideoCollection.mdb'", _

                       Nothing, Nothing, 0)

        conVideos.Execute("UPDATE 	Videos " & _

                          "SET Director = 'Phillip Noyce' " & _

                          "WHERE 	VideoID  = 3;")

        conVideos.Close()

End Sub

This time, because we know that only one video has a VideoID equal to 3, only its record will be changed.

Deleting a Record

Deleting a record consists of removing it from a table (or a form). To delete a record, you combine the DELETE operator in the following primary formula:

DELETE FROM TableName

When this statement is executed, all records from the TableName table would be removed. Here is an example:

Private Sub btnRemoveRecords_Click(ByVal sender As System.Object, _

		ByVal e As System.EventArgs) Handles btnRemoveRecords.Click

        Dim conVideos As New ADODB.ConnectionClass



        conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\VideoCollection.mdb'", _

                       Nothing, Nothing, 0)

        conVideos.Execute("DELETE FROM Videos;")



        MsgBox("All records from the Videos table have been deleted.")

        conVideos.Close()

End Sub

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

DELETE *

FROM TableName

In this formula, you use the * operator 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 TableName must be a valid name of a table in the specified or the current database. Here is an example:

Private Sub btnRemoveRecords_Click(ByVal sender As System.Object, _

		ByVal e As System.EventArgs) Handles btnRemoveRecords.Click

        Dim conVideos As New ADODB.ConnectionClass



        conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\VideoCollection.mdb'", _

                       Nothing, Nothing, 0)

        conVideos.Execute("DELETE * FROM Videos3;")



        MsgBox("All records from the Videos table have been deleted.")

        conVideos.Close()

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 TableName

WHERE Condition

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

Private Sub btnRemoveRecords_Click(ByVal sender As System.Object, _

		ByVal e As System.EventArgs) Handles btnRemoveRecords.Click

        Dim conVideos As New ADODB.ConnectionClass



        conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                       "Data Source='C:\Programs\VideoCollection.mdb'", _

                       Nothing, Nothing, 0)

        conVideos.Execute("DELETE * FROM Videos WHERE Director = 'Phillip Noyce';")



        MsgBox("All videos directed by Phillip Noyce have been deleted.")

        conVideos.Close()

End Sub

When this code runs, all videos directed by Phillip Noyce would be deleted from the table. Instead of deleting all records like this, you may want to remove only one particular video. To do this, you must set a condition that sets that record apart. Once again, the condition can be easily handled by the COUNTER column.