Web Support | Mastering Dreamweaver MX Databases

We're changing the way we do business, and to Microsoft's credit, Access 2002 offers several data-integrating features. Most likely, Access will never be the web-support database of choice, but now, even the novice can get the job done using the following features:

Using Access projects, you can access a SQL Server database.
Data access pages are a quick solution for interacting with a database via a web browser.
Access 2002's support for XML makes transferring data with non-Access systems easier than ever.
Web components display dynamic data quickly.

In the next section, we'll explore built-in features that make Access web solutions easier than they've ever been.

Access Projects—SQL Server Desktop

Access projects use the OLE DB architecture to combine Access objects and the SQL Server engine. The result is SQL Server Desktop—SQL Server's power and efficiency in Access's easy-to-use interface. All database objects—forms, reports, data access pages, macros, and modules—are code- based or HTML-based. That makes these objects extremely easy to publish to the web.

Note

OLE DB is a quick and efficient means to getting the data you want—regardless of its format—via network connections. For more information on OLE DB, see Chapter 10.

An Access project (an ADP file) is a simple container for database objects. The tables are SQL Server tables and are stored on SQL Server. Although these objects all look familiar to their Access counterparts, they offer more options. To illustrate some of these differences, Table 9.2 compares Access 2002 table properties to those in a project, and Table 9.3 compares Jet and Transact-SQL (T-SQL) datatypes—ADP files use T-SQL, not Jet.

Table 9.2: COMPARISON OF ACCESS 2002 AND PROJECT Table PROPERTIES
Access Project	Access 2002	Description
Column Name	Field Name	The field or column name.
Datatype and Field Size	Datatype	Specifies the column's datatype. Projects combine this property with Field Size for numeric datatypes.
Description	Description	Text that describes a column's purpose.
Length	Field Size	In Access, this property determines the size of a numeric or text datatype. Determines the size of a text or character field in a project.
Allow Nulls	Required	In Access, set the property to No to allow null values. When selected in a project, this property allows null values.Default Value Default Value Enter a specific value for new records.
Scale	No equivalent property	Specifies the total number of digits for a value's decimal portion.
Precision	No equivalent property	Determines the total number of digits in a column.
Identity	No equivalent property; use AutoNumber datatype Identity	Returns a unique value for each record.
Identity Seed	No equivalent property at table level	Sets the first value for an Identity column.
Identity Increment	No equivalent property at table level	Determines the incremental value between each Identity value in a column.
Is RowGridRowGuid	No equivalent property, use AutoNumber datatype	Specifies that the row contain a GUID (globally unique identifier).
Formula	No equivalent property	Supplies an expression for a computed column.
Collation	No equivalent property	Sets the sorting order for a character column.
Format	Format	Sets column's display attributes.
DecimalPlaces	Decimal Places	Number of digits that follow the decimal point (from 0 to 6). The result affects display only.
Input Mask	Input Mask	Enters code that displays formatting during input.

Table 9.3: JET AND SQL SERVER (ACCESS PROJECT) DATATYPES
Access Project	Jet	Description
text	Text	Stores any characters; limited to 2.14GB.
tinyint	Number, Byte	An integer value between 0 and 255.
smallint	Number, Integer	A short integer between -2¹⁵ and 2¹⁵-1.
int	Number, Long	A long integer between -2³¹ and 2³¹-1.
real	Number, Single	A single-precision floating-point value between 3.40E+38 and 3.40E+38.
float	Number, Double	A double-precision floating-point value between -1.79E+308 and 1.79E+308.
decimal	Number, Decimal	An exact numeric datatype that stores values between -10^–28 and 10^–28-1.
counter, autoincrement	AutoNumber	A long integer between -2,147,483,648 and 2,147,483,647.
datetime	Date/Time	A valid time or date. Jet year values must be from 100 through 9999. Project year values are 1753 through 9999.
bit	Yes/No	integers 0 and 1only (yes/no;ture/false)
momey	Currency	A scaled integer between -2⁶³ and 2⁶³-1.
image	OLE Object	Picture or object; limited to 2.14GB.
None	Hyperlink	An e-mail or website address.

Access projects are a great bonus in the following situations:

Limited financial resources SQL Server Desktop is free with any version of Access.

Growing businesses Projects are more stable and faster than the Jet database equivalent (MDB files)

Training needs Becoming familiar with SQL Server Desktop projects may be the easiest way to learn SQL Server.

Licensing You can avoid SQL Server licensing issues by installing a SQL Server runtime solution via a project.

Development environment Projects provide a convenient testing and development platform for SQL Server.

The down side is that projects are limited to five concurrent users because Microsoft deliberately limits the number of processes. In addition, a project is limited to just 2GB of data (minus the system tables). This limitation is the same as an Access database. You get stability and new features—especially of interest to the web developer are the forms and reports, which are all HTML-based objects—but you're limited in the number of users and file size.

Concurrent Users Versus Concurrent Connections

Despite what you may have heard, you can use Access to support a data-driven website. In fact, Microsoft insists that Access can handle 255 concurrent connections. You can find more information on this assertion at http://msdn.microsoft.com/library/ backgrnd/html/acmsdeop.htm.On the flip side, many developers will warn you to steer clear of Access altogether. The real trick to using Access successfully in a multiuser environment is to understand the difference between concurrent user and concurrent connection.A concurrent connection is a processed request—an action. A concurrent user is simply a user that's connected to the database. The concurrent user may consume none or several connections. An application that opens connections only as needed and then closes those connections as soon as the request is completed can consume just a few concurrent connections although those connections can support many concurrent users.

Here's our recommendation: if your website incurs a limited number of hits and the database is small, use an Access project instead of an Access MDB file. Your business may very well outgrow Access. When that happens, your Access project application will be much easier to upsize to SQL Server than an Access MDB application.

Data Access Pages (DAP)

Suppose you've got an Access application that runs well and efficiently. Then, you're asked to produce a prototype of a web application based on the existing application. Before you pull out the want ads and start job hunting, take a look at Data Access Pages (DAP). This relatively new technology saves an ordinary Access object as a DHTML (Dynamic HTML) file. The result is a browser-ready file that's ready to interact with your database. We can say a number of good things about DAP:

You can save an existing object as a DAP, or you can build one from scratch. Either way, the Access Page Designer generates all the script for you.
The client PC doesn't need Access to view a DAP.
DAP can interact with a SQL Server database.
You don't need Office 2002 Developer to distribute a runtime version of a DAP.

Unfortunately, the technology is demanding in other ways, which makes a DAP-based web solution a poor choice except for prototyping and for intranets:

DAPs aren't cross-browser compatible. That means the viewer must be using Internet Explorer 5 or later.
DAPs run only on Microsoft server technology—IIS 4.0 or later, to be exact.
The script generated and used to display the page and interact with the linked database is difficult to modify—we don't recommend you even try unless you're knowledgeable in the area. Frankly, if you have that much knowledge, you're probably not going to rely on DAP technology.
DAPs don't support subforms or subreports, although you can re-create the relationship using the Relationships Wizard.
DAPs based on an existing form or report don't support the original object's event procedures. You must rewrite these procedures using the Microsoft Script Editor.

Here's our recommendation: a successful website must cater to as many technologies as possible, and DAPs don't cater to anything except Microsoft. That isn't necessarily bad. To the contrary, a tool that allows a novice to quickly build an interactive website is impressive. However, that's exactly what Dreamweaver MX does—without the restrictions imposed by a DAP. Don't even try to interact with DAP using Dreamweaver MX—create your pages from scratch using Dreamweaver MX and forget DAP.

Office Web Components (OWC)

Office Web Components (OWC) are ActiveX controls, and they're new to Access 2002. You'll use these controls—PivotTables and PivotCharts—to display data in an Access form. In addition, you can save a web component view as a DAP, which means you can also view these controls via a web browser. Unfortunately, web components are as restrictive as DAP technology. All the limitations we mentioned earlier for DAP also apply to web components—but it gets worse:

The client PC must have a license for Microsoft Office 2002 to interact with a web component.
The client with OWC installed but no Office 2002 license can view but not interact with the data.
The client with neither OWC nor an Office 2002 license sees nothing.

Here's our recommendation: don't try to use OWC controls in Dreamweaver MX. There's no reason to—Dreamweaver MX controls are far superior to OWC.

Extensible Markup Language (XML)

Access 2002 supports Extensible Markup Language (XML), a platform-independent markup language.

You can use XML to publish interactive data on the web. More to the point, XML's main purpose is to ease the task of generating and reading data. Although XML is a web technology, it's quickly becoming the data-sharing technology standard—especially for systems that aren't connected and can't communicate with one another.

For the most part, you'll rely on SQL and data-specific features to retrieve, insert, delete, and modify data via a web browser when you're dealing with a live connection. However, you can increase performance by limiting those active connections when possible. When working with data that rarely changes, you can save data to XML format and then display that data via an associated Extensible Stylesheet Language (XSL) file. (An XSL file serves as a template for transforming data from one format to another.)

These XML data documents can be run from the server or the client and viewed on almost any browser that supports HTML 4. As needed, your web application can retrieve the XML document instead of hitting the server to process another data request. The resulting page is almost immediate.

Not only does XML share data—Access 2002 also imports and exports schema. Consequently, you can share an application's structure as well as the data it stores. You can create an entire relational database by importing an Extensible Schema Document. Developers can create entire relational databases or parts of databases simply by importing an XSD schema from the web.

Here's our recommendation: more than likely, you won't encounter XML files when using Dream- weaver MX since XML is a tool for transferring and sharing data, not accessing live data. We've included this short section because Access 2002 supports the technology.