Microsoft Office Tutorials and References
In Depth Information
The DELETE Statement
If the ContactName field had many empty (referred to as Null ) values, and you wanted to ill those
empty spaces with the word Unknown, the following example would accomplish that:
UPDATE Vendors
SET ContactName = ‘Unknown’
WHERE ContactName IS NULL
The dElETE statement
The DELETE statement deletes one or more rows from a table. If you want to delete the vendor
named Widgets, Inc., you would use the WHERE statement to specify which value in which column
should identify the record for Widgets, Inc. The VendorID column is the perfect column for this task
because a large company might have two vendors with the same name.
The following SQL statements would delete the record from the Vendors table that has the value
1234 in the VendorID column:
DELETE FROM Vendors
WHERE VendorID = ‘1234’
Make absolutely certain you specify the WHERE clause, because if you do not,
every row from the Vendors table would be deleted. If an empty table is what
you want, this fictional sequence would accomplish that:
DELETE FROM Vendors
Odds are, you don’t want an empty table with all rows deleted from it. The
kicker is, after the rows are deleted, you cannot undo that action as you can in
Excel. Unless you are good friends with an experienced database programmer
who might (or might not) be able to recover your unintentionally deleted rows,
take heed and always specify the WHERE clause in your SQL DELETE actions.
Try iT
This lesson introduced the fundamentals of ADO and SQL. You will see several examples in
Lesson 29 of VBA macros that show how to program ADO with SQL to interact with Access
databases from Excel.
Here is a way to get a head start on the instruction in Lesson 29 — become familiar with database
tables. Open Access and create a new database. Create a new table and enter some fictional data such
as a mailing list with fields for FirstName, LastName, StreetAddress, City, State, Country, and Postal
Code. Make a dozen or so entries and get a feel for navigating and editing a database table. For
example, Figure 24-1 shows a table in Access being populated with hypothetical employee information,
such as you might see in a company’s personnel database.
Search JabSto ::




Custom Search