Intellectual Adventure: Using MS Access to search 250 Million Records–Part 2

Guest Post by Bob Goethe

This post is a follow up for Intellectual Adventure: Using MS Access to search 250 Million Records

Downloading the Files...from now to January 21, 2016

I have zipped all the files for the 1/4 billion-record database (comprising 1.7 GB zipped, 30 GB unzipped) and made them available for download. You have my permission to do with them as you please, including modifying, and uploading them to other locations and sharing them, without limitation.

Unless you wish to include a text file attesting to how you owe all of your success in life to my example and mentoring-from-afar, you don't need to take any particular action to attribute the files to me.

My personal ISP account has some bandwidth limitations, so rather than letting you download directly from my server, I used YouSendIt/Hightail to share the files. The good news about YouSendIt is, so far as I can tell, there are no bandwidth limitations. Every Access-L member could download a copy and there is no problem.

The bad news is that the files I upload expire a week after I upload them. This means that you can download these files without restriction for the next few days, but the files will become unavailable in the afternoon of January 21.

There are 25 backend MDB files, plus a front end. You can download them all if you choose. You can store them in any folder you wish, but you must make path notations in the table called "DataFiles" so that the frontend knows where to look for its data. The data files can be, in principle, stored in any folder either locally or on a network share. They don't have to be stored in the same location. The frontend knows where to look for each file individually, based on the DataFiles table. The frontend file is called "MS_Jet Test.mdb".

If you would like to play with the database, but don't want to download *all* the files, you can choose to download one file only, called "Files21-25+FrontEnd.zip". This will give you a database of 10,000,000 records only. Again, you will want to go into the DataFiles table in the front end and remove all references to 1.mdb up to 20.mdb if you want to do this.

Files1-4.zip

https://www.hightail.com/download/ZWJYZGVURndvQUp1a3NUQw

Files5-8.zip

https://www.hightail.com/download/ZWJYZGVURnd5UkU4RmNUQw

Files9-12.zip

https://www.hightail.com/download/ZWJYZGVURnc1UjVBSXNUQw

Files13-16.zip

https://www.hightail.com/download/ZWJYZGVURncwMEhOUjhUQw

Files17-20.zip

https://www.hightail.com/download/ZWJYZGVlYStCSWNsYzhUQw

Files21-25+FrontEnd.zip

https://www.hightail.com/download/ZWJYZGVlYStFc0pvSWNUQw

Each zip file contains a collection of further zip files. This odd structure arose as a result of my experimenting with YouSendIt, to find the largest file I could successfully send while using a free account. I mixed-and-matched a bit before I decided that packages of 4 files were a good size to upload. In any case, just keep on unzipping, and eventually you will drill down to MDB files.

If you figure out other interesting things to do with, or to, this database, do share what you learned with this list.

Thanks!

Bob Goethe

Intellectual Adventure: Using MS Access to search 250 Million Records

Guest Post by

Bob Goethe

I was reading the Wikipedia article on the MS Jet Database Engine (for which MS Access is essentially a frontend) and the writer commented that there was no essential difference between SQL Server and MS Jet in terms of the total volume of data they could store, since one could split the data across multiple MDB files.

So I sought to create a database backend containing 250 million records.  I did this by generating a collection of 25 mdb files, each containing 10 million records, where each record consisted of:
- RecID
- AuthorID
- Quote

The database was set up as a collection of literary quotes.  While doing the initial populating of the database, every record had a unique RecID, an AuthorID = 1 (i.e. Shakespeare), and a Quote (in the initial populating of the db, a quote from Julius Caesar).

The screen captures below will make this easier to grasp.

Screen Captures

Now, with any database where the backend is split across multiple files, you lose the chance to have the database engine itself enforcing relational integrity.  That task becomes the programmer's job to manage via his code.

(The trendy name for a non-relational database with lots of records is to call it a NoSQL database.  "NoRel" would be more accurate, but doesn't sound as cool.)

Access was capable of producing 5,500+ new records per second...but this still meant that creating 10 million records took a half hour.  So creating 250 million records took several hours of computer time, spread across several days.

The code was designed to search through the first MDB file whose path was stored in a table.  When it reached the end of the records in that file, it cascaded to the second MDB file.  It was irrelevant whether that MDB file was on the same physical box as the first or not.

This structure would lend itself to building a server-cluster of inexpensive Network Attached Storage (NAS) devices running Linux, though in my case I wanted to keep the speed up by eliminating any Ethernet bottlenecks.  So I kept all 25 MDB files (30 GB worth) on the hard drive of a single Win7 box.

This test was run using MS Access 2003.

Results of Test

The search of 1/4 billion records took 177 seconds to complete.  This means that the Jet database was processing 1.4 million records per second, including the time it took to close its connection with one MDB file and open a connection to the next one in line.

Code that Did the Work

Private Sub cmdExecute_Click()
    On Error GoTo Err_Routine
        Dim wrkspc As Workspace
        Dim dbs As Database, vAuthorName As String, vStart As Double, vEnd As Double, vTotalRecCount As Long
        Dim rstSrc As Recordset, rst As Recordset, vAuthorID As Long, vSrc As String, rstTarget As Recordset, rstAuthor As Recordset
1      Set rstSrc = CurrentDb.OpenRecordset("DataFiles")
2      Set rstAuthor = CurrentDb.OpenRecordset("SELECT * FROM Authors WHERE Authors.RecID = " & Me!AuthorID & ";")
3      vAuthorName = rstAuthor!AuthorName
4      vAuthorID = Me!AuthorID
5      CurrentDb.Execute "Delete * from SearchResults"
6      Set rstTarget = CurrentDb.OpenRecordset("SearchResults")
7      Set wrkspc = CreateWorkspace("", "Admin", "")
8      Me.Caption = "Search start: " & Format(Now, "hh:nn:ss")
9      vStart = Timer
10     Do Until rstSrc.EOF
11       vSrc = rstSrc!BackendFile
         ' I have a field on the form to tell me which backend file is being processed this moment.
12       Me!Text10 = vSrc
         'There are no linked tables in the frontend file; I establish a connection as it is required.
13       Set dbs = wrkspc.OpenDatabase(vSrc)
14       Set rst = dbs.OpenRecordset("SELECT * FROM Quotation WHERE (((Quotation.AuthorID)=" & vAuthorID & "));")
15       If rst.RecordCount > 0 Then
16           Do Until rst.EOF
17               rstTarget.AddNew
18                   rstTarget!RecID = rst!RecID
19                   rstTarget!Author = vAuthorName
20                   rstTarget!Quote = rst!Quote
21               rstTarget.Update
22               rst.MoveNext
23           Loop
24       End If
25       If Not dbs Is Nothing Then dbs.Close: Set dbs = Nothing
26       rstSrc.MoveNext
27     Loop
28     vFinish = Timer
29     vduration = vFinish - vStart
30     Me.Caption = Me.Caption & "    " & "End at " & Format(Now, "hh:nn:ss") & "   (" & Int(vduration) & " seconds)"
31     Me.Requery
Exit_Routine:
       Exit Sub
Err_Routine:
       MsgBox "Error " & Err.Number & " on line " & Erl & ": " & Err.Description
       Resume Exit_Routine
End Sub

Private Sub cmdCountRecs_Click()
    On Error GoTo Err_Routine
        Dim wrkspc As Workspace
        Dim dbs As Database, vAuthorName As String, vStart As Double, vEnd As Double, vTotalRecCount As Long
        Dim rstSrc As Recordset, rst As Recordset, vAuthorID As Long, vSrc As String, rstTarget As Recordset, rstAuthor As Recordset
1     Set rstSrc = CurrentDb.OpenRecordset("DataFiles")
2     Set rstTarget = CurrentDb.OpenRecordset("SearchResults")
3     Set wrkspc = CreateWorkspace("", "Admin", "")
4     Do Until rstSrc.EOF
5       vSrc = rstSrc!BackendFile
6       Me!Text10 = vSrc
7       Delay (0.25)
8       Set dbs = wrkspc.OpenDatabase(vSrc)
9       Set rst = dbs.OpenRecordset("Quotation")
10      rst.MoveLast
11      vTotalRecCount = vTotalRecCount + rst.RecordCount
12      If Not dbs Is Nothing Then dbs.Close: Set dbs = Nothing
13      rstSrc.MoveNext
14    Loop
15    Me!Text10 = Format(vTotalRecCount, "###,###,###") & " records searched."
Exit_Routine:
   Exit Sub
Err_Routine:
   MsgBox "Error " & Err.Number & " on line " & Erl & ": " & Err.Description
   Resume Exit_Routine
End Sub

Things to Watch Out For

My first effort involved storing 15 million records per MDB file...but I found that once files grew to 1.5-ish GB each, unpredictable behavior sometimes occurred.  E.g. compact the file, and suddenly RecID is no longer the primary key.  Try and re-designate it as primary key, and you get a message that there is insufficient space in the file to complete the request.

So while the specs on Jet databases say that MDB files of up to 2 GB are supported, my experience is that it is better to treat ~1 GB as a good, working limit.

Next Step

I have never been able to compare the *performance* of an Access database directly with a SQL Server database, since you almost never have the SAME data set hosted on the SAME equipment, and accessed from the SAME frontend.

My plan is to set up this test on a SQL Server box, and then run the two database engines head to head.

My expectation is that SQL Server will perform better, but cost a good bit more than the Access solution.  A cluster of multiple Windows Server Enterprise Edition machines running SQLS could cost well over $100K in hardware and licensing fees of various sorts.

The MS Jet solution, on the other hand, costs $130 for a retail copy of MS Access, a bank of NASes for less than $2000, and users can access the database with no need for additional licensing fees. All they need to do is download, at no charge, the Microsoft Access Runtime application.

What Does it All Mean?

Microsoft's problem - and it is a big one, from their own point of view - is that Access is simply too good, too powerful, too capable.

A good bit of Microsoft's marketing says that Access is just fine for homemakers to utilize in keeping track of their inventory of spices, and then printing out a shopping list to take to the Safeway store. But if you want to run a business, then you want a man's database. You want SQL Server.

Not every company has drunk the Cool Aide of Microsoft's SQL marketing campaign (though probably most 25 year old computer science graduates have). Enterprises the size of Alberta Covenant Health ($1B revenue/year) and even Alberta Health Services ($10B revenue/year) use MS Access databases in mission critical applications (much to the chagrin of some of their own SQL database admins).

If Access has a poor reputation among professional programmers, it is most likely because - since Access is included with MS Office - it is the database tool most often used by doctors or accountants who fancy themselves as computer savants. Of course, the applications created by these kinds of people are often total P.O.S., with no concept of relational data integrity or efficient data management. So sometimes a professional is called in to "tidy up the database" only to find that he has a real mess on his hands.  In this situation, it will be pretty easy for him to come away with a negative perception of Access.

But clearly, in the hands of a competent programmer, MS Access is good for more than just organizing the spice rack in your kitchen.

They say that if your only tool is a hammer, every problem looks like a nail. There are certainly problems that *require* SQLS. But the database professional who adds MS Access to his toolkit expands his resources for efficient problem solving. The possibilities are worth pondering.

Sample files are available for a limited time. See this post for details:
Intellectual Adventure- Using MS Access to search 250 Million Records–Part 2

Bob Goethe

Bob is working for the Covenant Health Group of hospitals in Alberta, right now developing a seriously large document management-and-retrieval database.  Because of some of the politics that swirl around government healthcare in Canada, Covenant's management would like to have this project built in MS Access rather than SQL Server.  This sample database was built to test concepts that could be used in the operational document-management database.

Optimizing Continuous Form Size: Part 6 (conclusion)

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form (this post)

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

VI. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

The purpose of this section is to provide information so that a person familiar with Access can incorporate this functionality into another Continuous Form.

The code used for Continuous Form sizing and positioning is contained within two modules, four small forms, and code within each Continuous Form. A sizable portion of the code copied from the sample form should be used without modification. A small portion of the code can be tailored. In this part of the document, each section of code is displayed and clearly marked.

To incorporate the sizing and positioning functionality into another Continuous Form, perform these steps:

A) Make sure you have these references to code libraries (or similar references for your version of Access):

B) Import the two sample database modules (make no changes):

- FormMetrics is a class module that provides various form measurements.

- modGlobalsAndMaxRows is a standard module containing global variables and the function "FnMaxRowsOnForm," which accepts the name of a form and its border style and returns the maximum number of rows that will fit in the form.

C) Import the four small sample database forms used for calculating border size (make no changes):

• - frmBorderStyle0None
• - frmBorderStyle1Thin
• - frmBorderStyle2Sizable
• - frmBorderStyle3Dialog

D) Import form "frmSampleListing3" from the sample database for convenience in copying its sizing and positioning code. This form can be deleted when finished.

E) Open "frmSampleListing3" in Design mode and click on the Code button to expose the code behind the form. Copy all code from the beginning down through the "End Sub" statement of the "Private Sub Form_Current()" event. Copying the remainder of the code is optional ("Sub Form_Close" and "Sub cmdCloseForm_Click").

Three Subs are required:

Private Sub Form_Open

Sub SetVerticalFormSize

Private Sub Form_Current

Insert the copied code into the code module of your Continuous Form. If you are starting with a new Continuous Form, it might be easier to copy all of the code from "frmSampleListing3" into the new form, then modify and delete code as needed.

F) Customize the copied code if desired, following these customization notes.

Notes about customization

Placement of the sizing and positioning code (the code in the Open event of the sample form) can be in either the Open or Load event. As far as I can determine, it's pretty much your choice. Upon opening a form, the Open event fires first, but since these are the first two events to fire, it seems not to make any difference where the code lies. If you insert the copied code into the Load event, change "Form_Open" to "Form_Load".

The other two Subs ("Sub SetVerticalFormSize" and "Private Sub Form_Current") need to be inserted as separate subs, apart from the Open event code.

Following are several snippets of VBA code taken from the code in the sample form. Each is explained as to its purpose and whether it is customizable.

These lines of code at the top of the Open event should remain without change. Of course, the references to "Open" could be "Load".

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

These "Dim" statements are used in determining the number of records to be displayed and in setting the form size, and are customizable.

-----------------------------------------------

-----------------------------------------------

This statement (optional):

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

in the Open event of each form limits Access to one form at a time visible on the screen. If the above statement is removed, also remove all the OnClose event code; it unhides (makes visible) the previous form (OnClose event code shown here):

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

The next section of code, beginning with the comment "Begin Form Metrics Code" and ending with the comment "End Form Metrics Code", is the heart of the Continuous Form sizing logic and should be used without alteration, except possibly the percentage of unused vertical space allocated above the form. It can be changed if desired ("Const sglPctSpAboveForm").

--------------------------------------------------------------------------------------------------------------------

 
(Click to enlarge)

(Click to enlarge)

---------------------------------------------------------------------------------------------------------------------

The next section of code obtains the number of data rows to be displayed and calls a subroutine to set the vertical size of the form based on that number. This code is customizable.

---------------------------------------------------------------------------------------------------------------------

(Click to enlarge)

---------------------------------------------------------------------------------------------------------------------

Since the form has been resized above, the next set of code acquires the current form measurements and positions the form vertically within the Access window. This code should be used without alteration.

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

The remainder of the code in the Open event of the sample form is comprised of common statements to close the recordset, return memory to the system and exit. The "Stop" and "Resume 0" statements provide flexibility in debugging. If an error occurs, an error message will be generated for the user, then the code will stop. The F8 key can be used to step the code to the "Resume 0" statement, which will return to the specific statement which caused the error—sometimes very helpful.

---------------------------------------------------------------------------------------------------------------------

The following "SetVerticalFormSize" subroutine is the code described on page 6.

---------------------------------------------------------------------------------------------------------------------

(Click to enlarge)

There is an anomaly in the code for which no explanation has been found. Even though there is no useful code in the OnCurrent event, the existence of the event skeleton seems to be required for error-free operation.

---------------------------------------------------------------------------------------------------------------------

The remainder of the code in each of the sample forms is comprised of normal event code for closing the form.

After you finish inserting and customizing the code, click on the "Save" button, then click on the "Debug" button in the top menu bar and click on the "Compile" option in the top line of the dropdown menu. Any errors will be called to you attention. Correct any errors and repeat the Save and Compile sequence until no more errors are noted. Then close the code window and open your Continuous Form, observing whether it accomplishes what you expected.

Best wishes for Continuous Form vertical optimization.

You can find this sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

If you have any comments or corrections concerning this document or the sample database, please direct them to the author. The name and email address are near the top of the first page.

Optimizing Continuous Form Size: Part 5

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration (this post)
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

V. Sample Access Database Demonstration

The sample Microsoft Access database associated with this document is named "Optimizing Continuous Form Size.mdb". It contains sample forms that can be opened/perused for demonstration of the concepts described in the previous sections of this document.

The sample database contains:

• 2 Tables:
  • - tblBooks
  • - tblBooksFew
• 8 Forms:
  • - frmBorderStyle0None
  • - frmBorderStyle1Thin
  • - frmBorderStyle2Sizable
  • - frmBorderStyle3Dialog
  • - frmSampleListing0
  • - frmSampleListing1
  • - frmSampleListing2
  • - frmSampleListing3
• 2 Modules.
  • - FormMetrics
  • - modGlobalsAndMaxRows

A "tour" of the Listing Forms will demonstrate the concepts and advantages of optimizing the vertical size of Continuous Forms.

Open database "Optimizing Continuous Form Size".

Open form "frmSampleListing0" in Design mode. While this form is a Continuous Form, it is a classic version, not having any vertical sizing and positioning logic. Notice the tiny vertical size with no detail section rows visible.

Close the form.

Open form "frmSampleListing0" in production mode (with the Open button or by double clicking on the name of the form). It opens to a small size but Access forces about 3 rows of data--even with no detail section rows showing initially in Design mode. You can see the rest of the data by using the vertical slide bar on the right of the form.

Close the form using the "Exit" button on the form.

Open "frmSampleListing0" again in Design mode and drag the lower border of the form down to about the 3-inch mark as shown by the scale on the left side of the form.

Click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form.

Open form "frmSampleListing0" in production mode. Notice that the form is approximately the size set in Design mode.

Close the form using the "Exit" button.

Open form "frmSampleListing0" again in Design mode and move the top of the form to the top of the Access window. Then drag the lower border of the form down as far as possible.

Click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form.

Open form "frmSampleListing0" in production mode. Notice that the form itself nearly fills the Access window top to bottom, but there may be a blank space in the form below the rows of data (on larger screens and screens with higher resolution), demonstrating that on its own, Access does not fit the size of a Continuous Form to the number of rows of data to be displayed.

Close the form using the "Exit" button.

It's strictly optional, but if you wish, you can open the form in Design mode and reset its size to what it was originally--very tiny--so others can follow the same tour, then Click File | Save | File | Close to save the form to its original size.

The next time the form is opened in Design mode, it will appear in the Access window in the same location it was when it was saved and closed.

The remainder of the tour of the sample database will use the other sample Listing Forms (1, 2, and 3), all of which include the same sizing and positioning logic.

frmSampleListing1

Open form "frmSampleListing1" in Design mode. Notice it is very long vertically.

Close the form.

Open form "frmSampleListing1" in production mode (with the Open button or by double clicking on the name of the form). It opens with 30 records displayed. In spite of its large vertical size in Design mode, the size of the form in production mode just fits the number of rows of data—a demonstration of optimal vertical sizing by VBA code. You can verify this by dragging the bottom border of the form down a bit to see that there is no data hidden. And the last row is not slightly pinched; the bottom border of the record selector box on the left is visible.

Close the form using the "Exit" button on the form.

frmSampleListing2

Open form "frmSampleListing2" in Design mode. Notice it is very small vertically, similar to frmSampleListing0.

Close the form.

Open form "frmSampleListing2" in production mode (with the Open button or by double clicking on the name of the form).

As opposed to frmSampleListing0, which opened with 3 records, this form is dynamically sized to fit all 10 records, all the data available. As with the previous form, you can verify this by dragging the bottom border of the form down a bit to see that there is no data hidden. Again the bottom border of the record selector box on the left is visible.

Close the form using the "Exit" button.

frmSampleListing3

Open form "frmSampleListing3" in Design mode. It is small but not tiny. The 1-inch mark should be visible in the scale on the lower left of the form.

Close the form.

Open form "frmSampleListing3" in production mode (with the Open button or by double clicking on the name of the form). It nearly fills the Access window top to bottom, such that there is not enough spare space to include an additional row of data—demonstrating the use of vertical size optimizing code. You can verify there are more records than can be displayed on the screen by noting that there is a vertical slide bar on the right of the form and the Navigation Button field at the bottom of the form shows "of 116", indicating there are 116 records available for this form.

Close the form using the "Exit" button on the form.

This ends the tour through the sample database, demonstrating Continuous Form vertical size optimization.

Next time: Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

Optimizing Continuous Form Size: Part 4

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning (this post)
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

IV. Vertical Positioning

After determining the number of rows of data to be displayed (maximum or otherwise) and specifying the vertical size of the Continuous Form, consideration can be given to the vertical positioning of the form within the Access window. Horizontal positioning is not a consideration since it does not affect vertical sizing or positioning.

The specifics of satisfactory vertical positioning are pretty much individual preferences. Although the metrics from the form make it possible to vertically center the form within the Access window, vertical centering (particularly with small forms) does not seem to lend itself to comfortable viewing by the user. Positioning in the upper part of the Access window is generally preferable to positioning it lower.

The vertical positioning logic used in the sample forms favors the upper part of the window (30% of unused Access window space above the form), but can easily be changed. This amount was determined after testing, but is not based on any scientific value. Perhaps the suggestion here will spur your thinking toward something better.

The simple vertical positioning logic used in the sample forms is spelled out here in pseudo code:

Const sglPctSpAboveForm as Single = 0.30

'Since the form has been resized, get the form's current 'measurements in Twips, including the overall FormHeight.

Call mfi.GetSize ...

' Position form vertically in the Access window.

DoCmd.MoveSize , (WindowHeight - FormHeight) * sglPctSpAboveForm

The MoveSize calculation results in the integer number of twips for positioning the top of the form down from the top of the Access window.

A suggestion on moving the form:

There is probably something the author is not familiar with, but it seems that Windows 7 (don't know about XP) complains about moving a form unless it already contains records. You might want to try something different, but it appears to be necessary to use a "Requery" statement somewhere prior to using the MoveSize statement.

Next time: Sample Access Database Demonstration

Optimizing Continuous Form Size: Part 3

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations (this post)
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

III. Vertical Sizing by Calculations

To dynamically adjust the vertical size of a Continuous Form to the number of records to be displayed (without empty space below the records), a programmed method can be used. In addition to adjusting the vertical size of the form, vertical placement within the Access window can be set by program code also.

This section explains how Access VBA (Visual Basic for Applications) code can be used to automatically calculate the optimum vertical size for a Continuous Form. The sample Access database accompanying this document has forms and code for demonstration. Topic V of this document (Sample Access Database Demonstration) describes how to tour the demonstration.

To complete this task, we need to know the vertical size of the Access window (sometimes called the "Form Client") and the vertical size of each of the elements of a Continuous Form that contributes to vertical size.

The vertical size of a Continuous Form is comprised of these individual elements:

• Border (both top and bottom border combined as one value)
• Caption Bar (sometimes called "Title Bar")
• Form Header
• Form Detail
• Form Footer
• Navigation Buttons

If we have the metrics (measurements) for the Access window and each of the elements above in twips (1440 twips/inch), we can calculate the maximum integer number of rows of a Continuous Form that will fit within the Access window on the particular screen being used.

Comparison of the maximum number of rows that can fit in the Access window with the number of rows of data to be displayed, can provide the basis for specifying the actual size and position of the form.

To get the vertical size of some of the form elements, form metrics code is incorporated from the Access 2000 Developer's Handbook, Volume I by Getz, Litwin, and Gilbert (Sybex), Copyright 1999, which was published Dec. 17, 1999, on a web site called "Office VBA", and with subsequent articles by Getz stating that the Developer's Handbook code needed to be supplemented with code he then provided. The code has been modified to fit the current application.

We start with a description of how each of the measurements can be obtained.

Access Window Height, Caption (Title) Bar Height

Code from Getz, et al, provides these.

Form Header, Form Detail, Form Footer

These values are readily available from Continuous Form properties:

• FormHeader.Height
• Detail.Height
• FormFooter.Height.

Navigation Buttons

The NavigationButtons property of a form can be tested (True/False) for the existence of Navigation Buttons. If they are present, their height can be determined by saving the value of the form's InsideHeight property, setting the NavigationButtons property to False and again saving the value of the InsideHeight property. The difference is the height of the Navigation Buttons.

On some forms this difference may occasionally be reported from Access as a value near 15 twips, when it should be around 250 twips. This incongruity–cause unknown–is handled reasonably well by an approximation. If the difference is reported as less than 216 twips, the value is set arbitrarily to 216, equivalent to 0.15 inches, which is close to being correct.

Border

The last measurement to be considered is Border. It takes more effort to obtain this value since there is no property to provide it. We can use the difference between the measured overall form height for a form with no borders and no Caption Bar (BorderStyle = 0) and the measured overall form height for the same size form having the border style of the Continuous Form, less the height of the Caption (Title) Bar.

A method which can be used to determine the height of the borders involves using four very small forms (2 inches by 2 inches in the sample database) that are identical except for identification, border style, and the name of the global variable to which each form height is saved. Upon opening, each of these small forms obtains its overall form height, saves that into its individual global variable, then closes itself.

In the Open event of Listing Forms 1, 2, and 3, in the sample database, the small form with BorderStyle0 (None) is opened to capture the overall form size for border style 0. The small form having the same border style as the Continuous Form is then opened to capture its overall form size. The calculated difference minus the height of the Caption (Title) Bar is the size of the two (top and bottom) borders taken together.

For border styles 1 and 3 (Thin and Dialog), the composite border size is a variation of the following equation for the Sizable border, style 2 (all values in twips):

CompositeBorderHeight = FormHeightBorderStyle2 - FormHeightBorderStyle0 - CaptionBarHeight.

Having determined the values for the height of the Access window and each of the six elements of the form, we can now determine the total vertical space available for rows of data in the form's Detail section. Dividing that total space available by the height of the Detail section gives the maximum number of rows of data that can be displayed on the form in the Access window. Here is the equation (all values in twips):

MaxRowsOnForm = (AccessWindowHeight - CaptionBarHeight - FormHeaderHeight - FormFooterHeight - NavButtonsHeight - BorderHeight) \ FormDetailHeight

Notice the backslash ("\") preceding FormDetailHeight. That forces an integer quotient.

Maximum Number of Rows possible versus Actual Number of Rows of data

After calculating the maximum number of rows that will fit in the Access window, there must be consideration of the actual number of rows of data to be displayed.

The actual number of rows of data available can be obtained from opening a recordset using the same SQL statement as the RecordSource property of the Continuous Form and capturing the RecordCount property, as demonstrated in the code for sample Listing Forms 1, 2, and 3.

The maximum number of rows that can be displayed is compared with the actual number of rows of data available and the following logic is applied:

If all rows of data will fit in the Access window (actual less than or equal to the maximum), the vertical size of the form is specified to accommodate the actual number of rows.

If the number of rows of data available is greater than the maximum that will fit in the Access window, the number of rows for the form is specified as the maximum. There may be a small space left below (and possibly above) the form, but there should not be enough extra space to display an additional row of data. In form "frmSampleListing3" in the sample database, the maximum number of rows displayed can be seen in the Navigation Buttons area by clicking the record selector on the last record visible when the form is open.

In Listing Forms 1, 2, and 3, in the sample database, the number of rows of data to be displayed is calculated and placed into a variable named " intNRows". Then a subroutine is called to set the vertical form size (maximum or otherwise). Here is the call statement:

SetVerticalFormSize Me, intNRows

The use of "Me" as a parameter provides to the subroutine the name of the Continuous Form to be sized.

The "SetVerticalFormSize" subroutine sets the vertical size of the Continuous Form by calculating the form's InsideHeight property using this equation contributed by Lambert Heenan:

InsideHeight = FormHeader.Height + (Detail.Height * intNRows) + FormFooter.Height

(where "intNRows" is the number of rows to be displayed--either the maximum number permitted by the height of the Access window or the number allowing display of all data without blank space in the lower portion of the form.)

Next time: Vertical Positioning

Optimizing Continuous Form Size: Part 2

by Earl Brightup earlreb@charter.net

This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.

Topics:

1. Introduction - Continuous Forms
2. Vertical Sizing by Hand Adjustment (this post)
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html

II. Vertical Sizing by Hand Adjustment

In case the reader is not familiar with hand-adjusting the vertical size of a Continuous Form, here is a procedure for it. This method will cause the form to open in production mode to a fixed vertical size, the same size every time regardless of the number of rows of data.

In Design view, drag the bottom of the form down to the size you would like to see it when it opens in production mode. Then click File | Save | File | Close. The final File | Close can be replaced with a close via the big X in the upper right corner of the form. When the form opens in production mode, it will open to your preset size. With records displayed, you can see how close to your desired size the form is set. If not satisfactory, the form can be opened again in Design view and the procedure repeated.

The inconvenience of this method is that unless you are prescient, the size of the Detail section may not quite display a full integer number of records or you may have oversized it and have empty space. Hand-adjustment is just a way to get close to a desired vertical size.

Next time: Vertical Sizing by Calculations

Optimizing Continuous Form Size: Part 1

by Earl Brightup earlreb@charter.net
This six-part series describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution.
Topics:

1. Introduction - Continuous Forms (this post)
2. Vertical Sizing by Hand Adjustment
3. Vertical Sizing by Calculations
4. Vertical Positioning
5. Sample Access Database Demonstration
6. Adapting Dynamic Sizing and Positioning Code to Your Continuous Form

You can find a sample with complete documentation here: http://www.rogersaccesslibrary.com/forum/topic600_post618.html
I. Introduction - Continuous Forms
A "Continuous Form" is one of the form types that can be generated by Microsoft Access. It can display a variable number of rows of data with a fixed number of columns. This type of form is often used for displaying a list of items with related information such as Item Number, Item Description, Item Price, etc. Data fields are displayed in a format similar to a spreadsheet table.
This document describes a method of dynamically optimizing the vertical size of a Continuous Form to fit the number of rows of data to be displayed—on any screen size and resolution. For continuous forms that do not fill the Access window top to bottom, a method of vertically positioning them is suggested.
Dynamically adjusting the size of continuous forms can be valuable, especially in Access applications that will be distributed to a number of locations having different screen sizes and different screen resolutions.
The sample Access database to which this document refers ("Optimizing Continuous Form Size.mdb") was developed using Access 2003 in Access 2000 file format, and has been tested successfully under Access 2000, 2003, and 2007. A bit of the code has run under Access 2010.
The following screen shot is a simple Continuous Form displaying a couple of fields from a table containing a list of books:

The number of rows of data displayed depends on the internal space defined for the form. Without adjustment, Access will display nearly a full integer number of rows, but does not quite do justice to the last row of data unless specifically set by the user. Left to chance or estimation, the last row may be just slightly "pinched" at the bottom. The bottom border of the last record selector box is not visible (not a serious problem). This screen shot shows an example:

The vertical size of the form can be hand-adjusted in Design view (usually with repeated adjustment and testing) to better fit an estimated number of rows of data--and that size will remain fixed when the form is opened in production mode.
If the "AutoCenter" property is set to "Yes", Access will attempt to center the form both vertically and horizontally in production mode, but the vertical size of the form itself remains fixed at the size set by the user in Design mode.
If the user attempts to maximize the form size for the Access window, hand-adjustment may leave the form too long, resulting in the last visible row of data partially showing, like this:

If the number of rows of data to be displayed does not fill up the specified size of the form, there will be a blank area between the last row of data and the bottom of the form. The next screen shot is an example of this case:

Without some method of dynamically adjusting the vertical size of the Continuous Form for the number of rows of data to be displayed, we must depend on hand-adjustment to a fixed size with trial and error and perhaps the occasional blank area.
Next time: Vertical Sizing by Hand Adjustment