Full HTML for

Basic foilset Overview of Microsoft Access used with Active Server Pages(ASP)

Given by Nancy McCracken and Sangetta Aggarwal at CPS616 Technologies of Information Age on Spring 1998. Foils prepared 4 Feb 98
Outside Index Summary of Material

This describes Microsoft's extended HTML web based language ASP (Active Server Pages) for use of Microsoft Access database system
see Access used with JDBC

Table of Contents for full HTML of Overview of Microsoft Access used with Active Server Pages(ASP)

 Denote Foils where Image Critical
Denote Foils where HTML is sufficient
1 MicroSoft Access with ASP http://www.npac.syr.edu/users/gcf/accessasp97
2 Microsoft Access
3 MS Access - Features (cont'd)
4 Cool Features of Access - I
5 Cool Features of Access - II
6 The Access Event Model
7 VBA class modules
8 Database Design - I
9 Database Design - II
10 Database Design - III
11 Access SQL - I
12 Access SQL - II
13 Access SQL - III
14 Access SQL - IV
15 Access Controls
16 Access Forms and Reports
17 Developing Multiuser Applications
18 Security in Access
19 Client-Server Applications
20 Web-Enabled Applications -I
21 Web-Enabled Applications -II
22 Web-Enabled Applications -III
23 Sample code for ASP files - I
24 Sample code for ASP files - II
25 Access as an Automation Client-I
26 Access as an Automation Client-II
27 Access as an Automation Client-III

Outside Index Summary of Material

HTML version of Basic Foils prepared 4 Feb 98

Foil 1 MicroSoft Access with ASP http://www.npac.syr.edu/users/gcf/accessasp97

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
8/14/97
written by Sangeeta Agarwal,
Nancy McCracken
NPAC
HTML version of Basic Foils prepared 4 Feb 98

Foil 2 Microsoft Access

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Features
Microsoft ACCESS is a desktop relational database interactive management system developed for Microsoft Windows.
The databases of Microsoft ACCESS can be managed via
  • the ACCESS interactive system
  • or via a library of executable functions callable from any PC programming language, like Visual Basic, Visual C++, etc.
  • or via the Active Server Pages (ASP), an extended HTML web page language
HTML version of Basic Foils prepared 4 Feb 98

Foil 3 MS Access - Features (cont'd)

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
ACCESS is particularly useful because of its completeness and its user friendliness of its editing interface, the tabular visualization of the data (table browser) and for the ease of query definitions and graphic output.
Access 97 integrates data from spreadsheets and other databases and is the easy way to find answers, share information over intranets and the Internet, and build faster business solutions.
The interface usability of ACCESS offers the possibility to consult and edit the data and to execute the queries in an immediate and intuitive way. Defining forms allows the development of the GUI interfaces for the database.
HTML version of Basic Foils prepared 4 Feb 98

Foil 4 Cool Features of Access - I

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Form and Report Wizards help create Access forms and reports from database information with little user intervention
Table Analyzer Wizard analyzes the structure of flat-file data, and undertakes the normalization process for users
Simple Query Wizard helps users find exactly the information they seek
Database Wizard allows users without any database experience to create one of more than 20 fully functional database applications
HTML version of Basic Foils prepared 4 Feb 98

Foil 5 Cool Features of Access - II

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Performer Analyzer Wizard analyzes the structure of a database and suggests areas for improvement
OLE Automation allows users to share information across applications.
Support for ActiveX Controls - ActiveX controls can be embedded directly in Office files, allowing developers to create fully programmable documents and forms with enhanced capabilities for online viewing
HTML version of Basic Foils prepared 4 Feb 98

Foil 6 The Access Event Model

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
An 'object' is what can be seen in the database window --tables, queries, forms, fields, indexes, controls etc. To create an Access application, objects have to be created and manipulated in response to events which are simply change in state occurring for example, when the mouse is moved, a button is clicked etc..
Event Handlers in Access are Macro, Event procedure, Global module function and Form module function. All except the Macro use the VBA (Visual Basic for Applications) language. 3 type of events can occur for a form:
  • Form events
  • Section Events
  • Control Events
HTML version of Basic Foils prepared 4 Feb 98

Foil 7 VBA class modules

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The tool provided for Visual Basic provides ability to create and manipulate one's classes of objects and encapsulating data and processes within an object. Class modules also allow the ability to abstract complex processes.
By using object-oriented techniques, one can create classes, their objects, properties, define methods and hence make development simple.
Class modules have 2 events -- Initialize (creation of a class) and Terminate (destruction of the class). Class hierarchies can be built depending on relationships.
HTML version of Basic Foils prepared 4 Feb 98

Foil 8 Database Design - I

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The relational database model was conceived in 1969 by E.F.Codd.
The model is based on mathematical theory viz. a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables.
Benefits of a relational database:
  • Data entry, updates and deletions are efficient.
  • Data retrieval, summarization, and reporting are efficient
  • Database behaves predictably.
  • Changes to the database schema are easy to make.
HTML version of Basic Foils prepared 4 Feb 98

Foil 9 Database Design - II

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Each table in a relational database is made unique by designating a 'primary key' - a column or set of columns that have unique values. A table can have only one primary key. A 'foreign key' is a column in one table that references the primary key of another table.
There are 3 types of relationships amongst two tables:
  • One-to-One - For each row in the first table, there is atmost one row in the second table.
  • One-to-Many - For each row in the first table, there can be zero, one, or many rows in the second table, but for each row in the second table, there is exactly one row in the first table.
HTML version of Basic Foils prepared 4 Feb 98

Foil 10 Database Design - III

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Many-to-Many - For each row in the first table, there can be many rows in the second table, and for each row in the second table, there can be many rows in the first table.
The relational model specifies two Integrity rules:
  • Entity integrity rule - Primary keys cannot contain null (missing) data.
  • Referential integrity rule - The database must not contain any unmatched foreign key values.
HTML version of Basic Foils prepared 4 Feb 98

Foil 11 Access SQL - I

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The textual query definition is done using SQL (Structured Query Language), a non-procedural data access language.
Access SQL support a subset of SQL-89 and some elements of the newer SQL-92 standard.
Most of Access SQL directly maps to Access QBE (Query by Example) in both directions. Queries can be constructed using QBE and viewed in SQL by switching to SQL view. Queries can also be directly entered using SQL view.
SQL Statements:
  • The SELECT Statement: The most basic and main statement of SQL. It queries select rows of data and returns them as a dynaset recordset.
  • Syntax: SELECT column-list
  • FROM table-list
  • [WHERE where-clause]
  • [ORDER BY order-by-clause];
HTML version of Basic Foils prepared 4 Feb 98

Foil 12 Access SQL - II

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Syntax of SELECT clause:
SELECT {* | expression1 [AS alias1][, expression2 [AS alias2][,..]]]}
Example: SELECT *
SELECT LastName
SELECT [Customer#], LastName, FirstName
Syntax of FROM clause:
FROM table-or-query [AS alias]
Example: SELECT * from tblOrder;
SELECT OrderId, OrderDate FROM tblOrder as [Orders Table];
Syntax of WHERE clause:
WHERE expression1 [{And | Or} expression2[..]]
Example: SELECT OrderId FROM tblOrder WHERE OderTakerId = 2;
SELECT OrderId, OrderDate FROM tblOrder WHERE Sex="Female" AND Age BETWEEN 21 AND 29;
Syntax of ORDER BY clause:
ORDER BY column1 [{ASC | DESC}][,column2[{ASC | DESC}][,..]]
Example: SELECT * FROM tblCustomer ORDER BY LastName, FirstName;
HTML version of Basic Foils prepared 4 Feb 98

Foil 13 Access SQL - III

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The UPDATE Statement: Used to change values in one or more columns in a table.
Syntax: UPDATE table-or-query
SET column1=expression1[,column2=expression2][,..]
[WHERE criteria];
Example: UPDATE tblMenu SET tblMenu.Price = {Price]*1.1 WHERE MenuDescription Not Like "*Pizza";
The DELETE Statement: Used to delete rows from tables.
  • Syntax: DELETE [table.*]
FROM from-clause
[WHERE criteria];
Example: DELETE FROM tblMenu WHERE Discontinued = True;
The INSERT INTO Statement: Used to copy rows from one table into another or to add a single row of data to a table using a list of values.
Syntax: INSERT INTO target-table
select-statement;
Example: INSERT INTO tblCustomer SELECT * FROM tblCustomerNew;
HTML version of Basic Foils prepared 4 Feb 98

Foil 14 Access SQL - IV

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The SELECT INTO Statement: It is unique to Access SQL, Used to create a new table from the rows in another table or query.
Syntax: SELECT column1 [,column2[,..]] INTO new-table
Example: SELECT OrderId, OderDate INTO tblJonesOrders FROM tblOrder WHERE CustomerId=9;
Access SQL supports 4 DDL (Data Definition Language) statements:
  • CREATE TABLE - Creates a new table schema
  • ALTER TABLE - Modifies an existing table schema
  • CREATE INDEX - Creates a new index
  • DROP - Deletes a table schema or an index
Data Access Objects (DAO) is another method, like DDL, to programmatically create and manipulate table schemas in Access.
HTML version of Basic Foils prepared 4 Feb 98

Foil 15 Access Controls

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Controls are the workhorses of Access. They are used for inputting and outputting data, and for displaying static information. They can be used as global variables, to calculate intermediate values. Forms and reports share the same controls.
Some controls have values, given by user at design time, by the data from which the control is being fed, or by the user at run time. Eg. for a text box, the value is the text inside the box; for a list box, the control value is the one chosen.
Controls have properties which can be set or changed by the application. Access provides the Tag property which allows the user to specify and store upto 2048 characters of information attached to any control.
HTML version of Basic Foils prepared 4 Feb 98

Foil 16 Access Forms and Reports

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Beyond the tabular vision, for consultation and editing of data, there is a form editor and a form interpreter that allows the generation and management of specific input/output data interfaces (forms).
The usage of forms allows the visualization in a single window interface of data belonging to different tables and query results.
In Access, program code and a form (or report) is stored in a neat package. Each form and report can carry its own module with it. The event procedures are subroutines and their scope, by default, is private to the form.
Reports are information organized and formatted to fit the user's specifications. They provide a way to retrieve and present data as meaningful information. Examples are mailing labels, invoices, sale summaries, phone lists etc.
HTML version of Basic Foils prepared 4 Feb 98

Foil 17 Developing Multiuser Applications

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Main issue is handling errors that occur when locks are placed on pages of records. The following considerations are important:
  • Balance network security and maintenance against efficiency and speed when choosing to install Access on each workstation.
  • Balance ease of use, data integrity, and ease of programming in developing the locking strategy.
  • Awareness of errors that can occur when multiple users share data.
  • Use custom error-handling code when using bound forms with optimistic locking.
  • Use custom lock notification code when using bound forms with pessimistic locking.
  • Splitting database into separate data and application databases for increased performance.
  • Using VBA code to manage the linked tables in a split database structure.
HTML version of Basic Foils prepared 4 Feb 98

Foil 18 Security in Access

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
There are two overlapping security models:
  • WorkGroup-based security model - Also called user-bases security model. Security revolves around a database and is self-contained within the confines of that database. Security is based on users and their permissions. Objects can be assigned different permissions.
  • Database Password security model - This simpler model requires setting of a single password that all users must know to open the database. But it can't track individual users' activity in a shared database. But both the models can be used at the same time.
  • Encryption is another method provided by Access to secure the database from hackers. But it reduces database performance by 10-15%. Also database is made uncompressible by some programs. Also, the Access Security Wizard helps to secure the databases.
HTML version of Basic Foils prepared 4 Feb 98

Foil 19 Client-Server Applications

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Client-Server database applications shift the burden of data storage and manipulation to a dedicated application. In such applications Access provides the interface to the server data.
Some methods for retrieving data from a server are:
  • File-server - database is moved to a network server's hard drive. Inefficient due to the data moved across the network to serve queries.
  • Linked ODBC tables - User's SQL is translated to a universal SQL dialect and is passed to the ODBC manager running on the client workstation.
  • Direct connection using Data Access Objects - The OpenDatabase method is used. The ODBC connection is given information to open a server database directly.
  • SQL pass-through (SPT) queries - Used when some advanced feature of the database server is required that ODBC SQL can't understand.
  • ODBCDirect - Instead of the query being translated, it is sent directly to the database server.
HTML version of Basic Foils prepared 4 Feb 98

Foil 20 Web-Enabled Applications -I

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Access 97 makes it easy to share up-to-date information with others, regardless of where they are. With features of exporting dynamic forms to HTML and publishing them on the Web, it provides for sharing of dynamic or static database information across the Internet or intranet.
HyperLinks - Users can store hyperlinks in all MS Access databases to connect to information anywhere. Hyperlinks can be control-based i.e text box, combo box, label, command button, image control, can be bound to fields with a Hyperlink datatype.
  • Save to HTML - Users can publish static views of their data for a workgroup or on the Web.
  • HTML importing and linking - Users can import or attach their databases to HTML pages.
  • Internet replication - Users can extend the replication capabilities introduced in Access 95 over the Internet using FTP.
  • Publish to the Web Wizard - This wizard automates the publishing of database information to the Web dynamically.
HTML version of Basic Foils prepared 4 Feb 98

Foil 21 Web-Enabled Applications -II

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Data can be published in 3 formats:
  • Static HTML - Wizard output is HTML files. Supported by all browsers; simple, standard.
  • Dynamic Internet Database Connector (IDC) pages - Output is IDC and HTX (HTML extension) files. Supported by all browsers. Requires the MS Internet Information Server 3.0 (IIS) with an ODBC connection to the database, data is dynamic but read-only.
  • Dynamic Active Server Pages (ASP)- Output is ASP files. Requires Internet Explorer 3.0 browser with HTML ActiveX Layout control, IIS 3.0; data is dynamic and editable (forms only). ActiveX Server is a component of the IIS. The Access form-like pages can browse, update, insert and delete records. The ASP files are a combination of HTML and the VBScript language. VBScript is a special version of VBA adapted for Web use. The ASP files contain HTML tags interspersed with queries as SQL statements, template directives and VBScript code containing references to ActiveX Server Controls.
HTML version of Basic Foils prepared 4 Feb 98

Foil 22 Web-Enabled Applications -III

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
The ASP files also contain ODBC connection information to connect to an ODBC data source (Access database). The connection information includes the data source name, and user name and password (if user-level security is needed).
After the database has been published, the IIS, upon request from the web browser, runs the VBScript code, calls the ActiveX Server Controls, opens the Access database, runs the queries present in the ASP files to access the data, merges the results and HTML tags in the ASP file into one file and then sends it back to the browser for display as a Web page.
The adjoining figure shows the components for connecting to databases from Peer Web Services.
HTML version of Basic Foils prepared 4 Feb 98

Foil 23 Sample code for ASP files - I

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
<HTML><TITLE>Employees Directory</TITLE><BODY>
<h2> <center><font color=red>Form for Employees Directory</font></center></h2>
<%
If IsObject(Session("workpg_conn")) Then
Set conn = Session("workpg_conn") workpg is ODBC System
Else DSN for an Access db
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "workpg","",""
Set Session("workpg_conn") = conn
End If
%>
<%
If cstr(Request.QueryString("ok_ans")) <> "" Then
rs.Fields("ok_ans").Value = Request.QueryString("ok_ans")
End If
If cstr(Request.QueryString("OptBody6")) <> "" Then
rs.Fields("OptBody6").Value = Request.QueryString("OptBody6")
End If
% >
HTML version of Basic Foils prepared 4 Feb 98

Foil 24 Sample code for ASP files - II

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
<SCRIPT LANGUAGE=VBScript>
<!--
Dim rgszCtrls(24, 2)
Dim cMaxCtrls
cMaxCtrls = 24
Sub AddCtrlToList(szCtrl, szCtrlSrc)
Dim i
for i = 1 to cMaxCtrls
if rgszCtrls(i, 1) = szCtrl Then Exit Sub
if rgszCtrls(i, 1) = "" Then
rgszCtrls(i, 1) = szCtrl
rgszCtrls(i, 2) = szCtrlSrc
Exit Sub
End If
next
End Sub
Sub UpdateRefreshBtn()
nav_btn_MoveCancelUpdate.Caption = "Cancel"
End Sub
-->
</SCRIPT>
HTML version of Basic Foils prepared 4 Feb 98

Foil 25 Access as an Automation Client-I

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Automation is a technology that allows two separate applications components to communicate with each other as either data exchanges or commands issued by one component for the other to perform.
It requires a client and a server. The client uses the services of an automation server. It implements a development language used to write code to control the server. Capacity to use pre-built, robust and debugged software components in applications.
Automation Clients are Access, MS Excel, Word, PowerPoint, Visual Basic, and any application supporting VBA.
Sessions begin with the client applications creating an object, i.e. establishing a conversation with the server application and telling it which of its objects are required to be controlled.
HTML version of Basic Foils prepared 4 Feb 98

Foil 26 Access as an Automation Client-II

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Class Objects exposed by MS Office 97 applications
------------------------------------------------------------------------------------------
Server Name Class Name Description
-------------------------------------------------------------------------------------------
Access Application Pointer to an instance of MS Access.
Excel Application Pointer to an instance of MS Excel Chart Pointer to a new Chart object. Launches Excel & opens a new workbook if reqd
Sheet Pointer to new Worksheet object. Launches Excel and opens a new workbook if reqd.
Outlook Application Pointer to an instance of MS Outlook.
PowerPoint Application Pointer to an instance of MS Powerpoint.
Word Application Pointer to an instance of MS Word. Document Pointer to a new Document object. Launches Word if reqd.
HTML version of Basic Foils prepared 4 Feb 98

Foil 27 Access as an Automation Client-III

From Overview of Microsoft Access used with Active Server Pages(ASP) CPS616 Technologies of Information Age -- Spring 1998. *
Full HTML Index
Examples of the types of automation solutions that can be built are:
  • A mail-merge tool that inserts data from any form into a new Word document
  • A tool that creates and runs a PowerPoint presentation based on Access data.
  • A data analysis system that uses Excel to summarize date from Access table
Power Point
Access
Word
Automation
Web
Web
Web
Excel
Web
© Northeast Parallel Architectures Center, Syracuse University, npac@npac.syr.edu

If you have any comments about this server, send e-mail to webmaster@npac.syr.edu.

Page produced by wwwfoil on Wed Feb 4 1998