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
Don Jones is a multiple-year recipient of Microsoft’s MVP Award, and is Curriculum Director for IT Pro Content for video training company Pluralsight. Don is also a co-founder and President of PowerShell.org, a community dedicated to Microsoft’s Windows PowerShell technology. Don has more than two decades of experience in the IT industry, and specializes in the Microsoft business technology platform. He’s the author of more than 50 technology books, an accomplished IT journalist, and a sought-after speaker and instructor at conferences worldwide. Reach Don on Twitter at @concentratedDon, or on Facebook at Facebook.com/ConcentratedDon.