QTP: Connecting to SQL Database

This post describes how you can connect to a SQL Database, execute an SQL statement and loop through the results from QTP using vbscript. The process is pretty straight forward, the main issue is getting the correct connection string for your database.

In the example I am connecting to Access database Northwind. But it should be the same code for connecting to any database, just a different connection string. In my current project we are using this to connect to a IBM DB2 database and it works great.

Option Explicit

Dim connectionString, sql, cn, cmd, rs

' Connection String required to connect to MS Access database
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb;"
' SQL statement to run
sql = "select EmployeeID,LastName from employees"

' Create ADO Connection/Command objects
set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")

' Open connection
cn.open connectionString
' Associate connection object with command object
cmd.ActiveConnection = cn
' Set the SQL statement of the command object
cmd.CommandText = sql

' Execute query
set rs = cmd.execute

' Enumerate each row in the result set
while rs.EOF <> true and rs.BOF <> True
' Using ordinal
msgbox "Employee ID: " & rs(0)
' Using name
msgbox "Last Name:" & rs("LastName")

rs.movenext
wend

' Close Connection
cn.Close