Working with Databases
Microsoft® Windows® 2000 Scripting Guide
System administrators often find themselves working with large amounts of data. For example, consider a simple task such as inventorying computer hardware. If you have 10,000 computers in your organization, this task will result in an enormous amount of data. Although this data can be stored in a text file, your ability to view, modify, and analyze that data will be limited, to say the least. Instead, it is typically better to store large amounts of data in a database.
Most databases have an import feature that allows you to import data stored in other formats (such as text files). Because of this, you can save retrieved data as a text file and then open your database application and import the data. Needless to say, it would be much faster and much easier if your scripts could directly interact with a database. Using ActiveX Data Objects, scripts can do just that.
ActiveX Data Objects (ADO) are part of the Universal Data Access (UDA) technology that provides access to information across the enterprise. Like its predecessor, Open Database Connectivity (ODBC), UDA provides a common interface for communicating with SQL databases. However, UDA goes beyond database connectivity to provide access to information that is not stored in relational databases; for example, you can access information stored as part of an e-mail service, a file system, or a hierarchical database such as Microsoft Indexing Service.
Note
- ADO is sometimes referred to as ADO/OLE DB (OLE Database). This is because ADO serves as the scripting and application-level programming interface, while OLE DB serves as the system-level programming interface. ADO is required when working with scripting languages; system-level programming languages such as C++ can bypass ADO and work directly with OLE DB.
Although a complete discussion of the ADO object model is beyond the scope of this book, two objects are particularly important to administrators who need to connect to databases when writing scripts. These two objects, the Connection object and the Recordset object, are explained in more detail in Table 17.7.
Table 17.7 ADO Objects
Object |
Description |
|---|---|
Connection |
Manages the connection between your script and the database. The Connection object is used to open the database but does not return any data. The Recordset object is used to return data. |
Recordset |
Contains the data returned by your query. The data is contained in rows (referred to as records) and columns (referred to as fields). Each column is stored in a Field object in the Recordset Fields collection. Recordsets are returned by using SQL commands such as "SELECT * FROM Hardware". A recordset can represent all the records in a database or a subset, depending on how you construct your SQL queries. |
Using a DSN
To connect to a database by using ADO, you must have an OLE DB data provider; this data provider serves as the mechanism that connects you to a particular type of database (SQL Server, Microsoft Access, Active Directory, and so on). Although you can connect directly to a database by including the provider name and the path to the database as part of the connection string, a simpler method of connecting to a database is to create a data source name (DSN) for the database.
A DSN stores all the information required to connect to a database. If you create a DSN for a database, you can connect to that database by using a single line of code (for example, objConnection.Open "DSN=Inventory;"), without having to know the provider name or the physical path to the database. If you change the path to the database (for example, by moving the database to a faster hard disk), you can change the DSN rather than modifying all the scripts that connect to that database.
To create a DSN
Open Administrative Tools, and then click Data Source (ODBC).
On the System DSN tab in the ODBC Data Source Administrator dialog box, click Add.
In the Create New Data Source wizard, follow the prompts to create a DSN for your database. The steps will vary depending on the type of database. (For example, the steps required to create a DSN for a SQL Server database are different from those required to create a DSN for a Microsoft Access database.)
When you connect to the database in a script, you will make the connection by using the DSN you create with this wizard, not the name of the database itself. The ADO examples used in this chapter connect to the database by using a DSN.