VBScript | Microsoft Access VBA Programming for the Absolute Beginner

VBScript (or Visual Basic Scripting Edition) is a “lightweight” scripting language from which some of the features of VBA (like file access or access to the Win32 API) have been removed. (Many of these features are still available, though, through additional components, such as Microsoft Scripting Runtime, that are included with VBScript.) VBScript has four major applications:

The programming language for Outlook forms
A programming language for Windows Script Host, a collection of components for Windows system administrators and power users
A client-side scripting language for Microsoft Internet Explorer
A server-side scripting language for Active Server Pages, Microsoft’s object model to extend Internet Information Server and create dynamic web pages

If your system has access to Internet Information Server, you can create web pages that display data from your Access databases by using VBScript and ADO. The following code, for instance, when saved as an .asp file in a virtual folder, displays the contents of the Customer table, as Figure 21-14 shows.

<% Option Explicit %> <HTML> <HEAD> <TITLE>The Corner Bookstore Customer Table</TITLE> <% <!-- #INCLUDE VIRTUAL="C:\Program Files\Common Files\System\ado\adovbs.inc" --> %> <SCRIPT LANGUAGE="VBSCRIPT" RUNAT="SERVER"> Function GetRecordset()   On Error Resume Next   Dim con, rs     Set con = CreateObject("ADODB.Connection")   Set rs = CreateObject("ADODB.Recordset")   con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\BegVBA\thecornerbookstore.mdb;"   rs.CursorType = adOpenForwardOnly   rs.CursorLocation = adUserClient   rs.Open "SELECT * FROM tblCustomer", con   Set GetRecordset = rs End Function </SCRIPT> </HEAD> <BODY> <H1><CENTER>The Corner Bookstore</CENTER></H1> <H2>Customer List</H2> <TABLE> <%   Dim rs, fld     Set rs = GetRecordset()   ' Iterate fields for field names   Dim strField    Dim iPos   Response.Write "<TR>"   For Each fld in rs.Fields   strField = fld.Name   ' Remove "txt" from field name   iPos = Instr(strField, "txt")   If iPos = 1 Then strField = Mid(strField, 4)   ' Spell out "customer" in field name   iPos = Instr(strField, "Cust")   If iPos = 1 Then strField = "Customer " & Mid(strField, 5)   Response.Write "<TH>" & strField & "</TH>"   Next   Response.Write "</TR>"     ' Position pointer to beginning of file   rs.MoveFirst     ' Display records   Do While Not rs.EOF   Response.Write "<TR>"   For Each fld in rs.Fields   Response.Write "<TD>" & fld.Value & "</TD>"   Next   Response.Write "</TR>"   rs.MoveNext   Loop  %> </TABLE> </BODY> </HTML>

click to expand
Figure 21-14: Output from an Active Server Page built with VBScript

A good deal of the code consists of HTML statements, either directly or through code inserted into the output stream by the ASP Response.Write statement. The remainder of the page consists of VBScript and ADO code. Much of it is more or less identical to the code used to display the Customer table in Excel earlier in this chapter. The remaining code simply iterates the recordset’s fields to display their names and then iterates the recordset itself to display the values of each field in each record. Once again, this is a more or less straightforward extension of VBA code with which you’re already familiar.

Although you may be unfamiliar with many of the details needed to create ASP pages—you may not know how to configure IIS, what the ASP object model is like, or what syntax is required in ASP pages—nevertheless, given your knowledge of VBA and ADO, you have a firm basis to begin developing web applications with ASP if you choose to do so.