If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Query analysis tools for the stout of heart.

Let me begin by confessing that I am not qualified to write this article. Everything you read below comes from Internet research, primarily from two sources:

1.  MSDN: Access 2002 Desktop Developer’s Handbook

2.  Google Groups Advanced Search for ‘Showplan’

I’d like to give special thanks to the authors of the Access Developer’s Handbook, and to the professionals that freely give of their time and expertise on the many user groups, including but not limited to Lyle Fairfield, David Fenton, Albert Kallal and Jim Dettman, whose posts proved most helpful in researching this article.

It has, for some time now, been a goal of mine to understand how to analyze queries in both Microsoft Access and SQL Server. I’ve bought a few books and even started reading them on more than one occasion. Nevertheless, for some reason, I just can’t seem to follow through and really devote the time to it that would be required to become proficient at reading the results of analysis tools. Therefore, this article will cover the tools available and to a limited extent, my understanding of how the results may be interpreted. If you need to analyze your queries, you’re going to have to slog through the results yourself and try to make sense of the data returned.

The disclaimer out of the way, let’s plunge headlong into two undocumented and unsupported Jet Optimization tools in Microsoft Access that may help you in identifying performance bottlenecks in your queries:

• Showplan option
• ISAMStats method

Showplan Option

Showplan is an undocumented option that, once enabled, creates a file named showplan.out where query execution plan information is appended each time a query is run. The location of this file can be a bit elusive, but if you don’t find it in the same folder as the currently active mdb file, then look in your My Documents folder. Only one file will be created per folder. Results from all Access database queries are appended to the same file and over time, it will grow large.

Before we describe how to enable this feature, we’re obliged to supply this warning:

Jet Showplan should only be enabled for debugging and analysis. It carries with it a notable performance hit and the increased potential for application crashes.

To turn on Showplan, you simply edit one of the Microsoft Jet registry entries. If you are comfortable with the registry, then simply navigate to the following Windows Registry key using Regedit and set the JETSHOWPLAN key to the text value of ‘ON’
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug.

While I’m comfortable with the Registry, I prefer to create a pair of .reg files for managing this setting with the double-click of my mouse. The following scripts were supplied by the authors of the Access Developer’s Handbook, an indispensable resource for Access professionals. Simply copy and paste each of these scripts into Notepad and save them as .reg files.

JET ShowPlan ver 4.0 ON.reg

  Windows
  Registry Editor Version 5.00
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
  “JETSHOWPLAN”=”ON”

JET ShowPlan ver 4.0 OFF.reg

  Windows
  Registry Editor Version 5.00
  [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
  “JETSHOWPLAN”=”OFF”

Note: You may have version 3.5 of the Jet engine installed. If so this registry key will be found in the following node:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Debug

Additional Note: For Office 12 (Access 2007), the key is located here, but is still undocumented.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug]

Once enabled, you need only open a query to begin logging. (Since this file grows rapidly, I found that, for the sake of this article, it was necessary to save off its contents and delete the showplan.out file from time to time.) I created a simple query to select author name and state from the pubs authors table, where state=’CA’. Below are the results, before and after an index was added to the state field.

First Query … single table with where clause:
SELECT authors.au_lname, authors.au_fname, authors.state
FROM authors WHERE authors.state=’CA’;

— Query1 —
Author note: most recent query run is on top
- Inputs to Query -
Table ‘authors’
- End inputs to Query -
01) Restrict rows of table authors
      using index ’state’
      for expression “authors.state=’CA’”

— Query1 —
Author note: First run without index on state column
- Inputs to Query -
Table ‘authors’
- End inputs to Query -
01) Restrict rows of table authors
      by scanning
      testing expression “authors.state=’CA’”

Second Query … with primary key links and group by clause:
SELECT stores.stor_name, sales.ord_date, Sum(sales.qty) AS [Total Books Sold]
FROM stores INNER JOIN sales ON stores.stor_id = sales.stor_id
GROUP BY stores.stor_name, sales.ord_date;

— Query2 —
Results after adding Primary Keys to tables
- Inputs to Query -
Table ’sales’
    Using index ’storessales’
    Having Indexes:
    storessales 21 entries, 1 page, 6 values
      which has 1 column, fixed
    PrimaryKey 21 entries, 1 page, 21 values
      which has 3 columns, fixed, unique, primary-key, no-nulls
Table ’stores’
- End inputs to Query -
01) Inner Join table ’stores’ to table ’sales’
      using index ’sales!storessales’
      join expression “stores.stor_id=sales.stor_id”
02) Group result of ‘01)’
— Query2 —
First query run without primary keys defined
- Inputs to Query -
Table ’sales’
Table ’stores’
- End inputs to Query -
01) Sort table ’sales’
02) Inner Join table ’stores’ to result of ‘01)’
      using temporary index
      join expression “stores.stor_id=sales.stor_id”
03) Group result of ‘02)’

Now comes the hard part … interpreting the results. I’ve marked what I believe to be the critical results above with red text. In the first query example, we are informed that rows were returned as a result of “scanning.” This is bad. We want the query optimizer to find an index, as it does in the second run (top result) after having added the state index.

In the second query example, linked tables had no primary keys defined, and no indexes. The first run (bottom result) shows that a temporary index was used. I suppose it’s good that Access created a temporary index, but I suspect it’s better to have created the indexes myself. The second run takes advantage of the ’sales!storessales’ index that was created when I added a primary key to Store, and created a relationship to the sales table.

These examples are simple and obvious. To test something more complex, I cleared the showplan.out file and launched my most complicated application. After the splash screen and startup functions, I selected a search from the menu and loaded a complex form with subforms. From that form, I double-clicked to drill to an additional form. The results? Showplan grew to 77 kb which took up 77 pages in Microsoft Word. A total of 216 entries were made to Showplan.out in less than 2 minutes.

What does this mean? I don’t know. I’d have to pore over all these results to identify each and decide which, if any, could be optimized. Clearly, this is not the way to use the showplan option.

One of the newsgroup posts I found was from an accomplished developer named David Fenton. He posted the following to Comp.Databases.MS-Access on 2002/01/28 …

It’s an eye opener. I had one app where running a single normal everyday process caused the log file to grow from 38K to nearly 2MBs (it was updating fields in a temp table using functions that used DAO to get their values; every call to those DAO functions got listed in Showplan, in one case for a couple of thousand records in the updated table!). I completely re-architected that component of the app, and the users are delighted.

ISAMStats method

If it can be said that I know little about Showplan, I know even less about ISAMStats, but having run across it in my research, I’m compelled to include it for those of you who wish to take your query analysis to another level. Again I emphasize that the code below is something revealed by my research and credit should go to the two references at the top of this article.

The ISAMStats return metadata such as disk reads and writes. The DAO method for retrieving this information is shown below. I also created a disposable function to run DiskStats against two similar queries. The first is the query analyzed above named Query1. It simply selects a list of authors who live in the state of CA. After having run the DiskStats 3 times and recording the results, I changed the query where clause to allow the user to enter a state.

Option Compare Database
Option Explicit
Public Function DiskStats(bolReset As Boolean)
  On Error Resume Next

  Dim lngTemp    As Long
  If bolReset = True Then
    DBEngine(0).BeginTrans
    DBEngine(0).CommitTrans
    lngTemp = DBEngine.ISAMStats(0, True)
    lngTemp = DBEngine.ISAMStats(2, True)
  Else
    MsgBox “disk read = ” & DBEngine.ISAMStats(0) & vbCrLf & _
           “disk write = ” & DBEngine.ISAMStats(1) & vbCrLf & _
           “cache read = ” & DBEngine.ISAMStats(2) & vbCrLf & _
           “read ahead = ” & DBEngine.ISAMStats(3) & vbCrLf & _
           “locks placed = ” & DBEngine.ISAMStats(4) & vbCrLf & _
           “locks released = ” & DBEngine.ISAMStats(5) & vbCrLf
  End If
End Function
Function TestDiskStats()
  Call DiskStats(True)
  DoCmd.OpenQuery “Query1″
  Call DiskStats(False)
End Function

I can’t say the results were surprising, because I really didn’t know what to expect. What’s more, I can’t interpret them for you either. For that, you’ll have to check MSDN or post your question to the newsgroups where you will find someone who knows more about it than I do. However, I can analyze the results and see that where the state was ‘hard coded’ to CA, it required fewer disk reads, fewer cache reads and fewer locks. So, while I don’t necessarily understand what each attribute represents, I can use them for making comparisons. Run the stats, modify the query and re-run stats to determine which method is more efficient.

Does this help?

I had promised 4 installments of Queries on Steroids, including a discussion of Showplan. I’m not sure how helpful it is to me, let alone my readers, but as usual I’d be happy to hear from you. Perhaps you can add to this discussion and share your techniques on analyzing and interpreting the results of Jet Showplan and ISAMStats.

—

by Danny Lesandrini 

• 0 Comments

In this article we discuss Fancy Filtering Techniques for Queries on Steroids. Before we’re done, we will have shown you three ways to return a filtered query. If you want to play with the code for this article, download it here.

The first method is the one you read about in chapter 3 of Access For Dummies, and while it does work, it’s incredibly tacky, forcing the user to type in the filter value. The second method filters a query based on some value, which resides on an open form. This method is much more elegant, but works only when you cross your fingers. Ok, that’s not completely true, but it seems that way sometimes.

The third method is what I call The Fancy Filtering Technique and it was developed in order to meet these requirements:

1. Where possible, users should NOT have to type in the filter value
2. The filter should always work, even when a particular form isn’t loaded
3. The filter should be smart
   • lookup the filter parameter where possible
   • substitute a default where reasonable
   • ask the user to select where necessary
   • provide custom dialog box where reasonable
4. Remember user selections for subsequent requests
5. Log errors and even use statistics if you’re anal

Queries built with straw

Yes, the three little Piggys built their queries. The first little piggy read about parameter queries and decided that was pretty slick and it was so simple to use. Here’s how it works:

• Create a new query and add your tables and field
• Add a prompt in square brackets to the criteria row of the filter column

Very simple approach and actually quite flexible. The SQL for such a query, shown below, illustrates how this approach may even be used with wildcard matching. If you need a quick-and-dirty way to deliver a filtered query, this will work. However, the dialog box for supplying the filter criteria is free text and users may type whatever they want.

The second little Piggy recognized that sometimes users can’t even supply the required parameter, as in the case where you might pass a record ID to a report. Users don’t think in terms of unique IDs and getting them to locate it on the form and type it into a parameter box is asking for trouble.

Accordingly, the following technique was developed whereby the query could “look up” the ID from the open form. We do this in VBA code all the time, reference the value of a control, and the syntax looks like this:

    SELECT * FROM authors WHERE [au_id] = Forms!frmDemo!au_id

This is elegant, because it’s invisible to the user. You could place a report button on the form that opens a report filtered for the current record. The problem is, what happens when someone opens that report when the form is NOT open? Well, to Access it now just looks like a parameter query and the same Enter Parameter Value dialog box appears, prompting the user to enter the not-so-intuitive ID value, only this time, the prompt message is more cryptic than before.

Now maybe this will never happen in your applications, so go ahead and use references to form variables. It’s not an evil technique, but it lacks the finesse of our next method …

Queries built with brick

Fancy filtered queries, according to our requirements above, are smart. They’re smart because we’ve made them smart. We, the developers, have an idea where to get the filter parameters and if they’re not there, we presumably know what to do next. Maybe, if the form isn’t open, we flash a warning message and shut down the user requested process. Perhaps we simply return the report with no data, or we could log the request as an error. The point is, when we offload the processing to VBA code, we can do anything we wish.

Below is a screen shot of this month’s demo app, available in the download. The top part of the form shows the current author record information. The top two buttons demonstrate how to implement the methods described in the section above. The bottom portion of the form demonstrates two different ways to implement the Fancy Filtering technique.

The first implementation uses a pair of module level functions: SetAuthorID() and GetAuthorID(). The SetAuthorID() may be executed wherever it makes sense in your application, but for our demo, we will update the property each time the form navigates to a new record. In the Form_Current() event, the current au_id is passed to SetAuthorID() and the private module level variable, m_sAuthorID, is updated with the new value. Now that the private variable is updated, the GetAuthorID() function can retrieve it for use in the query.

Option Compare Database
    Option Explicit

    Private m_sAuthorID As String

    Public Function SetAuthorID(ByVal sID As String) As Boolean
        m_sAuthorID = sID
    End Function

    Public Function GetAuthorID() As String
        GetAuthorID = m_sAuthorID
    End Function

This is a simple, but effective way of saving the record ID while the program is running, but the value is saved in RAM memory and is of course lost when the user closes the app. In most cases, that is all that’s required, but what if you wanted to ‘remember’ over time, the records visited by a user. A slightly modified version of the above functions will do the job.

Below is the code for the new functions, SetHistoryAuthorID() and GetHistoryAuthorID(). This code presupposes there is a history table named [UserRecordHistory] that is updated each time a user navigates to a new record. The code for SetHistoryAuthorID() creates the history record, assuming the table is ‘authors’ and the user is CurrentUser(). (Note that this built-in Access function will always return Admin unless Access security has been implemented.)

The code for GetHistoryAuthorID() is a little more involved. First, it looks in the UserRecordHistory table to find the latest record. But what if the current user has no history record? Rather than failing and returning nothing, this Smart Lookup takes another tack. It checks the private module variable to see if perhaps it is populated. If not, then it checks to see if frmDemo happens to be open. If not, then it finally gives up and returns nothing.

    Public Function SetHistoryAuthorID(ByVal sID As String) As Boolean
        Dim sSQL As String

        sSQL = “INSERT INTO UserRecordHistory ([TableName], [RecordID], [UserName]) ” & _
               “VALUES (’authors’,'” & sID & “‘,’” & CurrentUser() & “‘);”
        CurrentDb.Execute sSQL

    End Function

    Public Function GetHistoryAuthorID() As String
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim sSQL As String

        sSQL = “SELECT TOP 1 RecordID FROM UserRecordHistory ” & _
               “WHERE [TableName]=’authors’ ” & _
               “AND   [UserName]=’” & CurrentUser() & “‘ ” & _
               “ORDER BY [DateEntered] DESC”

        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

        If Not rst.BOF And Not rst.EOF Then
            GetHistoryAuthorID = rst!RecordID
        Else
            ‘ History is empty … try something else
            If m_sAuthorID <> “” Then
                GetHistoryAuthorID = m_sAuthorID
            Else
                If CurrentProject.AllForms(”frmDemo”).IsLoaded Then
                    GetHistoryAuthorID = Nz(Forms!frmDemo!au_id, “”)
                Else
                    GetHistoryAuthorID = “”
                End If
            End If
        End If

        Set rst = Nothing
        Set dbs = Nothing

    End Function

The last option is to open a custom dialog form that allows the user to select an author by name, but have their ID saved to the query. For this scenario, the demo form opens the author lookup form, but passes the name of the query in the OpenArgs property. Once the user selects an author, the ID value is saved using SetAuthorID() from the code above, the lookup form is closed and the query opened.

One more to go

That concludes Part III of the Queries On Steroids series. I’ve saved the hardest for last: Jet ShowPlan. Check back to see if I can pull this off. There’s little written about this virtually hidden and seldom used feature of Microsoft Access.

—

by Danny Lesandrini 

• 0 Comments

The key words for today’s query tip are simple and dynamic.

There are two ways to read data from a remote Access database:
Create a link to the table and open it.
Just open it.

No, I’m not pulling your leg. Today we’ll see how you can write a query in Access that points to and opens data in a remote Microsoft Access database without linking to it first. I’ve created a demo database that pulls data, simply and dynamically, from a PubsData database and you can play with it yourself in this download.

However, in this article we are not going to make you wait to see the code. It’s so simple, you can virtually understand every nuisance by considering the following screen shot from the Query Design Window (in SQL view).

To pull data from the sales table in the PubsData.mdb database, you simply point to its network location. Since this file is in a folder named C:\Articles\QueryTricks\, we simply add that to the query, so the sql becomes …

  SELECT * FROM [C:DevelopmentArticlesPubsData.mdb].[sales] ORDER BY ord_date DESC

That’s all there is to it. You now know 90% of what is in the demo. The only thing left to discuss is how you might leverage this nifty little trick in your applications.

What about Performance?

The first question you should be asking yourself is “How’s the performance of this approach?” Frankly, I don’t know. I have never actually seen a performance issue when using these queries, but then again, I have never built an entire application with them. (Something I wouldn’t recommend, though I can’t give you any evidence why.) Here’s a link to a Microsoft KB article that will tell you much more than I aspire to in this article, and below is the only mention it makes of performance.

How To Access Multiple Databases in an SQL Query

Attaching tables has powerful performance and administrative advantages — especially if you are executing queries repeatedly. However, sometimes you might want to take the slower route described in this article when you need to use ad hoc queries that encompass two or more databases. This article explains how to construct these slower cross-database queries.

So, there you have it. Attached tables have a performance advantage, but this approach is great for ad-hoc, cross-database queries. The article includes additional syntax for creating ad-hoc odbc queries and explains additional syntax for Access databases with security implemented.

Where to use Simple-Dynamic Queries?

The demo code for this article (see screen shot below) suggests two ways you can implement this idea:

1. Saved queries that point to remote database tables

2. Dynamic rowsource SQL for combo and listbox controls

The top listbox shows a bunch of saved queries that all begin with the text Remote_Table_. Upon opening, my code loops through all QueryDefs of the database and when it finds one of these, it rewrites the SQL and points the query to the PubsData.mdb file in the same folder as the current db. Thus, the app contains six queries that are “linked” to the PubsData database and may be used as you would any tables: adding, editing or deleting records as you see fit.

The bottom half of the form displays two listboxes whose row source properties are set dynamically, based upon the file path in the text box just above them. It defaults to the PubsData.mdb, assuming that you will unpack the full contents of the zip file into a temp folder. However, you can enter any valid path to an Access database and view the catalog of local tables. Select a table and the listbox to the right is loaded with the TOP 10 records and left 4 columns of data. Double click on a table in the catalog, and a saved query is modified to point to that table and the new query is opened.

As an aside, the catalog of tables is easy to find because every Access database includes a table named MSysObjects, which includes all of the tables, forms, etc. in the database. By filtering for Type=1 and excluding tables that start with MSys, we get a list of your special, local tables.

Give it a Try

This is one of those “hip pocket” tools that you should just keep in mind when thinking about pulling data from remote data sources. Sure, there’s a performance hit and I wouldn’t build an application with this paradigm, but I’m sure glad Microsoft included this functionality.

Part II of Queries on Steroids was short and sweet, so if your appetite for Query Tricks isn’t quite satiated, stop by next month for Part III: Fancy Filtering Techniques.

—

by Danny Lesandrini

• 0 Comments

There are some things you just can’t do with SQL. The requests usually go something like this …

Please list ALL phone numbers related to this contact.
Show all items related to the current topic.
Include all notes in a single field of the query.

This is difficult to do in a query … and impossible to do with ANSI SQL alone!  Read on as I explain how this task might be accomplished with the use of a semi-generic function you create in MS Access.

> As usual, the code for this article is available for download.

The Problem Defined

The basic problem is this: For each author, there are multiple titles. So if I do a SQL join from the [authors] table to the [titles] table (through the many-to-many [titleauthor] table), it produces multiple rows for each author where the author is associated with more than one title. (Notice query output below).

Sometimes, this is exactly the output we want, but for this spec, what we want is one row per author, with ALL of their titles in a single cell, like the output below. This cannot be accomplished by means of a SQL statement, no matter how complex the joins.

The only way to accomplish this is by calling a VBA function from the query, as shown in the screen shot (below) of the Access Query Design Grid. This query calls the GetTitles() function twice (Titles and Titles2 columns) to display the results in two different ways. We’ll explain that in minute when we describe the code behind this function, but it underscores the reusability of such functions.

What’s Your Pleasure?

I can’t count the number of responses I had to the article I wrote back in August 2003 on this subject. It was titled Concatenate Column Values and used VBA code to pivot a query into a temp table for reporting on multi-valued rows. It seems that this idea of combining multiple columns into a single row is very popular, and while that article was fun, it really wasn’t that practical. In addition, adapting it to work for slightly different scenarios was really complicated and I spent many hours assisting readers with their code. This time, we try a different approach.

The approach described below is easier to understand and more extensible. You may need (or want) more than one of these functions, but they will all look very similar to what is shown below. The process goes as follows:

1.  Create a function that will be called from the query.

2.  Function accepts an argument corresponding to the data row’s PRIMARY KEY.

3.  A user-supplied delimiter character is used to separate data values.

4.  A DAO recordset is created and looped to generate output string.

5.  Function returns this output as a string value.

For our example, we’re going to make the function specific to the problem of returning the book titles for a given author. In this case, AuthorID is actually a string value that possibly corresponds to the author’s social security number. Since SSN is a text value, our first argument is a string, sID. The second argument of the function, sDelim, is the character you wish to insert in-between titles. It could be a comma, a space, the pipe character or a line feed.

(For the record, from VBA we can use vbCrLf to represent a Carriage Return/Line Feed combo, but from a query we must use the actual characters, which translate to Chr(13) & Chr(10), as shown above in the screen shot.)

For your applications, you will need to change the SQL Statement used to return the recordset. If your key value is numeric, then omit the single quotes. If your key is text that might include single quotes, you either need to double them with the replace function, or enclose the key with double quotes, not single quotes, something like one of these two options …

sSQL = “SELECT title FROM qryTitles ” & _
           “WHERE AuthorName =

              ” & “”"” & sID & “”"” & _
           ” ORDER BY title;”

or …

sSQL = “SELECT title FROM qryTitles ” & _
           “WHERE AuthorName =

              ‘” & Replace(sID, “‘”, “””) & “‘ ” & _
           “ORDER BY title;”

Hopefully, you will be looking up records by their numeric IDs so this will be a moot point, but there are probably applications like this Pubs database where entities are identified by text based keys.

The rest of the code is pretty basic. An output variable, sOut, is repeatedly concatenated with the current item value and delimiter string. When finished, the last delimiter that was tacked on is stripped off. Where the string is empty, that call is skipped to avoid an error and the result is returned by the function.

Public Function GetTitles(sID As String, sDelim as String) As String
    On Error Resume Next
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim sOut As String

    Set dbs = CurrentDb
    sSQL = “SELECT title FROM titles INNER JOIN titleauthor ” & _
           “ON titles.title_id = titleauthor.title_id ” & _
           “WHERE au_id = ‘” & sID & “‘ ” & _
           “ORDER BY title;”
    Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

    Do Until rst.EOF
        sOut = sOut & rst!title & sDelim
        rst.MoveNext
    Loop

    If Len(sOut) > Len(sDelim) Then
        sOut = Left(sOut, Len(sOut) - Len(sDelim))
    End If

    Set rst = Nothing
    Set dbs = Nothing

    GetTitles =
      sOut

End Function

From here …

As previously mentioned, you are going to have to modify this function to meet your specific needs. It’s not that I don’t like email, but before you write for help, step through the code in debug mode and watch what is happening. The most important pieces of this process are the KEY that is passed to the function, and the SQL used to return a filtered recordset based on that key. If your results look wrong, these are the elements of the function to suspect.

If you’re clever, you can probably think of ways to make this function more generic. It could accept the name of a sorted query as an additional argument and if the data type of the key was certain, the SQL statement could be generated on the fly, making this function very, very useful indeed.

—

by Danny Lesandrini 

• 0 Comments

My last couple of articles have featured a demo app affectionately named Something Not Entirely Unlike Access. The application employs a variety of methods to obfuscate the “Accessian” features. In our last article, we discussed how to automatically resize subforms, the way some browser frames work. This month, we’ll tackle navigation.

Once again, the download is the same as last two month’s. The screen shot below displays the main form in design view, and you’ll notice the Internet Exploreresque Forward and Back buttons in the upper left corner. Programming the logic behind their navigation is this month’s project.

The Easy Piece

Let’s get the easy piece out of the way. The form has at least three buttons: one to navigate back, one to navigate forward, and a Home or Start Page button. The images were easy to create using MSPaint and a screen shot of Internet Explorer. Once you have an image you like, just assign it to the [Picture] property of the button.

As shown in the screen shot, I’ve used the technique whereby I assign a public function to the OnClick event. (A public sub should work in newer versions of Access, but I believe even Access 2000 would not recognize the assignment unless it was a Function, not a Sub.) So by clicking the back button, a function on frmMain called MainNavBack() is executed. The code for these functions, MainNavBack() and MainNavForward(), is shown below.

Each of these functions calls another function on frmMain named LoadMainSubform(). This process was described in our first article, which introduced the Something Not Entirely Unlike Access application. Accordingly, we won’t go into detail on what this function does, but suffice it to say that, given the name of a form, it loads and resizes that form into the single subform object on frmMain, effectively changing pages.

This month’s trick will be logging page visits and then determining the correct page to load when users click Back or Forward. To do this, we create and implement our own navigation class, named clsNavigation. We’ll show the code for the class a little later, but first let’s show how it’s used. Implementation in the form requires these steps:

1. Declare a module level object of type clsNavigation using the NEW modifier.
2. Call the object’s Load method in the form’s Open event.
3. For each new page, call the object’s AddNavPage method to log the visit.
4. Create a function to call the object’s NavPrevPage method to find the previous page.
5. Create a function to call the object’s NavNextPage method to find the next page.

That’s all we have to do on the form. Our work is done here. The real logic exists in the class, clsNavigation. So long as you have the above five steps implemented correctly in the form, everything else depends on the class module, which you may freely import into your application from the download code. You’ll need to tweak it a little to get it to work, but it’s all there, and more.

    ‘ First, create a module level navigation object of
    ‘ type clsNavigation.  (this class does not yet exist
    ‘ … we will be building it shortly.)
    Private m_objNav As New clsNavigation

    Private Sub Form_Open(Cancel As Integer)
        On Error GoTo Err_Handler

        ‘ Create class to manage navigation controls
        m_objNav.Load

        ‘ do other Form Open stuff here …
    End Function

    Public Function MainNavBack() As Boolean
        On Error GoTo Err_Handler
        Dim strPage As String

        strPage = m_objNav.NavPrevPage
        Call LoadMainSubform(strPage, False)
    Exit_Here:
        Exit Function
    Err_Handler:
        MsgBox Err.Description, vbCritical
        Resume Next
    End Function

    Public Function MainNavForward() As Boolean
        On Error GoTo Err_Handler
        Dim strPage As String

        strPage = m_objNav.NavNextPage
        Call LoadMainSubform(strPage, False)
    Exit_Here:
        Exit Function
    Err_Handler:
        MsgBox Err.Description, vbCritical
        Resume Next
    End Function

    Public Sub LoadMainSubform(ByVal sFormName As String, _
                               ByVal fLogNav As Boolean    )
        On Error GoTo Err_Handler

        ‘ Update the navigation object with the new page,
        ‘ unless the LogNav flag is set to False. This
        ‘ give you flexibility to skip logging for some pages.
        If fLogNav = True Then m_objNav.AddNavPage sFormName

        ‘ continue with process of loading form …
    End Function

Creating and Loading The Navigation Class

Technically, the Navigation class is created above in the declaration. When the module level variable, m_objNav, is declared with the New modifier, the class is instantiated and ready to use. Because it is a module level variable, it persists as long as frmMain is open.

To build the class, you need to select Class Module from the Insert menu option. A Class module is different from a Standard module, so be sure that all the code that follows goes into a Class Module. When the above-mentioned variable, m_objNav, is declared, an instance of the class is created. (Notice that the icon for a Class module is different from the icon for a Standard module.)

While an instance of the class may exist in m_objNav, it can’t really do anything until the Load method is called. Loading the class does little more than create an in-memory ADO recordset, into which we will load our data. The code looks like this …

Option Compare Database
Option Explicit

Private c_rstNav As ADODB.Recordset
Private c_intCurrItem As Integer
Private c_intMaxItem As Integer
Private c_fMovedBack As Boolean

    Public Sub Load()
        On Error GoTo Err_Handler

        ‘ Instantiate the private level ADO recordset object, add
        ‘ as many fields as you’d like, and load the first record.
        Set c_rstNav = New ADODB.Recordset
        With c_rstNav
            .Fields.Append “ItemID”, adInteger
            .Fields.Append “Value”, adVarChar, 64
            .Fields.Append “CustomerID”, adVarChar, 5
            .Fields.Append “EmployeeID”, adInteger
            .Fields.Append “ProductID”, adInteger
            .Fields.Append “OrderID”, adInteger
            .Fields.Append “URL”, adVarChar, 512
            .Open

            ‘ Note that I have added various IDs to the recordset.
            ‘ These will be used to load the correct record.
            ‘ I always include functions to set and get these IDs.
            ‘
            ‘ This could be recoded to be more flexible, but since
            ‘ the demo app uses this format, I’ll let it be for now.

            .AddNew
            !ItemID = 0
            !Value = “frmStartPage”
            !CustomerID = GetCustomerID()
            !EmployeeID = GetEmployeeID()
            !ProductID = GetProductID()
            !OrderID = GetOrderID()
            .Update
        End With

    Exit_Here:
        Exit Sub
    Err_Handler:
        MsgBox Err.Description, vbCritical
        Resume Next
    End Sub

The class is now loaded. The private ADO recordset, c_rstNav, exists and contains a single, initial record. This recordset will persist so long as the class persists. The class persists as long as the form is open. So, we’ve created a little in-memory log book of the pages visited by our user.

Adding a Page

The next action we need to code is the adding of a page. Of course, we need to log the page’s name, but we’re also going to need some additional information. For example, if we just navigated to frmCustomer, we’re also going to need to know WHICH customer, that is, what was the CustomerID at the moment the form was loaded?

Once again, having considered my previous articles would be of benefit, but in a nutshell, here’s how I load forms: When the user double-clicks on, for example, the row of the Customers List Subform, the current row’s CustomerID is saved by means of the SetCustomerID() public function. When the form is loaded, it uses the GetCustomerID() method to determine which record to load. So, at the time the page is “navigated to,” the CustomerID is known and can be persisted in our navigation object. The same is true for EmployeeID, ProductID, OrderID or URL, if the form is loading a web page.

Now, I should be ashamed of myself for this clumsy and non-extensible code. It would have been much better to have only two columns: KeyFieldName and KeyFieldValue. These could be reused more efficiently. When the frmCustomer is loaded, the [KeyFieldName] would be set to the text “CustomerID” and the [KeyFieldValue] to its value. When frmEmployee is loaded, [KeyFieldName] would be “EmployeeID” and [KeyFieldValue] would contain the current EmployeeID. This would have been smart, but as it turns out, that’s not how the demo code works, so I won’t bother tweaking it now, but as you can see from the code below, it’s pretty simple to modify this navigation recordset. Add and/or remove fields as you wish. Play with it … it’s fun.

The AddNavPage() method has some tricks to it. First, the argument, sValue, must exist. This part of the code could be smarter too, by checking to see that a form actually exists by the name passed in sValue.

In order to behave as Internet Explorer does, the FlushForward method must be called each time a page is added. It basically resets the recordset to make the current page the last record. It’s like with IE (or any other browser) when you navigate back three or four pages, and then go to a new page. The browser dumps any knowledge of those pages and starts off on a new path, with the current page becoming the last in the string of pages.

Next the c_rstNav recordset object is manipulated to locate our starting point, the last ItemID and value in its set. The strLastValue variable is used to avoid adding rows for the same page multiple times. (The Refresh or Requery action of the form may trigger this method and we don’t need to log those events.) I’ve also added a condition to allow for logging of multiple pages so long as the form is frmIE, which is a browser control.

Once the record is successfully saved, we increment the class variables c_intMaxItem and c_intCurrItem, which will be used later when locating the requested navigation page. At this point, our page is logged in the recordset, and the class properties are set.

Public Sub AddNavPage(ByVal sValue As String)
        On Error GoTo Err_Handler

        Dim intLastItem As Integer
        Dim strLastValue As String

        If Trim(sValue) = “” Then
            Exit Sub
        Else
            Call FlushForward

            With c_rstNav
                If Not .EOF Then .MoveLast Else .MovePrevious
                If Not .BOF Then
                    intLastItem = !ItemID
                    strLastValue = !Value
                End If

                ‘ Always log IE browser pages.
                If sValue <> strLastValue Or sValue = “frmIE” Then
                    .AddNew
                    !ItemID = intLastItem + 1
                    !CustomerID = GetCustomerID()
                    !Value = sValue
                    !EmployeeID = GetEmployeeID()
                    !ProductID = GetProductID()
                    !OrderID = GetOrderID()
                    !URL = GetURL()

                    On Error Resume Next
                    .Update
                    If Err.Number = 0 Then
                        c_intMaxItem = c_intMaxItem + 1
                        c_intCurrItem = c_intMaxItem
                    End If
                End If
            End With
        End If

    Exit_Here:
        Exit Sub
    Err_Handler:
        MsgBox Err.Description, vbCritical
        Resume Next
    End Sub

Retrieving A Page

The code for NavNextPage is shown below, but NavPrevPage is nearly identical. All this code needs to do is locate the previous or next record in the recordset and extract the form name and associated IDs. This is where that class variable, c_intCurrItem, comes into play. The Find method is used with the c_rstNav recordset to locate c_intCurrItem. Once found, the values of the row are read and the form name is returned by the function.

    Public Function NavNextPage() As String
        On Error GoTo Err_Handler

        Dim strOut As String

        c_intCurrItem = c_intCurrItem + 1
        If c_intCurrItem > c_intMaxItem Then
            c_intCurrItem = c_intMaxItem
            strOut = “-1″
        End If

        With c_rstNav
            If Not .BOF Then .MoveFirst
            .Find “[ItemID]=” & c_intCurrItem
            If Not .EOF Then
                strOut = !Value
                SetCustomerID Nz(!CustomerID, “”)
                SetEmployeeID Nz(!EmployeeID, 0)
                SetProductID Nz(!ProductID, 0)
                SetOrderID Nz(!OrderID, 0)
                SetURL Nz(!URL, “http://www.amazecreations.com/datafast/”)
            End If
        End With

    Exit_Here:
        NavNextPage = strOut
        Exit Function
    Err_Handler:
        MsgBox Err.Description, vbCritical
        Resume Next
    End Function

Final Thoughts

In reviewing the code, I can’t help but think that I’ve over-complicated the process and over-simplified the explanation. That having been said, I still think this code may be of benefit to readers. It can be improved upon, it’s true and it will take some concentration to read and understand all that is taking place, but that’s what programming is about. It’s not perfect, but it’s a start.

—

by Danny Lesandrini

• 0 Comments

In the last article we introduced the application we’re calling Something Not Entirely Unlike Access, which simulates some aspects of a web browser in Microsoft Access. This article will focus on the process of resizing subforms on the main form, and the download is the same as last article’s. The screen shot below displays four subforms:  two wide ones on the left, and two narrower ones on the right. In this example, all four have the same height, but as you’ll see, that too is adjustable.

Public Const cGap As Long = 100

lngObjWidth = Me.InsideWidth - (cGap * 2)

lngHeadFoot = Me.Section(acHeader).Height + Me.Section(acFooter).Height
lngObjHeight = Me.InsideHeight - (lngHeadFoot + (cGap * 2))

Public Function ResizeControls
	(ByVal lObjWidth As Long, ByVal lObjHeight As Long) As Long
    On Error GoTo Err_Handler

    Dim lngWidthLeft As Long
    Dim lngWidthRight As Long
    Dim lngHeightLeft As Long
    Dim lngHeight As Long
    Dim lngHorOffset As Long
    Dim lngVerOffset As Long

    ‘ The following two public function calls
	‘ perform some standard formatting.
    ‘ The first one sets the forms colors, such as control font color,
    ‘ section back colors and the like.  The download includes this code,
    ‘ which is relatively generic.  The argument passed is the form
    ‘ itself (Me), to which the modifications are being made.
    Call SetFormColors(Me)

    ‘The code for setting the header controls is more involved,
    ‘ requiring some resizing.  Accordingly, this function is explained
    ‘ below.
    g_lngResult = SetHeaderCtls(Me, lObjWidth)

    ‘ This first step is a little tricky.  I wanted to account for
    ‘ scrollbars, but not every form has it’s Horizontal and/or vertical
    ‘ scroll bars set. So I created a function, GetScrollbarOffset(),
    ‘ which would determine how much space should be allotted.  The code
    ‘ for that is in the download file.

    ‘ Determine the control widths.
    ‘ In this example, I’m allotting 70% to the left side controls,
    ‘ and 30% to the right side controls.  I’m also allowing for the
    ‘ space of 2 gaps.
    lngHorOffset = GetScrollbarOffset(Me, “V”) + (cGap * 2)
    lngWidthLeft = (lObjWidth - lngHorOffset) * 0.7
    lngWidthRight = (lObjWidth - lngHorOffset) * 0.3

    ‘ Determine the controls heights.
    ‘ This is similar to the process above, except we must account
    ‘ for the header section.
    lngVerOffset = GetScrollbarOffset(Me, “H”) + (cGap * 2)
	+ Me.Section(acHeader).Height
    lngHeight = (lObjHeight - lngVerOffset) / 2

    ‘ This next section does the real work.  You must know the names of
    ‘ all your subform objects and you must set the LEFT, TOP, WIDTH and
    ‘ HEIGHT properties of each.  Finally, you need to call the
    ‘ ResizeControls() method of each of these subforms, so that they can resize
    ‘ their subforms … if they have any.  (For consistency, and simplicity, I
    ‘ make sure every form and subform has this public function, even if it doesn’t
    ‘ actually do anything.  That way it never fails when this call is made.

    ‘ NOTE:  The positioning is simple math.  You’ll have to work out the details
    ‘        for your application in a way that’s pleasing to you.  The following
    ‘        provides a working template of how it might be accomplished.

    ‘ Position objects and call resize functions
    Me!objEmployee.Left = cGap
    Me!objEmployee.Top = cGap
    Me!objEmployee.Width = lngWidthLeft
    Me!objEmployee.Height = lngHeight
    g_lngResult = Me!objEmployee.Form.ResizeControls(lngWidthLeft, lngHeight)

    Me!objCustomer.Left = cGap
    Me!objCustomer.Top = Me!objEmployee.Top + (lngHeight) + cGap
    Me!objCustomer.Width = lngWidthLeft
    Me!objCustomer.Height = lngHeight
    g_lngResult = Me!objCustomer.Form.ResizeControls(lngWidthLeft, lngHeight)

    Me!objProduct.Left = cGap + lngWidthLeft + cGap
    Me!objProduct.Top = cGap
    Me!objProduct.Width = lngWidthRight
    Me!objProduct.Height = lngHeight
    g_lngResult = Me!objProduct.Form.ResizeControls(lngWidthRight, lngHeight)

    Me!objOrders.Left = cGap + lngWidthLeft + cGap
    Me!objOrders.Top = Me!objProduct.Top + (lngHeight) + cGap
    Me!objOrders.Width = lngWidthRight
    Me!objOrders.Height = lngHeight
    g_lngResult = Me!objOrders.Form.ResizeControls(lngWidthRight, lngHeight)

Exit_Here:
    Exit Function
Err_Handler:
    MsgBox Err.Description, vbCritical
    Resume Next
End Function

As you poke around in the sample application, you’ll notice that every form has an array of header controls: lblCaption and lblDescription and sometimes hyperlink labels named New, Edit and Delete. Again, for consistency, I try to include these labels on every form, even if they are not used. (You can set the properties of an invisible label, but you’ll get an error if you try to reference a non-existent control.)

Below is the code that is called from every ResizeControls() function. It takes three arguments: The calling form (by reference), a width and an optional comma-delimited string list of control names that should be formatted as hyperlinks. See inline comments for an explanation of the code.

Public Function SetHeaderCtls(ByRef frm As Access.Form,
                                   ByVal lWidth As Long,
                                   Optional ByVal sHyperLinks As String) As Boolean
    On Error GoTo Err_Handler

    Dim lngScroll As Long
    Dim strForm As String
    Dim strControls() As String
    Dim iCtl As Integer
    Dim ctl As Control
    Dim lngStartLblPos As Long
    Dim fLblCaption As Boolean
    Dim fLblDescr As Boolean
    Dim strCaption As String
    Dim strDescr As String
    Dim strCriteria As String

    ‘ Grab the form’s name … that will be required later.
    strForm = frm.Name

    ‘ If the form has a scrollbar, then deduct that from the width passed.
    lngScroll = GetScrollbarOffset(frm, “V”)
    lWidth = lWidth - lngScroll

    ‘ //////////////////////////////////////////////////////////////////////////////
    ‘ The sHyperLinks parameter is optional.  If missing, set it to empty string
    If IsMissing(sHyperLinks) Then sHyperLinks = “”

    ‘ //////////////////////////////////////////////////////////////////////////////
    ‘ When sHyperLinks exists, process the list of hyperlink labels.
    If Trim(sHyperLinks) <> “” Then
        strControls = Split(sHyperLinks, “,”)

        ‘ Place control at the left, shifted right by one “Gap” width.
        lngStartLblPos = cGap

        ‘ Loop through all the hyperlinks, positioning them with gaps.
        For iCtl = 0 To UBound(strControls())
            Set ctl = frm.Controls(strControls(iCtl))
            ctl.Top = 50
            ctl.Left = lngStartLblPos
            ctl.Height = 210
            lngStartLblPos = lngStartLblPos + (ctl.Width + cGap)
            ctl.HyperlinkAddress = ” ”
        Next
    End If

    ‘ Determine if the form has controls named lblCaption and lblDescription and
    ‘ set the flags appropriately.  This method may be extended to handle other
    ‘ common controls that appear on multiple forms.
    ‘
    ‘ First, assume the controls are missing or don’t exist.
    fLblCaption = False
    fLblDescr = False

    ‘ If found, then set the flag to True.
    For Each ctl In frm.Controls
        If ctl.Name = “lblCaption” Then fLblCaption = True
        If ctl.Name = “lblDescription” Then fLblDescr = True
    Next

    ‘ //////////////////////////////////////////////////////////////////////////////
    ‘ Set the text for the caption and description labels based on the form name.
    ‘   (Captions and Descriptions are saved in a table named FormLookup.)
    strCriteria = “[FormName]=’” & strForm & “‘”
    strCaption = Nz(DLookup(”[CaptionText]”, “FormLookup”, strCriteria))
    strDescr = Nz(DLookup(”[DescriptionText]”, “FormLookup”, strCriteria))
    If strCaption = “” Then strCaption = ParseFormName(strForm)
    If strDescr = “” Then strDescr = “No description found for [” & strCaption & “]”

    ‘ //////////////////////////////////////////////////////////////////////////////
    ‘ Set properties for lblCaption … if it exists.
    ‘   (Note that constants are used for all color values.  This allows for quick
    ‘    and easy formatting changes by editing the list of constants.)
    If fLblCaption Then
        With frm.Controls(”lblCaption”)
            ‘ If the label is set to NOT VISIBLE, then might as well skip formatting.
            If .Visible = True Then
                .Caption = ” ” & strCaption
                ‘.Top = 0
                ‘.Left = 0
                .Width = lWidth
                .ForeColor = cCaptionForeColor
                .BackColor = cCaptionBackColor
                .BackStyle = cNormal
                .FontName = “Tahoma”
                .FontBold = True
            End If
        End With
    End If

    ‘ Set properties for lblDescription … if it exists.
    If fLblDescr Then
        With frm.Controls(”lblDescription”)
            ‘ If the label is set to NOT VISIBLE, then might as well skip formatting.
            If .Visible Then
                .Caption = ”   ” & strDescr
                .Left = 0
                .Width = lWidth
                .ForeColor = cDescripForeColor
                .BackColor = cDescripBackColor
                .BackStyle = cNormal
                .FontName = “Tahoma”
                .FontBold = True
            End If
        End With
    End If

Exit_Here:
    Exit Function
Err_Handler:
    MsgBox Err.Description, vbCritical
    Resume Next
End Function

Fun and Frustration

This object resize code works pretty well and I’m pleased with the applications where I’ve implemented it. That doesn’t mean, however, that it is without frustration. Getting things to line up and display where desired will take some tweaking. If you set one property incorrectly, the whole page will look screwy. Those who attempt to implement this will undoubtedly want to write me for assistance and I’ll be happy to help, but ultimately you are going to have to use trial and error to get your pages to display the way you want. Please check and double check the TOP, LEFT, WIDTH and HEIGHT properties before assuming the code is broken. Remember, it works in the demo, so if you have difficulty, the solution is in your implementation code.

—

by Danny Lesandrini 

• 1 Comment

“We want you to build an application in MS Access, but we don’t want it to look like Access. In fact, we want it to look like a web page … like a browser application.”

Can that be done? Judge for yourself. Below is the screen shot and here is the download with the working code for the application I’m calling Something Not Entirely Unlike Access. This application demonstrates the following browser-ish features:

• Absence of Access menus
• Obfuscation of Access icon and startup screen
• Single form (page) interface
• Automatic resizing of subforms and their controls
• Forward and Back buttons to navigate “pages”
• Start Page button to return users to their “home” page
• Built in WWW web browser page
• Hyperlinks to load new forms/subforms
• Reports display as Snapshots outside of application

If any of these topics interest you, download the demo application and give it a whirl. It wasn’t built with the intention of being ascetically pleasing, so please don’t send me feedback about how ugly it is. It’s all about the code … and there’s plenty of that in this app. More than I can realistically cover in a single article, but I’ll break out pieces for future articles as the spirit moves me. In the mean time, you can get it all, provided you’re not afraid to dig into the code.

Hide Access Stuff

The first thing you want to do is to get rid of the things that make Access look like Access. Menus are the first to go, and here’s the code that gets rid of them. I put this code on my startup form and the first thing I do is to set the form Visible property to FALSE and turn off the screen painting (DoCmd.Echo False), so that I work my magic without the user seeing the flashing. I also maximize my startup form, but that has more to do with other aspects of this application.

    Me.Visible = False
    DoCmd.Echo False
    DoCmd.Maximize

    DoCmd.ShowToolbar “Web”, acToolbarNo
    DoCmd.ShowToolbar “Menu Bar”, acToolbarNo
    DoCmd.ShowToolbar “Form View”, acToolbarNo

Menus
To turn off the menus, execute the DoCmd.ShowToolbar command with the name of the target toolbar or menu, and the parameter acToolbarNo. (acToolbarYes, as you might imagine, shows the menu.) The help file says that ShowToolbar works only on toolbars, but that isn’t my experience. To prove this to yourself, double-click on the Welcome label. I added code that executed the acToolbarYes option to show the “Menu Bar” and it indeed toggles the main menu bar visible.

Icon and Title
The next Access Stuff to remove is the application icon, application title and title bar text. It’s been so long that I’ve been using this code, I can’t say for sure who to give credit to, but more likely than not, it was from the Access Web developer’s reference site. If not, I apologize to whomever contributed this fun and useful piece of code.