Recipe 15.6: Create a Web Frontend to an Access Table Using FrontPage
While SharePoint is amazing, it has several limitations. First, it requires a server running Windows 2003 server and Windows SharePoint Services. In addition, the Data View Web Part is read-only and provides no mechanism for updating the data. Is there a way to use FrontPage to create a web frontend to your Access data without employing SharePoint?
You can use the FrontPage Database Interface Wizard to create a complete web site that lets you view and edit data from a database table. The trick in this solution is to create the database connection before running the wizard. Follow these steps to create a web site based on the tblAlbums table from the 15-06.MDB database:
WARNING This solution will not work with a SharePoint-enabled web site.
Startup FrontPage 2003.
Select Select File → New... to create a new web site. On the New task pane, click on "One page Web site..." under New Web site.
At the Web Site Templates dialog box click on Empty Web Site and enter the following location for the Web site:
FrontPage creates a new empty Web site on the current machine. If you do not have a Microsoft web server running on the current machine, you will need to change localhost to the name or address of a Microsoft web server for which you have site creation privileges.
Select File → Import. Click on Add File... from the Import dialog box.
Navigate to the 15-06.MDB sample database and click Open. Click OK to add the database to the site.
When you click OK, FrontPage recognizes that you are importing a database and asks you if you wish to create a database connection for the database.
Enter "15-06" for the database connection name and click Yes to import the database and create the database connection.
FrontPage displays an additional dialog box suggesting that the database be moved to the fpdb folder. This is a good practice so you should click Yes.
Select File → New... to create a new web site. On the New task pane, click on "One page Web site..." under New Web site.
At the Web Site Templates dialog box, select Database Interface Wizard, making sure to check the "Add to current Web site" checkbox before clicking on the OK button.
On the first page of the wizard, under "FrontPage has detected that your page will display best using:", select "ASP.NET" unless you'd prefer to use the older ASP technology.
TIP: You will need to choose ASP instead of ASP.NET if your web server is not configured to support ASP.NET.
Under "This wizard will connect your site to a database. What would you like to do?", select "Use an existing database connection", select "15-06" from the dropdown control, and click Next.
On the second page of the wizard, under "Select the table or view you would like to use for this database connection", select the "tblAlbums" table. Accept the default location for the new files and click Next.
On the third page of the wizard you are given the opportunity to modify the columns to be displayed. There's no need to modify the columns, so click Next.
On the fifth wizard page, you are asked to supply a user name and password to protect the database editor. Enter a user name and password or check the "Don't protect my submission page or my database editor with a username and password" checkbox.
Click Finish to complete the wizard.
The wizard creates a number of pages. Under the 15-06_interface\tblAlbums folder you should find the results_page.aspx page. Select File‡Preview in Browser to display this page in your browser.
You aren't limited to one database interface per web site. You can rerun the FrontPage Database Interface Wizard as many times as you like, creating set of pages for either different tables within the same Access database or different databases. Just remember to check the "Add to current Web site" checkbox before clicking on the OK button when selecting the Database Interface Wizard template.
The steps in this solution apply when using FrontPage 2003. However, except for a few differences, the steps are virtually identical when using FrontPage 2002. (One big difference: FrontPage 2002 doesn't give you the choice of creating the site using ASP or ASP.NET; it always uses the older ASP technology.)
Alternatives to FrontPage
There are a number of technologies you can use to create a web frontend to an Access database, including: Data Access Pages, SharePoint, ASP.NET, Active Server Pages (ASP), Cold Fusion, PHP, and Java Server Pages (JSP). Data Access Pages are discussed in detail in Chapter 13. In addition, an ASP.NET example is included in Chapter 17.
With a wealth of options, you may be wondering which solution will work best for you. Ultimately, a number of factors will help you arrive at a decision. Do you want a solution that you can create quickly with little or no programming? If so, then you probably will want to use the FrontPage Database Interface Wizard (as demonstrated in this solution), Data Access Pages, or SharePoint. Use SharePoint (along with FrontPage) if you need to create a workgroup-based portal. One disadvantage of the SharePoint approach is that you cannot modify the Access data. You can use the FrontPage Database Interface Wizard, as demonstrated in this solution, to quickly create an ASP- or ASP.NET-based frontend to Access that you can then modify and extend using Visual InterDev (if you are using ASP), or Visual Studio .NET (if you are using ASP.NET). Finally, you can also use Data Access Pages, but only for smaller intranet-based sites.
If you're not averse to programming, you may want to use Visual Studio .NET to create an ASP.NET-based web site, or Visual InterDev to create an ASP-based site. In most cases, ASP.NET is the better choice because it provides a programming object model that is more similar to Access than ASP. Other, non-Microsoft options include Cold Fusion, PHP, and Java Server Pages (JSP). You can also use Macromedia's Dreamweaver MX to create Cold Fusion, PHP, and JSP web sites (as well as ASP- and ASP.NET-based sites).
When Access won't do the job
Keep in mind that Access might not be the most appropriate database to use in many Internet-based scenarios. As mentioned in the Solution in Recipe 15.2, Access works well in small workgroup settings, but if you expect a moderate number (over a dozen or so) of simultaneous users, you'd be better served with a server-based database such as SQL Server.
See Database Power with Microsoft FrontPage Version 2002. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfp2k2/html/fp_dbpower.asp