Script Tips

Quicker Queries

Add database queries to your scripts.

Here's a quick-and-dirty way to incorporate databases—Access, SQL Server, Excel spreadsheets, or whatever—into your scripts.

Start by defining an ODBC Data Source Name (DSN) using the Data Sources console (located under Administrative Tools). You'll give the DSN—let's say "MyData"—and point it to a data source, which can be nearly anything: an Excel spreadsheet, an Access database, and so forth.

In your script, you'll create an ActiveX Data Objects (ADO) Connection object, which will open the DSN and attach to the database you've selected:

Dim oCN
Set oCN = CreateObject("ADODB.Connection")
oCN.Open "MyData"

Simple enough so far. Now for some quick terminology: You'll be querying one or more columns from one or more rows in a table. In Excel-speak, that's columns, rows, and sheets; Access uses columns, rows, and tables, as does SQL Server. Let's say you have a table (or Excel sheet) named MyTable; it contains columns named UserName and UserEmail. You want to query these two columns for every row in the table (or sheet) and display that information. You'll use an ADO RecordSet object to do your dirty work:

Dim oRS
Set oRS = oCN.Execute("Select UserName,UserEmail " & _
  "FROM MyTable")
Do Until oRS.EOF
  WScript.Echo oRS("UserName") & " = " & oRS("UserEmail")

This uses the previously defined Connection object to query your two columns from the table (or sheet), returning a recordset (also called a rowset, since it is a set of rows). Then, a Do…Loop construct runs until the end of the recordset. For each row, you'll see the contents of the UserName and UserEmail columns; the MoveNext method brings up the next row.

There you have it: Simple database queries from within VBScript. I've got lots more online in the ScriptVault at, if you want to see what else ADO can do.

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, 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

comments powered by Disqus
Most   Popular