Working with Records and Fields in VBScript
How to Use VBScript to Process the Contents of a Recordset
Jan 7, 2009
Visual Basic is one of the most commonly used programming languages in the world of software. One reason for its success is that VBScript (the scripting element of Visual Basic) is installed on every Windows computer. Another reason is that VBScript can be used to program many Windows applications - for example Microsoft Access. With VBScript it is very easy to process one a vital element of Microsoft Access - the recordset.
A recordset is the data returned from running a SQL query on a database or by reading the contents of a table, and so the first stage is to connect to the database.
Using VBScript to Connect to a Microsoft Access Database
Making a connection to a Microsoft Access database is very easy and just requires a couple of inputs:
- the connection provider (in this case it's the Microsoft Jet engine)
- the location of the database (and in this example it is c:\sales.mdb
The connection code is, therefore:
If this code is save to a .vbs file and run (by double clicking on the file in Windows Explorer) then nothing obvious will happen - nothing interesting will happen until a recordset has been created.
Using VBScript to Create a Recordset
The processing of recordsets may be used quite a lot in an application, and so it's useful to build the functionality into a subroutine:
The recordset can then be created from the sql and the connection passed to the subroutine:
The recordset will now have been loaded into VBScript, and so the next step is to process the contents of the recordset.
Using VBScript to Process the Contents of a Recordset
There are two things that the programmer is unlikely to know about the recordset:
- how many records have been loaded into the recordset
- how many fields make up each record in the recordset
The VBScript must, therefore, be able to cope with these variables and it does this in two ways:
- loop through the recordset record by record
- loop through each record field by field
Both tasks are, of course, very easy with VBScript.
Using VBScript to Process Records in a Recordset
VBScript must be able to process the recordset record by record (or line by line), and it does this by making use of the recordsets eof (end of fields) property and the movenext method:
Here VBScript will loop through each record in the recordset, and there's an important point to take not of at this point. If the movenext method is missed out an infinite loop will be caused (because the code will not progress past the first record).
Now that the recordset can be processed record by record the next stage is to process each of the fields in the record.
Using VBScript to Process Fields in a Record
The fields in a record can be processed by using the for each statement:
These two techniques can now be combined to produce a subroutine that will be able to handle any number of records with any number of fields.
A Complete VBScript for Processing a Recordset
The final subroutine can accept any sql statement with a connection and will display the results of the query:
The subroutine can now be called from anywhere in the application, for example:
or the table name can be used:
The end result, in this example, will be for the contents of the customer table to be displayed to the user.
 |
