Access provides an export filter to convert tables, queries, forms and reports into web pages. These web pages are static in two senses, one that the data from Access is exactly as it was when the web page was created, it doesn't change with updates to the data, and the web pages are not linked to a database on a web server, they are passed to the user in the same form every time.
To export a table select the table required in the database control panel and choose File/Export. Select the destination, add a file name and choose 'HTML Documents' as the Save as type option.
The result of this first attempt are shown here: Table Export Example 1
In this example the Save Formatted option was checked: Table Export Example 2
The appearance of the HTML output can be changed by changing the format of the table. Choose Tools/Options/Datasheet or Format/Datasheet to set properties such as background colour, font colour, gridlines, etc. When the Save Formatted option is ticked these formatting options will be applied to the HTML file.
Exactly the same methods apply to queries and forms.
Form Export Example 1. As you can see, this produces almost identical output to the table export.
Reports can also be output to HTML and here the results are different and probably better than from tables, queries and forms. Unfortunately OLE objects such as pictures are not exported; getting pictures from Access to other formats such as a a web page or a Windows form can be problematical.
As you can see, the report export facility generates a page of output for each page in the report and provides links between the pages so the user can move from page to page. The splitting of data across pages could be a problem that may require editing in the Report designer. The data could be merged in a HTML editor.
Web pages can be edited in Access by choosing the Pages tab in the database control panel and selecting the 'Edit web page that already exists' option. This opens a fairly basic but effective web page editor which allows you to make changes to your web pages. This editor will place a lot of Microsoft formatting in a web page and it is not recommended for general web page editing.
A second option for exporting from an Access database to HTML is to create Active Server Pages (ASP). For this to work there should be a Data Source Name entry for the database and there should be a Microsoft IIS web server available for the pages to run from. Once these are established tables, queries and the recordset from forms can be exported to an active server page. This has the benefit that the page displayed to the user will be dynamic and will reflect the most recent changes to the database.
If you are serious about creating web pages that reference a database it is probably better to write ASP pages directly inside a suitable editor such as Visual Studio or FrontPage. There are notes on creating web pages this way in later sections of this tutorial.
Data Access Pages allow an up-to-date display of data from a database using DHTML (Dynamic HTML) rather than distribution of pages from a web server. Instead of ASP these pages use the Office Data Source ActiveX control, included in Office 2003, which in turn uses ActiveX Data Objects (ADO) to fetch the data from the database. To view the contents of a DAP users should have Office 2003 or the Office Web Components extension, which means that this form of output is much better suited to a company intranet than to a fully-fledged web site accessible across the internet.
To create a DAP choose Pages from the database control panel and choose the 'Create data access page by using wizard' option. This takes you through a series of steps very similar to those for creating a report. To begin with you must choose the fields you want to be on the page:
At the next stage you can add grouping information. Use the arrow buttons to remove and to add fields by which the data will be grouped. Each choice of a grouping field adds an extra navigation bar to the page so it is best not to add too many levels.
At the next stage you can choose a field on which to sort the records that will appear under the group headings:
At the end of the steps you will see a display of your DAP in either Page or Design view - use the View menu to switch between them.
In Design view you can do a number of things to improve your page.
Some basic format properties can be set from Tools/Options/Pages.
To change the colours of background or text right click an object on the page or a section and choose Fill/Back Color and Font/Fore Color from the popup menu.
To apply a theme, as supplied in MS Office and especially FrontPage, choose Format/Theme. On choosing a theme you may find that the font styles now applied mean that the labels are too big for the space available.
To reveal any text in the labels obscured by the data control click on the data part of a control (use Shift/Click to select more than one) and drag the left border to the right to make room for the labels. Now select the label part of the controls and drag that to the right to make room for the text. Finally select the data parts again and drag the right border out to make them bigger again.
You will notice that there are two navigation bars for the design we specified, an outer one for the grouping field, Artist, and an inner one for the CD details. The navigation bars can be customised by right clicking and choosing Navigation Buttons from the popup menu. The function of the various buttons can be determined from the names in the list. Depending on the skills of your users you may want to remove some of these buttons so as not to confuse them or give them options they should not have.
To give the page a title when it is viewed through a browser click in the Title Text area and enter your own title. The text will be formatted as Heading Style 1 of any theme you selected earlier.
These options, in the left-most button on the toolbar, allow you to see the Data Access Page as it will appear inside a web browser.
You must save your page from the File menu and set up a link to it to make it available. When you first choose Save As a dialog box opens inviting you to provide a name for the DAP:
It is best to enter the name of your file at this point (e.g. cds_dap).
The standard Save As dialogue box now opens and you can locate the directory where you want to place the DAP/HTML file. This will probably, though not necessarily, be in the same place as your database file. The following message box alerts you to a technical aspect of Data Access Pages, whether you choose to use an absolute path or a UNC path in your connection. For now use an absolute path.
When you access a Data Access Page you may get one or more warning messages:
In this case it may be that the path to the DAP file and the Access database is not providing enough information. To correct this try editing the hyperlink to give the full path to the file.
In a web design application enter the text you want to serve as the hyperlink and then set up the hyperlink to the page you saved earlier.
DAP Example 1 (no external access!)
The output is controlled by an expansion/contraction button in the top left corner of the display. It can get a little wearing to have to keep clicking on this for each new record so an alternative design might be preferred.
The two navigation bars and the constant clicking on the expand button on the form can be improved upon by changing the group filter (the Artist field) to drop-down list. To achieve this switch to Design view and do the following:
The wizard did not convert the combo box into a drop-down list but left it as a plain text box. To make a drop-down list for this field do the following:
Select all of the fields and choose Format/Vertical Spacing/Increase to move the field controls apart.
The hyperlink and email fields do not work as they should so we need to update them. Choose the Hyperlink button in the Toolbox and drag out a rectangle on the form. The Insert Hyperlink form now appears and you can complete it in the same way as before. Delete the old control and move the new one into its place. Add a new label for the hyperlink. Format the new controls to match the others by opening the Element Properties page and changing the BackgroundColor and Color properties.
To add a Smart Tag right click on the new hyperlink control and choose Element Properties from the popup menu. The Smart Tags property is under the Data tab; click the dieresis and choose Person Name from the options at the top of the form, as before. This will add a Smart Tag to the email hyperlink control.
Data Access Pages do not work with OLE objects so the Picture field is not even offered as a choice in the field list by the page wizard. It is still possible, however, to add images from a database to a Data Access Page if the table includes a field that simply stores the address of an image in text format.
As a first step, therefore, we need to amend the CDs table by adding a new field. This field, call it 'CoverPhoto', should be a plain text field, the size to be determined by the length of any path to the images you might want to include (20-50 characters should be fine). Enter the pathname and filename of each image in this field. Sample paths might be:
Back in Design mode for the page click on the Textbox button in the Toolbox and drag out a rectangle for a new field (use any area of the screen for this, you will hide it later under the image). Set the ControlSource property of this new Textbox to the CoverPhoto field you just added in the CDs table. Select and delete the label part of this new control.
Now add the Image control. Make sure you have a suitable space for the image on the page and then click on the Image button in the Toolbox and drag out a rectangle for it. Right click the image and set its ControlSource to:
Move the CoverPhoto text field under the image control to hide it. We now have a design that looks something like this:
Note the CoverPhoto Textbox control underneath the Image control. The images are visible in Web Page Preview and when viewed through a browser.
DAP Example 2 (no external access!)
The dimensions of an Image control on a page are fixed at design time so the images that will be placed in it should be of a fixed size appropriate to the shape of the control. A typical application for an Image control is a mug shot or a picture of a product such as a CD or a book. The size of images for a database form or web page should be fixed so that there is little or no distortion. An extreme would be placing a landscape image inside a portrait place holder.
This form can be used to add new data items, but only to fields inside the group filter, Artist. If the database file is on a network the ability to edit records will depend on access permissions. If we had not chosen a group filter then we could have added complete new records - you pay your money and make your choice!