'Serving the Microsoft® Access
Unbound Forms and Large Tables - Part 1
If you are a regular to my site, you know that I am a fan of unbound forms. (to read that series, click here)
For fun one day, I decided to replace my home grown program that creates word search puzzles. I initially did this in a DOS environment using Turbo Pascal version 4. Alas, Windows XP will not run it.
Not only should Access be able to handle the task, it will be simple to store puzzles in a database. My original program only stored them in text based files.
NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.
I am a lazy planner. I tend to just dive in and brute force my way to the finish line, often changing things as I go. I can do it the proper way for a client, but not myself. So, my first thought was to have a puzzle size maximum of 15 by 15, requiring 225 text boxes on the screen, 1 character wide. This allows me to place individual characters anywhere, since word search puzzles allow words in any of the 8 possible directions.
I matched these with 225 fields in a table.
The logic worked out well, asking for a word and then randomly attempting to locate that word within the 15 by 15 grid. Once all the words had been placed, another VBA routine filled any empty spaces with randomly selected letters. The 15 by 15 grid of textboxes even allowed me a simple method of designing shapes. A click on a field removed it from the array of possible locations for a letter, causing a shape to appear as all the letters were filled in.
Now I need to save the data in the table. (actually 2 tables, one for the letter grid and another for the actual words, so they could be listed below the puzzle). I wrote the save code using the technique from this article; the "for each field" method. I then tested it by clicking the Save button. And I waited. And I waited. And I waited some more. Was my code broken? Was the save working? As I pondered this, it finally said "Save completed". But why did it take so long?
I then did a series of tests, and found out that direct DAO table edit methods are very inefficient with large tables. Large meaning many fields. By direct DAO edit methods, I mean something like the following example.
or the method from this article
I created a simple series of tables, one with 200 fields, one with 100 fields, one with 50 fields, and a 4th with 10 fields. I then threw this code at each of them. (note: I left off all the Dim statements to save space)
In each case, I only did edits on 10 fields, since the smallest table had 10 fields. The results were as follows, where all data in the 2nd column represents a ratio of time taken compared to the fastest one (the 10 field table):
I looked at the results and found it hard to believe that a table with a large number of fields could take so much longer.
How do we get around this problem?
If we must stay with unbound forms, then one obvious point to be made is to avoid large numbers of fields in a single table. In my case, this is the norm, but then maybe my clients just tend to have simpler needs. In a production environment, I normally do not see tables larger than 30 or 40 fields. And the client would not normally notice the difference between a 1/4 second save, versus a 1 second save. (this edit method is normally only used where there is human interaction with the system; big batch runs normally do updates via SQL commands that process many records at a time)
If you must have more fields, then there is a method that is much more efficient. MUCH more. At least for those large tables. The drawback is that it is a bit more work to code the VBA.
What is this other method? See the next article in this series!!! (coming soon)
(article continues after sponsor spot)