Microsoft Windows 2000 Scripting Guide(c) Automating System Administration 2003

Microsoft® Windows® 2000 Scripting Guide

« Previous | Next »   

There will also be times when you need to clear a database table, deleting all the records. For example, suppose at the end of each month you import event log records into a database and then run a series of statistical analyses on that data. At the end of the next month, you might want to clear the table; that way, the next set of records imported into the database will not commingle with any previous records.

Although you can clear a database by finding and deleting each record individually, a better and faster approach is to use an SQL Delete query to delete all the records in a single operation.

Scripting Steps

Listing 17.20 contains a script that deletes all the records in a database table. 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 delete all the records from the Hardware table.

    The Open method requires four parameters:

    • The SQL query ("DELETE * FROM Hardware"). The exact query used to delete all the records will vary depending on your database. Some SQL databases use "DELETE FROM Hardware", without the asterisk, instead.

    • The name of the ADO connection being used (objConnection).

    • The cursor type (adOpenStatic).

    • The lock type (adLockOptimistic).

  7. Close the connection.

Listing 17.20   Deleting All Records in a Database Table

1 2 3 4 5 6 7 8 9 10

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 "DELETE * FROM Hardware" , objConnection, _ adOpenStatic, adLockOptimistic objConnection.Close

 Send us your feedback « Previous | Next »   
Related

Категории