How to Use VBScript to Create Recordsets

Building a Recordset Without a Database

Dec 14, 2008

A VBScript Recordset Application, Mark Alexander Bain

Recordset are set of records downloaded from database. However, with VBScript it is possible to create a recordset without a database.

A recordset is (rather obviously) a set of records (each of which consists of a number of fields) and it is normally derived from a database (either by reading a table or running a query). However, that's not always the case - in some programming languages a recordset can be created and maintained without a database even being present. VBScript is just such a language.

If a programmer uses VBScript to work with a recordset then they can:

create a recordset without the necessity of a database
add new records to the recordset
store the recordset in a file rather than in a database

However, some additional software is required before a recordset can be used - the Microsoft Data Access Components.

Installing Microsoft Data Access Components

Installing the Microsoft Data Access Components (MDAC) is very simple:

downloaded the MDAC installer from the Microsoft Download Center (the current version is MDAC 2.8)
run the installer
reboot the pc

And then any VBScript applications will be able to use recordsets.

Creating a VBScript Recordset

When a programmer creates a recordset they will use one or more values from a set of constants - these constants are built into languages such as VB (Visual Basic) but not VBScript. Therefore the VBScript developer will need to define the constants themselves:

option explicit 'Enforce variable declaration

const adUseClient = 3

const adInteger = 3

const adChar = 129

const adDBTimeStamp = 135

The programmer will also need to define where are data is to be stored:

dim data_dir : data_dir = "c:\vbscript\data"

dim person_file : person_file = data_dir & "\person.dat"

Next the recordset (which is an ActiveX Data Object) can be defined:

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

The VBScript now needs to ensure that the the folder for the recordset file exists:

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

if not fso.folderexists(data_dir) then

mkdir data_dir

end if

With the folder in place the recordset file can be created if necessary - using the append method to define new fields:

if not fso.fileexists(person_file) then

person.cursorLocation = adUseClient 'Uses a client-side cursor

person.Fields.Append "Name", adChar, 25 'String with 25 characters

person.Fields.Append "Age", adInteger

person.Fields.Append "Updated", adDBTimeStamp

person.open

person.save person_file

person.close

end if

The above process of

creating the fields
opening the recordset
saving the recordset file
closing the recordset

will only have to be carried out once - after that the file can be opened straightaway:

person.Open person_file

And finally the FSO can be disposed of (to save memory):

set fso = nothing

This code will be useful in a number of different applications and so if the code is saved in a file (for example H:\vbscript\database.vbs) then it can be used as a library to be used elsewhere.

Using the Recordset in a VBScript Application

The recordset library code can be called from any VBScript application (for example load_recordset.vbs):

option explicit 'Enforce variable declaration

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

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

set fso = nothing

The recordset functionality can then be used as required and typical activities are to:

query the recordset to see how many records there are
add a new record
save the recordset

For example:

msgbox person.recordcount

person.addnew

person("Name") = "Fred"

person("Age") = 21

person("Updated") = now

msgbox person.recordcount

person.save person_file

And it is very easily to loop through the whole recordset to examine its contents:

person.movefirst

while not person.eof

msgbox person("Updated")

person.movenext

wend

Finally the script should close the recordset and reclaim any memory used:

person.close

set person = nothing

And so with just a few lines of VBScript code the programmer is a able to maintain a set of data - without the need for a database.

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

A VBScript Recordset Application

The Recordset Library

The Recordset Application

Print Article

View All Articles

How to subscribe to feeds