Databases are the strong, silent type. Day in and day out, they house and keep our data in check, sitting silently in the background until we query them for information. Equally strong and silent is the technology that lets scripts interact with databases: ActiveX Data Objects (ADO). Using ADO, you can interact with not only a variety of databases, including Microsoft Access, Microsoft SQL Server, and Oracle, but also other data stores such as Active Directory (AD) and Microsoft Excel. You can even use ADO to work with data in comma-separated value (CSV) text files.
Perhaps it's because ADO is the strong, silent type that not much has been written about it compared with how much coverage other technologies such as Windows Management Instrumentation (WMI) and Active Directory Service Interfaces (ADSI) have seen. If you conduct a search on the Scripting Pro VIP Web site you'll run across a lot of articles that discuss scripts in which ADO is used but few articles that explain how to use it. The most notable articles are "Rem: Obtaining Data from a SQL Server Database" by Mike Otey and "An ADSI Primer, Part 5: Using ADO to Script Fast ADSI Searches" by Alistair G. Lowe-Norris. "Rem: Obtaining Data from a SQL Server Database" discusses how to use ADO in VBScript code to access a SQL Server database, read the data from a table, and write that data to a file. "An ADSI Primer, Part 5: Using ADO to Script Fast ADSI Searches" is part of a series on ADSI, so it concentrates on how to use ADO for accessing AD. The article provides an introduction to the ADO objects, properties, and methods you use for that purpose.
To give ADO the coverage it deserves, Scripting Pro VIP will be running several articles that explain how to use it. We'll cover the basics, but if there's a specific topic you'd like covered, please let me know via email. Look for the ADO series to start in early spring 2008.
In the meantime, if you're interested in seeing ADO in action, here are some of the scripts that we've provided:
- For an example of how to use ADO to work with CSV text files, check out Bill Stewart's articles "Gathering File System Data" and "A Scripting Solution for Cataloging Your File System." These articles discuss FileDB.wsc, a Windows Script Components (WSC) object that saves information about files to a CSV text file.
- For examples of how to use ADO to work with AD data, take a look at Updateattribs.vbs in the article "Updating AD Attributes—Revisited" by Steve Seguis. This script updates attributes for AD objects, using a CSV file or an Excel spreadsheet as an input file. Chang Lu's ScriptTemplate.vbs script in "Don't Let Your AD Scripts Hang on You" also uses ADO to work with AD data. If you prefer JScript or Perl, check out Bill Stewart's LastLogon.js script in "Finding a User’s Last Logon" or Dave Roth's FindEmail.pl script in "Locating Email Accounts in AD."
- For examples of how to use ADO to work with databases, see "Script Inventories Remote Computers and Writes the Information to a Database" or "Display Tables’ Contents in a Browser." In "Script Inventories Remote Computers and Writes the Information to a Database," Magni Mauro provides a script, Computer_Inventory.vbs, that gathers inventory information from remote computers, then creates and populates a database with that information. In "Display Tables’ Contents in a Browser," Arthur Zubarev offers a script, QueryTable.vbs, that queries a table on a local or remote database server and displays that table's contents in a browser.
You can find other scripts that use ADO by going to the Scripting Pro VIP Web site and performing a keyword search on ADO.