Deleting Selected Records from a Database

Microsoft® Windows® 2000 Scripting Guide

« Previous | Next »   

There will undoubtedly be times when you want to delete a record or set of records from a database. For example, you might decommission the DNS Server service on a computer; in turn, you will then need to delete that computer from the database table that contains the names of your DNS servers. Or suppose you maintain three months of performance monitoring information in a database. Each month you will need to delete some of the old data to make room for the new data. For example, in April you will need to delete the data for January. After you add the April data, the database will again contain data for three months: February, March, and April.

To delete a record by using ADO, do the following:

1. Connect to the recordset.
2. Connect to the appropriate record. This is typically done by using the Find method to locate an individual record.
3. Use the Delete method to delete the record.

When you use the Delete method to delete a record, the record will be deleted as soon as the method is called. You do not need to use the Update method to apply the changes. No warning of any kind will be issued before the record is deleted.

Scripting Steps

Listing 17.19 contains a script that deletes a record from a database. To carry out this task, the script must perform the following steps:

1. Create three constants adOpenStatic, adLockOptimistic, and adUseClient and set the value of each to 3.

   These constants will be used to configure the CursorLocation, CursorType, and LockType for the connection.

2. Create an instance of the ADO Connection object (ADODB.Connection).

   The Connection object makes it possible for you to issue queries and other database commands.

3. Create an instance of the ADO Recordset object (ADODB.Recordset).

   The Recordset object stores the data returned from your query.

4. Use the Connection object Open method to open the database with the DSN Inventory.

   Be sure to append a semicolon (;) to the DSN name.

5. Set the CursorLocation to 3 (client side) by using the constant adUseClient.
6. Use the Recordset object Open method to retrieve all the records from the Hardware table.

   The Open method requires four parameters:

   • The SQL query ("SELECT * FROM Hardware")
   • The name of the ADO connection being used (objConnection)
   • The cursor type (adOpenStatic)
   • The lock type (adLockOptimistic)
7. Set the variable strSearchCriteria to ComputerName = WebServer .

   The variable strSearchCriteria will serve as the search criteria for the Find method.

8. Use the Find method to locate the computer named WebServer.
9. Use the Delete method to delete the record for the computer named WebServer.
10. Close the recordset.
11. Close the connection.

Listing 17.19   Deleting Selected Records from a Database

1 2 3 4 5 6 7 8 9 10 11 12 13 14 

Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=Inventory;" objRecordset.CursorLocation = adUseClient objRecordset.Open "SELECT * FROM Hardware" , objConnection, _     adOpenStatic, adLockOptimistic strSearchCriteria = "ComputerName = 'WebServer'" objRecordSet.Find strSearchCriteria objRecordset.Delete objRecordset.Close objConnection.Close

---

 Send us your feedback

« Previous | Next »