Microsoft Access VBA Programming for the Absolute Beginner

2017-07-07 02:10:07

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> <%  %> <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.

Категории