Web access to EnterpriseDB with Data Access Pages

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.

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.


By Jayaram Krishnaswamy   Popularity  (4443 Views)