MSAccess: Load Subforms Dynamically
April 20, 2006
I frequently see comments and questions on the newsgroups about how to improve performance with Microsoft Access applications. The knee-jerk reaction is to blame the platform, but in most cases, that's not the problem. There are many things a developer can do to squeeze an incredible amount of power and performance out of an Access database. The first, and foremost, is to avoid loading data until you need it. One way to do this is through the smart implementation of subforms.
Subforms are one of the great features of Microsoft Access. If you have ever tried to replicate the Form/Subform paradigm of Access in Visual Basic, you will appreciate how simple the process really is.
1. Create the master form
2. Create the child form
3. Drag child form and drop on master form
4. Set the Link Master Field and Link Child Field properties
5. Get a cup of coffee; you're done!
The down side is that it is too simple to add subforms. Once a developer gets started creating subforms, they often don't know when to quit. Before long, they have a dozen subforms pointing to a dozen related tables referencing thousands of related records. Is it any wonder performance suffers?
So, how do you get around this performance hit? Simple; don't load any data until the user actually asks to see it. That is what this month's article is all about and as usual, the code is available in the download file.
The image below tells the whole story. My form has a tab control with four pages, one for each of the tables referenced. The first page has fields of the master form embedded in it while the other three pages will reference its corresponding subform. However, as you can see from the design-view image of the form below, there aren't any subforms embedded on these tab-pages. There is one unbound Subform object floating around in the upper right-hand corner where it appears out of place. Through code, it is dynamically resized, moved and loaded with the correct subform, depending on which page is selected. Let's take a look at the code that manipulates this simple control.
Dynamically Load Subform on Page Change
The code for this process is very, very simple. If there is a trick, it is knowing which event receives the code. When I was preparing the code for this article, I instinctively selected the Click() event instead of the Change() event. Needless to say, it did not behave as I had intended. Once I corrected that issue, things proceeded pretty smoothly. Check out the code, and keep in mind this is all the code in the entire application.
Private Sub tabPublisher_Change() On Error Resume Next Dim strSubformName As String ' Based on the page selected, set the Subform Name variable Select Case Me.tabPublisher Case 0: strSubformName = "" Case 1: strSubformName = "sfrmAdditionalInfo" Case 2: strSubformName = "sfrmEmployeeInfo" Case 3: strSubformName = "sfrmPublisherTitles" Case Else: strSubformName = "" End Select ' If a subform exists, then set the properties, otherwise ' just hide the generic subform object If strSubformName = "" Then Me.objSubform.Visible = False Else Me.objSubform.SourceObject = strSubformName Me.objSubform.LinkMasterFields = "pub_id" Me.objSubform.LinkChildFields = "pub_id" Me.objSubform.Top = Me.lblAnchor.Top Me.objSubform.Left = Me.lblAnchor.Left Me.objSubform.Width = 0.95 * Me.tabPublisher.Width Me.objSubform.Height = 0.75 * Me.tabPublisher.Height Me.objSubform.Visible = True End If End Sub
Notice the tight code method used in the Select Case statement. This syntax makes sense in our application since each case performs a simple variable assignment and the code is clean and easily readable. I could have combined the Select statement with the code in the If branch, but that would have resulted in repetition of the subform property assignments. However, if your subforms need to be sized or placed differently, that is exactly what you would do.
Of special interest is the pair of properties that set the LinkMasterFields and LinkChildFields. All of my subforms share the same common field, pub_id. In most cases, the Primary Key of the master form is likely going to be the one on which the subforms are linked, but there may be times the subform properties are different. In that case, you may set the link properties dynamically.
By implementing this paradigm, the number of table connections was cut from guaranteed four references to either one when page p00 is selected, or two when one of the other pages is active. While this performance gain is not likely noticeable with this little demo application, it is very appreciable where there are tens of thousands of records and ten or more subforms.
This trick alone was enough to completely solve performance issues for one of my clients. After months of "why is this application so slow?" complaints, and hours of pouring over server settings, the basic issue described above of loading data only when it's requested solved my problems and made me a hero.