Building A Smart Access eBook

Building A Smart Access eBook

By Garry Robinson

My life as a programmer has greatly benefited from being connected to the Internet. When I first started out on my own 6 years ago, I used to communicate with my clients by sending floppy disk upgrades of my software to my mining clients that were located all over Australia. This was an exercise that involved a 3-5 day turnaround in response times. Then along came email and my interaction with clients became a whole lot better. The next improvement in my programming life was online information. I can now undertake more complex projects on topics because I have a pretty good understanding of where to look for source code and articles on relevant programming topics. But the techniques that I use for this is generally a hit and miss affair. Included in my treasure hunt is usually a search through the Smart Access download database that I have on my PC. If this is out of date, then I have to download and open up the latest version and as it is a 2 megabyte download, I generally head off to smartaccessnewsletter.com instead. This is OK on my cable modem but if I had a dial up modem, I would be getting a bit fed up.

But then a couple of my neurons connected. Why not use Microsoft HTML help to generate a searchable Smart Access index that I could use offline. Lately I have been doing a bit of work with the HTML help format as I use it to turn my web site into a programmers resource kit. I like the HTML help file format because it has a built in Table of Contents and Indexed word support that is easily programmable and a built in search engine. On the end user side it almost universally supported by anyone with a PC which has IE4 or Office 2000 or any other modern Microsoft product. In other words virtually any programmer who reads Smart Access. Rather than market the product any further, see Figure 1 to see what you can now dowload to search for your favourite Smart Access article.

Figure 1. A Smart Access eBook showing Table of Contents and a Links Page

Building Your Own eBooks

Apart from providing you with a new offline searchable resource, this article will demonstrate what you have to do to start building your own eBooks from your own database. Please forgive me if I take a little poetic license by calling these HTML help files eBooks but in essence this style of HTML help file has a lot of the same features. I.e. They can be transferred electronically, they are organized by pages of standard size and they are self contained objects.

To install all the files that make up this eBook builder, download the file SAeBook.exe. This is a winzip executable that will install all the files in a directory called c:\Pinnacle Once you have the system working, you can move the software to a directory of your own choice. The external files that you will need all have a prefix of “zzSAArticles” plus there is a standalone program called fxSwap.exe

Alternatively you can download you may want to download the latest “complete source code for the month” download from www.smartaccessonline.com as all the source code used in this article is available inside the database. By the time you read or download this source code, there should also be additional code to show word indexes. To find the eBook code and objects, look for any object with a “FX_” prefix. After you read the article, There will be tables, queries, forms reports and modules that all help build this eBook.

To construct the HTML help, you are going to have a copy of HTML help compiler that comes with the Microsoft Developer edition of Office 2000. Luckily though, exactly the same tool is available as a free download from

http://msdn.microsoft.com/library/tools/htmlhelp/

Building The HTML Files

Before stepping into the HTML help building technology, I must first explain how to build the HTML detail files that make up all the content (pages) in the eBook. For this I have used what is arguably Microsoft Access’s most useless feature, Export a Report to HTML.

When you right click on a report in the database container, one of the options in save to external file is HTML format. I imagine that most of the readers have used this at least once and ended up with a collection of files with a *.htm file type and no instructions on how to use them. In case you have forgotten what happens in Export a report to HTML, here is what happens.

Firstly the exporting function asks you to nominate a directory where you want the file(s) to appear in.

Then you are required to nominate a HTML template file. This template file tells the exporting routine what the basic layout of the html forms including images such as the Smart Access one. Also you can determine where the Navigation buttons (if any) will appear on the pages that are exported. ZzSAarticles.htm is thie template used for this eBook.

The software then goes ahead and produces a HTML page for every page in your report. Whilst you may feel that it would be better if Access just produced one html file for your report, bad luck as the page is set by the page size of your default printer rather than any useful page size that you may want to nominate. In the case of the Smart Access articles, this is 137 different html files. So before you devote to much time to building your HTML files using report exports, you need to convince your self that Access exporting reports is suited to your requirements.

The mechanics of the export of the report “FXR_saArticles” that is used in this example involves the generation of HTML file names as follows. FX_saArticles.htm and then FX_saArticlesPage2.htm through to FX_saArticlesPage139.htm. Figure 2 shows this export in action. Keeping track of what is exported to HTML is very important in this eBook building software.

Making Reports And HTML Exports Look Good etc

One of the most frustrating things with HTML exports is that things just do not seem to look the same and the reason is because the pages are constructed in a different way. An Access report design is undertaken in paper space so any text or object may appear at any height or location on the report. Unfortunately HTML works like a MS Word document and the only way that you can make text appear in the same paper space fashion that an Access report uses is to use tables and cells.

Access export to HTML uses tables and table cells for all exporting of text. This can make for some funny looking results. In figure 3, the layout of one of the html files is demonstrated by looking at one of the output files in Frontpage design view. For the Smart Access eBook, it just so happens that the layout is quite suited to this page based exporting. See my article in June of 1999 which shows you how you can create HTML files directly from Access. This will probably be a better approach in more complicated cases.

Figure 3. An Access report shown in design view in FrontPage. Note the table cells used for layout.

Now I am going to give a tip that applies especially to these HTML reports but can apply to most Access reports. Try the Verdana font. This is one of the most popular font on the internet and it looks terrific in most Access reports. This especially applies to reports that are viewed in snapshots or on the screen. My clients love this font compared to stodgy old Times New Roman. Its only drawback is that it is a wide font which means that you have to reduce the point size of the text.

One other really weird thing that happens in the HTML output is that vertical blank space in reports is ignored. This means that your reports can bunch up in the vertical direction. I got around this by adding text boxes with a white font so that nothing appears on the page. White font is also a good way of adding key words to a page that will be found by the search engine but will not show up on the actual page. That’s an old trick that is used to get your html page higher in the internet search engines rankings.

Integrating your Report Into HTML Help

Microsoft HTML help is a very open format as it relies on HTML as the basis of the help pages that are used to build the help files. It also has a number of files that use simple text structure for building the different components of the help system. As this is a Access related magazine, I will not go into too much detail on HTML help as this is adequately described in the HTML help compiler. But there are some interesting features in HTML help that you should know about. HTML help uses an Internet Explorer browser object for its browsing and therefore is very adaptable to some of the more extensible components of HTML in that it supports dynamic HTML, visual basic and java scripting. But what I like is that you can hyperlink to web based information and the help file just keeps on working. And the result looks very professional.

The most important thing in a HTML help file building program is that all the HTML files that you want to use are referenced in a project file or linked with hyperlinks in the local directories. In the case of Access report, you only need to reference the first page of your HTML export report and a special little HTML Help web crawler will find all the pages using the local hyperlinks. In the case of this HTML help project, the exported report would be referenced in the project file zzSAarticles.hhp as follows.

[FILES]

FXR_saArticles.htm

That is all that is needed to reference every one of the different pages of the report as the web crawler will find the Next hyperlink on every page (see the top of figure 3) and then go and load the next page in the chain.

Another thing you need to do is too make sure that every HTML page has a Title tag that reflects the content of the page. The Access report exporting function gives each page the same title as the report. This is next to useless as any HTML search function will return pages with meaningless titles. Now we will find out how to change the titles of the HTML pages.

Changing The Title Of Each HTML Page

Dim rstHtml As DAO.Recordset, FileName As String

Dim sqlStr As String

Set rstHtml = CurrentDb.OpenRecordset("FX_htmlOutput", _
dbOpenDynaset, dbAppendOnly)

With rstHtml
.AddNew
DoCmd.SetWarnings False

' Write the HTML output information to a table for
' reuse in a later part of the ebook build

FileName = Me.Name & repSfx_FX(Page) & ".htm"
!htmlName = FileName
!Title = Me.[Title]
!Issue = Format(Issue, "mmm-yy")
.Update

DoCmd.SetWarnings True

End With
rstHtml.Close

Now we need to change all the Title tags in the HTML that has been exported. This all happens in the form or module that is used to run the report export as shown below

Dim i As Integer, htmlFile As String
Dim rstHtml As DAO.Recordset

' The following constant defines where all the
' external files exist and all the output is sent ....

strDir = "c:\pinnacle\"
strFile = "FXR_SAArticles"

' Generate a HTML pages for each page in your report

DoCmd.OutputTo acOutputReport, strFile, _
acFormatHTML, strDir & strFile & ".htm", _
False, "c:\pinnacle\zzSAArticles.htm"

Now we are ready to use the Title information that we stored away in the Page Footer On Print event. The code for this involves a loop through all the records to extract the location of HTML files

Set rstHtml = CurrentDb.OpenRecordset("FX_htmlOutput", _
dbOpenForwardOnly)

' Swap the titles in all the detail HTML files

With rstHtml
Do While Not .EOF
‘ Swap Title Tag strings in a text file

      Call fxSwapStr(strDir & !htmlName, _
       "<TITLE>" & strFile & "</TITLE>", _
       "<TITLE>" & !Title & "</TITLE>")
      .MoveNext

Loop

End With
rstHtml.Close

The hidden work in the above code is undertaken by a module called fxSwapStr. This runs a simple visual basic program that replaces any occurrence of a text string with another text string. The visual basic program fxSwap.exe runs like the SED command that is available on Unix to replace strings in text files. Whilst trying to pass complicated text strings such as those that include inverted comma’s, I decided to enclose the commands inside HTML type tags. That way the text strings became much easier to handle. If you want to handle replacement of strings using your own code, see my good reading section at the end of the article.

Shell strDir & "fxSwap.exe “ _
“ <File>" & FileName & "</File>” _
“<findStr>" & findStr & "</findStr>” _
“<replStr>" & replStr & "</replStr>"

Adding A Table of Contents To Your eBook.

Navigation of your eBook by using the Next, Previous, First and Last hyperlinks on each page of your eBoo whilst being useful is not going to help sell the content of your eBook. The first thing you can do to improve this is too add a Table of Contents (abbreviated as TOC) to the LHS of the eBook. This first involves a reference in your HTML Help project file as follows

Contents file=zzSAarticles.hhc

Now it’s a matter of building the Table of Contents text file from within Microsoft Access. The trick here is to first experiment using the HTML Help graphical user interface to prototype a TOC style that you are happy with. Then you can safely start automating the generation of the TOC text file. The TOC file type should be *.hhc

Following is the text that produces a topic of “Jun-2000” and a subtopic of “Access 2000: An Opinion”. A new topic of “May-2000” is then started. The topic hierarchy is increased by the <UL> tag with the unwinding of topics hierarchy handled by the </UL> tag.

<UL>

When writing the TOC text file from software, I use the grouping on print events to mimic this same hierarchy as shown below

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)

Dim FileName As String, issueStr As String

' Store the filename for the TOC
FileName = Me.Name & repSfx_FX(Page) & ".htm"

' Write the table of contents information

Print #ioTOC, ""
Print #ioTOC, " <LI> <OBJECT type=""text/sitemap"">"
issueStr = Format([Issue], "mmm-yyyy")

Print #ioTOC, " <param name=""Name"" value=""" & issueStr & """>"

Print #ioTOC, _
" <param name=""Local"" value=""" & FileName & """>"

Print #ioTOC, " </OBJECT>"

End Sub

I also write the remainder of the TOC text file in the following report events.

Report_Open, PageFooter_Print, Report_Close, OnClose

You may also want to explore automatic generation of Table Of Contents using the HTML <H1> <H2> <H3> tags. This is described in the HTML help program help.

Building A Page With Hyperlinks.

Probably the most frustrating things about the Access export reporting to HTML functionality is the fact that it is impossible to generate a hyperlink with alternate text on the report pages. If you look at the RHS pane of figure 1, you will see the hyperlinks to articles such as AdHoc report builder. If this was working, the report export ideally would produce something like the following HTML code

<A HREF = FXR_SAArticlespage110.htm>AdHoc Report Builder</A>

Unfortunately the best Access can do is produce the text that tells you what the filename and it certainly isn’t a hyperlink. This even applies to hyperlink fields inside tables which already have all the hyperlink information stored internally. So this brings me to the second report (called FXR_saArticleLinks) that I have used to produce hyperlinks to all the articles grouped by month (see figure 1).

For this report I have utilized all the relevant information from the temporary table that I stored away when extracting the first report (see Changing The Title Of Each HTML Page). But I had to do perform a little trickery on the query that forms the recordsource for the report as follows.

SELECT htmlName,
"!hlst!" & [htmlName] & "!hlmd!" & [title] & "!hled!" AS htmlTag,
title, Issue FROM FX_htmlOutput;

You will note the special strings of !hlst! for hyper link start, !hlmd! for hyperlink middle and !hled! for hyperlink end that I have embedded into the query. This will return a text string that displays on the Access report as shown in figure 4 like the following.

Figure 4 – Tricky hyperlinks displayed on a report. These are converted to hyperlinks on extraction.

strFile = "FXR_SAarticleLinks"
DoCmd.OutputTo acOutputReport, strFile, _
acFormatHTML, strDir & strFile & ".htm", _
False, "c:\pinnacle\zzSAArticles.htm"

' Swap the special tags for hyperlink tags

For i = 1 To noPagesInReport
    htmlFile = strDir & strFile & repSfx_FX(i) & ".htm"
    Call fxSwapStr(htmlFile, "!hlst!", "<A HREF = ")
    Call fxSwapStr(htmlFile, "!hlmd!", ">")
    Call fxSwapStr(htmlFile, "!hled!", "</A>")

This leaves then turns the text in the HTML pages into ordinary hyperlinks as shown below

<A HREF = FXR_SAArticlespage2.htm>Creating Stored Procedures</A>

If you are wondering why I do not simply build the hyperlink text string shown above rather than go about it in the roundabout way that I have and that is because Access report extracting turns all triangular brackets from “<” into “<” which knocked that idea on the head.

Compiling All The HTML Into An eBook

Now the reports and string substitution software have created a separate HTML page describing each individual Smart Access article plus a Table of Contents and 7 pages of links that show the articles in each monthly edition. All that leaves us with is running the HTML help compiler to build the HTML help file, I once again use the shell command to run the HTML Help compiler program using the HTML help project file for this eBook. You will need to change the location of the hhc.exe program to point to your location of the HTML compiler program. The hhc program then produces your eBook.

Shell htmlHelpDir & "hhc.exe " & strDir & "zzSAarticles.hhp"

This then produces a really neat little eBook of less than 100 k that lets you search all your Smart Access articles off line. Hope you will use it as much as I am already.

Conclusion

HTML help is a very widely supported file format that is already installed on almost all Windows PC’s. It has a built in search engine, a table of contents and capabilities for index searching. When you consider that you can readily automate the generation of a HTML Help file from your database, you may well want to distribute your database information in this format rather than forcing every one to use your database (web) interface. So why not try creating your own little eBook.

Useful Further Reading and Resources

To find out more on exporting to HTML, open Access and search for “report static HTML”

It is just as easy to transfer your data directly to HTML files using a method that I explained in the July 99 issue of Smart Access. Search your new Smart Access eBook for “HTML”.

If you are looking for a generic string replacement algorithm, try the Access 97 Freestuff database. Download. How you find this is explained at http://www.vb123.com/toolshed/99/freestuff.htm

See the Saicom newsletter by Nirmala’s for some useful source code on Replacing Strings in Text files

http://www.saicomsystems.com/tip2000v01.htm

To find out about extracting the different parts of a hyperlink field, search Access help for the HyperlinkPart Function Example.

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies web site at http://www.gr-fx.com/ or sign up for his Access email newsletter here. The web site features a popular shareware data mining tool written in Access. When Garry is not sitting at a keyboard, he can be found playing golf or shark wrestling at one of Sydney's lovely bays. Contact details … +61 2 9665 2871

Other Pages On This Site You Might Like To Read

Indexing Tables Tips For Microsoft Access
The Treeview Control In Access
Persistent Caching >>>Take a Load Off Your Server with Access 97-2000

Click here for the download file if you own "The Toolshed" Else click here

This article first appeared in the August 2000 Edition of Smart Access. Reprinted with permission from Pinnacle Publishing (http://www.pinpub.com).

and was written by Garry Robinson from GR-FX Pty Limited