How to Use VBScript to Create Recordsets

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

What Will I Learn?

Building a Recordset Without a Database

Requirements

Visual Studio Code https://code.visualstudio.com

Difficulty

• Intermediate

Tutorial Contents

• Installing Microsoft Data Access Components
• Creating a VBScript Recordset
• Using the Recordset in a VBScript Application

How to Use VBScript to Create Recordsets

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 Data Access Components

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

• downloaded the MDAC installer from the Microsoft Download Center
• run the installer
• reboot the pc
  And then any VBScript app
  lications 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.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---