Web enabling backend data is a dire necessity for businesses to make an impact. In
these days and times there are no businesses without support for web access to
their backend data. Microsoft provided this support (in versions 2000 to 2003)
in Microsoft Access with Data Access Pages, a dynamic HTML page that could display
and modify business data. Data Access Pages are not supported (creating DAP)
in Office 2007 [http://office.microsoft.com/en-us/access/HA100308321033.aspx]
although one can still look up DACs created with the earlier versions. With the
Microsoft Windows Share Point services taking off, a more popular alternative
for DAC emerged. However, there are many businesses still using various versions
of MS Access, and for them data access pages is still relevant.
Data Access Pages [DAP] which had its debut in Access 2000 is a client side technology
and depends on DHTML. The DHTML binding to data accessible by ADO makes data
access pages possible. The DHTML page developed in MS Access hosted on the Intranet
web server makes this dynamic user interaction possible. In Microsoft Access
2007 this interaction with the intranet web server was taken away.
EnterpriseDB, another component of this article enhances Postgres to provide enterprise
capabilities to Postgres users. EnterpriseDB is available for Windows platform
as well. It has most of the necessary features of an enterprise class database
such as advanced development, monitoring, migration and administrative tools
with a stable environment. It also has plug-in capabilities for Oracle. It can
easily integrate with most applications such as Java, Ajax, Ruby, and Drupal
etc.
In this article I will show you the steps you need to take to create data access
pages that can display data on an EnterpriseDB database. In order to work with
this you need to download and install EnterpriseDB which is actually a PostgresPlus
server. The detailed procedure to install may be found in
this article. There are several other related articles in my blog which the reader may find useful. It is also assumed that you have MS
Access which can create DACs is available (although MS Access 2003 was used for
this article on Windows XP with SP2 and IE 7.0).
We begin with creating an ODBC DSN to access the database and create a linked server
to link to the table. We will use the linked table to generate the web page which
can display grouped data.
Table in EnterpriseDB displayed using Data Access Pages
In this article we will be web enabling data from the table "Orders" on
the EnterpriseDB shown in the following figure. The installation of the product
as well as the use of the Postgres Studio interface is fully described in the
articles whose links were provided earlier. The next figure shows the objects
on the EnterpriseDB server in the Postgres Studio.
The details of the table which will be displayed in a web page using data access
pages are shown in the next figure. This is a table from the Northwind database
that was migrated to Postgres.
Creating the ODBC DSN
This step is necessary to get data from the EnterpriseDB. Click Start | Control Panel
| Administrative Tools | Data Sources (ODBC). This opens the ODBC Data Source
Administrator window as shown. The default tab is User DSN.
Click the Add... button to open the Create New Data Source window as shown. Scroll
down till you see EnterpriseDB 8.3R2. This is the driver that gets installed
when you install EnterpriseDB.
Click Finish to display the EnterpriseDB ODBC Driver configuration window. Provide
a name for the Data Source (herein EDBDataAccess). The database which contains
the table is PGNorthwind. The other details will have been filled for you if
EnterpriseDB is correctly installed. Of course you need to fill in the User Name
and Password fields. You may test the connectivity by clicking on the Test button.
You may also want to look at the Data Source options using the Datasource button
which would reveal a tabbed page with two tabs, Page1 and Page 2. With this you
will be able to fine tune the options. Herein the defaults are accepted and no
changes were made. Click the save button. The ODBC DSN gets saved, the ODBC Data
Source Administrator gets shown displaying EDBDataAccess. Click OK.
Creating a Linked Server in MS Access
Create a blank Microsoft Access Database, herein named DapEdb. There are two options
to bring in data from an external source in MS Access. Either import the data
or establish a link to the data. Herein the Link option is used. From File |
Get External Data | Link tables...you can establish a link to tables on the EnterpriseDB.
This opens the Link window where you can browse to establish a link. The default
file type is set for Microsoft Office Access (*.mdb, *.mda, *.mde) and this should
be changed to ODBC Databases () as shown in the next figure.
When the ODBC Databases () is selected the Select Data Source tabbed window gets
displayed. Change to Machine Data Source tab.
Highlight the EDBDataAccess and Click OK. This displays the Link Tables window which displays all tables, both user as well
as system tables.
Highlight public.Orders and click OK. You have the option to save password. When
you click OK, the Select Unique Record Identifier window gets displayed. In order
to update records (although in the grouped data access page we will be just displaying
the results) you would need a unique reference. The window comes up with a default
identifier (OrderID) which you may change if you choose to do so.
Click OK on the above window. The public_Orders table gets displayed as a linked
object in the DapEdb database as shown. A few records from the table are also
shown in this superposed image (showing few of the many columns).
Creating a data access page
In MS Access there are two options to create a data access page, in design view or
using the wizard. It is rather easy to create a data access page using the wizard as the steps are
well laid out. Even grouping of data is easy to implement. Here we will create
a data access page in the design view.
Select "Pages" under objects in the main window, click on "Create
data access page in Design view". This opens a "Microsoft Office Access"
warning regarding incompatibility between 2003 and 2000 which can be overcome
by installing Microsoft Office XP Web components. Click OK. This brings up the design view of Page1 as shown.
At the same time a Field List window will be also added as shown. You can drag and
drop elements from the field list on to the design area. In this list all the
columns in the table are listed.
Highlight all the columns [use shift key] from 'OrderDate' to 'ShipCountry' in the
field list and drag the highlighted items to the design area and drop them. You
would see a blue bordered rectangular area into which you should release the
mouse key. The border goes way and a "Layout Wizard" window gets displayed
as shown.
Keep the default "Columnar" layout and click OK. The dragged fields will
be displayed in the drop area as shown.
If you were to save the page and browse to it you would be seeing a HTML page with
the data from the database and, you can cycle through the data using the navigation
keys.
Grouping the Data
Highlight EmployeeID in the field list and drag it over to the design area and drop
it just above "Header: public_orders" as shown.
After dropping the “EmployeeID", you can see that the design changes over to
the design shown in the next figure. Observe the controls for grouping outside
of the detailed section for “Order".
Change the title of the page to reflect the data by over writing , and click View
| Page View to display the page as shown.
It is grouped by the employees but this is not the way you would like to see. Save
the page. Herein, the page is saved as DapEdb.htm. You may get some messages
as to the link the page refers to, and the database being not exclusively locked.
Click OK to them. The page will not be saved to the database but it can be edited
and modified.
Open DapEdp.htm page (the data access page) from its saved location. Access the group
level properties access menu as shown in the next figure.
Click open the properties for "GroupLevel: public_Orders-EmployeeID” and therein
change the DataPageSize property to 1(default is 10) and ExpandedByDefault to
true (default is false). Similarly set the DataPageSize property for the GroupLevel:
public_Orders to 1.
This way you can view the data as shown in the next figure and navigate through the
data.
You can make further changes to the format of the page as well as use scripting to
work other objects and events raised by the objects on the page.
Data access pages can present some challenges and these are mainly related to the
changes MS Access has undergone as well as the changes in the internet explorer;
the MDAC stack and the platform OS (Windows XP, Windows 98 etc). There are issues
with IE8 and you may see empty data access pages displaying in IE8 and Windows
XP with Sp3. There is a lot of discussion on this on the internet. You may benefit
by reviewing all my articles on Data Access Pages here.
Summary
The article describes in detail web enabling data on a Postgres database using Data
Access Pages. A linked table to Enterprise DB’s Postgres database provides the
data for the data access page. The grouped data access page is created using
the design option.