Lesson 7

Using Microsoft Access with the Web Conclusion

The Web has made the world a lot smaller. With the new features in Access 2016 for the Web, Access has become a lot more powerful. This module has discussed some of the features that can make life easier, as well as help you get on the Web that much more quickly.
In this module, you learned how to:

Create hyperlinks on forms in order to open other forms, applications, or access the Web
Examine what Data Access Pages are and how they are used with Access reports and forms
Learn how Data Access Pages can be used
Use the Page Wizard to create a data entry style page
Use the Page Wizard to create a grouped page

Take some time to work with the features described and see whetherz they can meet some of the needs you have.

An Example: Using ADO over the Web

A simple real-world illustration of the use of ADO. Many web sites expose data from an underlying database. Now, it is quite easy to save an Access table in the form of an HTML page, using Export feature of Acces. However, the resulting data is static. To generate dynamic data in response to a user's input, we need to do some programming.
One of my duties is to maintain a web site called The Mathematics Online Bookshelf (http://www.mathbookshelf.com). This site is essentially a frontend for a searchable Jet database of several thousand high-level mathematics books. The user can fill in a search form and click a Search button. All matching records will be returned to the user over the Web. Let us look at a simplified version of the ADO code used to search the database. (Incidentally, the context of this code is an Active Server Pages (ASP) file, and the scripting language is VBScript. However, you do not need to know anything about these technologies.) Figure 3-7 shows a greatly simplified version of the search form. This version allows user input of author, title, and publisher, and the principle is the same for more complicated forms.

We begin by noting that in VBScript, the Like operator uses a percent sign (%) to 1represent any string and an underscore ( _ ) to denote any single character. (This is the syntax of regular expressions.) First, we declare some variables. Since this code is written as VBScript, variables are declared without a type. Note that we include variables that correspond to the values of each search-form control.

' Declare variables
Dim cn, rs, sSQL
Dim author, authorexact, title, titleexact, publisher
Dim connective
Dim cMatches

Then we assign the variables to the control's values, as returned by the ASP Request object.

' Gather input from search form
author = Request("txtAuthor")
authorexact = Request("optAuthor")
title = Request("txtTitle")
titleexact = Request("optTitle")
publisher = Request("lstPublishers")

Now we open an ADO connection to the database, which is called MobBooks, and declare a recordset variable for later use.

' Open a connection to MobBooks database
Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
cn.Provider = "Microsoft Jet 3.51 OLE DB Provider"
cn.ConnectionString = "Data Source=" & Server.MapPath("/MobBooks.mdb")
&  ";Jet OLEDB:
Database Password="password"
cn.Open

Now we can build an SQL statement based on the contents of the search form, as contained in the variables.
We begin by creating a JOIN between the MobBooks and the MobPubs tables. The reason is that the PUB field in the MobBooks table contains abbreviations for the publisher names, but we want to display the full publisher names, which are in the MobPubs table.

' Build SQL statement
' Start with a join between MobBooks and
' Publishers to pick up Long name of publisher
sSQL = "SELECT MobBooks.*, MobPubs.[LONG NAME] AS Publisher"
sSQL = sSQL &  " FROM MobBooks INNER JOIN MobPubs ON MobBooks.PUB =
MobPubs.PUBLISHER"
connective = " WHERE "
' Publisher
If publisher <> "-All Publishers-" Then
sSQL = sSQL &amp;  connective & "([Long Name] = '" & publisher & "')"
connective = " AND "
End If

' Author
if author <> "" then
if authorexact = "exact" then
sSQL = sSQL & connective & "(AU='" & author & "')"
else
sSQL = sSQL & connective & "(AU Like '%" & author & "%')"
End If
connective = " AND "
End If

' Title
if title <> "" then
if titleexact = "exact" then
sSQL = sSQL & connective & "(Title='" & title & "')"
else
sSQL = sSQL & connective & "(Title Like '%" & title & "%')"
End If
connective = " AND "
End If

Next we open the recordset:

' Open recordset
rs.Open sSQL, cn

Now we can write the search results to HTML output, using the Write method of the ASP Response object Microsoft Access Reference

Throughout this course you have read about the many features that Microsoft has added to Access over the years. As exciting and interesting as these new capabilities are, they pale in comparison to the ability to upsize Access applications to Windows SharePoint Server. Each recent version of Access has demonstrated greater and greater ability to integrate with SharePoint. The most exciting aspect of this integration is the ability to actually run your Access application as a SharePoint website. In this module and the next, we will explore the various techniques that allow you to upsize your Access databases to SharePoint.

Data Access Pages - Quiz

Click the Quiz link below to answer a few questions about working with Data Access Pages.
Data Access Pages - Quiz