How to Use Database Recordsets with VBScript

Accessing the Information in an ODBC Database from VBScript

Dec 15, 2008

VBScript is a powerful computer programming language and ODBC is a simple way of connecting to databases - together they provide a versatile tool for any programmer

Recordsets are an easy way of working with the the information stored in a database - they are a set of records containing the results of a query run on the database. If the programmer, therefore, combines the versatility of VBScript programming with the simplicity of ODBC (Open Database Connectivity) then they can quickly produce a database application.

However, before using ODBC the programmer must ensure that it is set up correctly - and the first step is to install the correct database drivers.

Installing Database Drivers

ODBC enables a programmer to connect to a database whilst knowing nothing about the database and ODBC does this by making used of database drivers - each of which is unique to the particular database being used. For example, if the computer program is to use a MySQL database then the programmer will need to install MyODBC (also known as MySQL Connector/ODBC). Once that's been installed then the programmer will be able to connect to a MySQL database using VBScript (or any other programming language that supports ODBC).

Adding a Data Source to ODBC

The database can be added as a data source to ODBC; and for that the programmer will need to know:

And from that point onwards the VBScript application will be able to connect seamlessly with the database.

Connecting to an ODBC Database

The programmer must define some constants which are used when accessing data on the database: -

const adopenstatic = 3 'A static copy of a set of records

const adlockoptimistic = 3 'lock records only when calling update

const aduseclient = 3 'Uses a client-side cursor

The actual connection can now be made by using the dsn (data source name) defined for the database in ODBC:

dim conn : set conn = createobject("adodb.connection")

conn.open "dsn=my_new_database;" 'Connection handled by ODBC

It should be noted at this point that MDAC (Microsoft Data Access Components) are needed as well as the correct ODBC driver (for more information on MDAC read How to Use VBScript to Create Recordsets).

Obtaining Information from the Database

With the connection in place he information stored in the database can be accessed, for example the list of tables on the database can be obtained:

dim tlist : set tlist = createobject("adodb.recordset")

tlist.cursorlocation = aduseclient

tlist.open "show tables" , conn, adopenstatic, adlockoptimistic

This code is likely to used more than once in different applications and so it is sensible to store it in a vbs file (for example h:\vbscript\odbc.vbs). This vbs file can then be used as a library for other VBScript applications.

Using the ODBC VBScript Library

A programmer can load the library file into any VBScript application by making use of the FSO (File System Object):

dim fso : set fso = createobject("scripting.filesystemobject")

executeglobal fso.opentextfile("h:\vbscript\odbc.vbs",1).readall

set fso = nothing

The VBScript program can now use the objects defined in the library - in this case to display a list of the tables in the database:

dim table_list

tlist.movefirst

while not tlist.eof

table_list = table_list & tlist.Fields(0) & vbcr 'Carriage return

tlist.movenext

wend

msgbox table_list

Ending the Database Session

And so the programmer can create a complete database application with just those simple lines of VBScript code.

The copyright of the article How to Use Database Recordsets with VBScript in Windows Programming is owned by Mark Alexander Bain. Permission to republish How to Use Database Recordsets with VBScript in print or online must be granted by the author in writing.