How to display any Access table on the web
A number of recent posts to the microsoft.public.access newsgroup suggest that, more and more, users want to know how to display Access data on the web. Many Access users have looked to Data Access Pages (DAPs) for a web solution. While DAPs provide a rich, easily generated UI, they have serious limitations and it has been hinted that Microsoft will be deprecating Data Access Pages in future versions of Microsoft Office.
Other options include static HTML pages, created directly from the Microsoft Access environment by exporting a table, query or report in that format, but this solution is usually of little value, as the page is merely a snapshot of the data in time. What people really want is to view live data and filter it at will. This requires not static, but active pages and there are a couple of options within the Microsoft product framework.
- Classic Active Server Pages (ASP)
- ASP .Net Pages (ASPX)
The solution in this article uses Classic ASP because it is fast & simple and because I had the code lying around. Perhaps ASP .Net is better suited to the task and I am just being lazy but I really like the ease with which one can create simple ASP web pages using only Notepad as a development tool. Edits are quick, there is no need to compile and distribution is a snap. That having been said, you can see a live demo of the final product, download the code and find simple installation instructions at my web site. (An alternate download is also shown below in the event that my web site not available.)
By the way, if an ASP .Net guru happens upon this article and can replicate this solution with a simple ASPX page, I would love to see it. Please email the solution to me at email@example.com.
Simply stated, the objective is to display the data of an Access table on a web page. However, there are a couple of limitations to keep in mind: Width and Height. Access tables, especially those created without the concept of normalization in mind, become very wide. While it is no big deal to scroll right in an Access datasheet, a web page with 255 columns is virtually useless. Accordingly, tables or queries you display should be narrow with it comes to exposing columns.
The question of height (number of rows) may seem like a trivial matter. After all, if the table or query you are displaying has only 5 or 6 columns, who cares how far down you have to scroll in the browser to view records? Well, maybe you don't mind scrolling, but how long are you willing to wait for the page to display? A better question is, "How long is the web server willing to wait?" It is Unfortunate, but true, that you are limited by the resources of the server. You can increase the Script Timeout property to allow more time for the script to run and/or add more memory to the server, but eventually you reach the point of diminishing returns.
Therefore, what you need is either a filter or a way to page the results in more manageable clumps. This is where a solution in ASP .Net (especially the most recent version) would be more suitable, albeit more complicated. I have seen a demonstration of ASP .Net 2.0 where paging was added by simply editing a property. That's great, if you have Visual Studio 2005, but if you want to develop in Notepad, you are going to have to know quite a bit more about ASPX to make that work.
In lieu of taking the more complex route, our demo application uses Classic ASP and will assume the following:
All Access tables available for viewing data have under 20 columns (minor code modification required to show queries instead of tables)
No column names include spaces, but follow proper-case capitalization rules (script includes function to insert spaces automatically for column heads)
Where no filter is supplied, only the TOP 100 rows are returned
User may supply ad-hoc WHERE condition and/or ORDER BY clause
No paging is provided with this demo (records batched into groups of, for example, 25 rows per page)
This solution doesn't allow for adding, editing or deleting records (see previous DBJ article for explanation of that functionality)
It's not pretty
The Setup Gotchas
The first change that needs to be made to the ShowMeTheTable.asp page from the download is to set it to point at your data file. Open the asp file in Notepad, or your favorite editor, and look for the first line of actual code, after the variable declarations. It currently reads as shown below. You must edit it to point to the location of the Access database file that contains your data.
sMDB = "C:\InetPub\wwwRoot\ShowMeTheTable\SLM.mdb"
However, if you try to run it without making two additional small changes, the code will break. You see, it tries to read the list of tables from the system table, MSysObjects. As it turns out, read permissions need to be set on this object so that the IUSR_MachineName user has rights. The following image shows how that may be accomplished:
That takes care of the first security gotcha. The next issue has to do with how Internet Information Server (IIS) loads ASP pages and manages security. If no form of authentication is forced, then users who access your files do so through the IUSR_MachineName user. Therefore, if your machine is named OoopsIDidItAgain then the user will be IUSR_OoopsIDidItAgain. This user needs to be given Read/Write access to the folder where your Access database resides, even if you are only going to display read-only records. Remember that when you open an Access database, a locking file is created and this user needs to be able to create that file. So, right click the folder and choose Security options. Add this user and save.
What the Code Does
I had intended to reproduce the most critical parts of the code here, but decided against it. Below is a link to a page that shows ALL the code for the ASP page. You could simply copy and paste it into Notepad and save it off as an ASP page, and you would virtually be done, with the exception of taking care of the Gotchas described above.
What I will do here is briefly outline the process that takes place in this ASP code. VB Script runs from TOP to BOTTOM, so the first thing to execute is at the top of the page. I have placed my three functions at the bottom and they are called from various locations in the code. Here's what happens, as it happens:
1. Variables are declared and the path to the Access MDB file is assigned.
(error handling included)
2. Parameters are collected from the previously posted page.
(Name of table to load and the WHERE clause)
3. The HTML part of the page is built with all the requisite tags.
(HEAD, BODY, TITLE, FORM, TABLE, etc.)
- Topmost section displays list of available tables from which user makes selection
Calls function: AvailableTablesList()
- Middle section shows current table, where clause, Query button and Select Table control.
Uses variable, sTableList, populated in function AvailableTablesList()
- Bottom section is the results of the query, in web datasheet view.
Calls function: LoadTable()
Calls function: ConvertToLabel()
4. Add closing HTML tags corresponding to each object in step 3 above.
That is all there is to it, though the above outline may seem oversimplified. There is much room for reformatting the output. You will have to get the code and play with it to see how the data table might be tweaked to meet your needs, but the basic elements are there, with the exception of paging. While stepping through the VB Script and HTML of the page, you will notice a few things that have not been thoroughly explained. They are somewhat beyond the scope of this article, but I suggest you simply accept those things at face value and focus on the table and recordset pieces. That is the heart of this demonstration.
Where to from here...
From here, you can go anywhere you want with your Access data, so long as you do not need to add, edit or delete data. This solution is strictly a read-only display version of your data, with some really, really simple filter application thrown in. However, it's simple and should give you a good starting point for quickly developing ASP pages that make your Microsoft Access data available to Web users in real time.