Script Tips
Connect to Your Data
Another ADO trick that allows you to connect to your data via scripting.
I thought it’d be fun to share a powerful, yet simple scripting technique for you. One of the coolest things you can do with a script is to pull information from databases, and ActiveX Data Objects (ADO) makes it pretty easy.
Start by connecting to the database:
Dim objCn
Set objCn = CreateObject("ADODB.Connection")
objCn.Open "connection string"
You’ll need a connection string that tells ADO where to find the database; cheat by referring to www.connectionstrings.com to get the right connection string for whatever type of database you want to work with (SQL Server, Access, Excel, text files, you name it).
Next, use a SQL query to retrieve some data from the database:
Dim strQuery, objRs
strQuery = "SELECT ComputerName FROM Computers"
Set objRs = objCn.Execute(strQuery)
The objRs variable is now a recordset, or set of rows from a table named Computers in the database. The only column retrieved from the table was the one named "ComputerName"; replace that with "*" to retrieve all the table’s columns. To view the data:
Do Until objRs.EOF
WScript.Echo objRs("ComputerName")
objRs.MoveNext
Loop
Use the objRs("column_name") format to access the contents of any given column for the current row; objRs.MoveNext moves to the next row so that you can access it’s data. objRs.EOF is True when you’ve moved past the last row, and so the loop will exit. It’s polite to clean up after yourself at that point:
objRs.Close
objCn.Close
And that’s it: Quick and dirty database scripting in VBScript, using ADO.
About the Author
With more than fifteen years of IT experience, Don Jones is one of the world’s leading experts on the Microsoft business technology platform. He’s the author of more than 35 books, including Windows PowerShell: TFM, Windows Administrator’s Scripting Toolkit, VBScript WMI and ADSI Unleashed, PHP-Nuke Garage, Special Edition Using Commerce Server 2002, Definitive Guide to SQL Server Performance Optimization, and many more. Don is a top-rated and in-demand speaker and serves on the advisory board for TechMentor. He is an accomplished IT journalist with features and monthly columns in Microsoft TechNet Magazine, Redmond Magazine, and on Web sites such as TechTarget and MCPMag.com. Don is also a multiple-year recipient of Microsoft’s prestigious Most Valuable Professional (MVP) Award, and is the Editor-in-Chief for Realtime Publishers.