VBScript

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>

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.