The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
January 2010

What to Do When Things Slow Down: Turn Off Sub-Data Sheets

Last month I described a process to alleviate slowdowns in your Access database. This month, I'll continue the process of speeding things up by showing you a way to turn off a sometimes helpful, but potentially foot-dragging feature: Sub-data sheets.

In this Issue
  • Tip of the Month - Get the Full Path to Your Application
  • Sub-Data Sheets - The Basics
  • Sub-Data Sheets - The Downside
  • How to Turn Off Sub-Data Sheets
  • Conclusion

  • Sub-Data Sheets - The Basics

    Sub-data sheets are an interesting feature that Microsoft added to Access with the 2000 release. They essentially allow you to see related tables easily in a single table view that shows both parent and child records. This can be very efficient for reviewing data in your application. By using this feature, you can see a whole lot of information without having to build queries linking tables together.

    This feature is turned on by default, and Access will attempt to use it wherever it can in a relationship between two tables. This will happen for the records in the first and subsequent parent tables. In effect, you'll get a nested set of tables.

    When this feature is on and you open a table in data sheet view, you'll see little plus signs on the left hand side as in the table below.

    By clicking on one of the pluses you'll be able to see the child records for whichever record you've selected. And if the table below has a child table, you'll then be able to see the records from that table as well. As you can imagine, this is a very powerful feature.

    Unless you've changed the defaults, this feature will also flow over into both queries and forms. No doubt, the ability to see both parent and child records without having to build queries could save an awful lot of time.

    Sub-Data Sheets - The Downside

    The trouble with sub-data sheets, though, is that the processing that takes place to determine what to show consumes a lot of processing power. If you have a table with an associated table with many records, this feature may drag down your performance. It may be difficult to detect that it is even happening. There may be no visible indication that this processing is taking place other than your machine begins to react slowly.

    How to Turn Off Sub-Data Sheets

    Luckily, turning off sub-data sheets is relatively easy. Open the table and, in datasheet mode, click on format, select sub data sheet, then select 'remove' as in the picture below.

    This will turn off the feature for that table. If you want turn it off across the board. You have to do this for each table in turn, or run some code against all the tables in your database.


    Like many things in life, sub-data sheets are a trade- off; in this case you trading convenience for speed. You have to decide for yourself whether the hit to your system is worth the convenience. It's certainly worth experimenting to see what effect removing this feature has on the speed of your application.

    Tip of the Month - Get the Full Path to Your Application

    Periodically you may need to know the full path to your application. In Microsoft Word or Excel, this is fairly easy. You just click on File | Save as, and the information is available. In Access however, this is much tougher. If you try this, you'll be invited to save the current table, form, or report, rather than the application, and there's no way to get to the path.

    Luckily, it's not too tough to get the information if you know the trick. Hold down the control key and press the letter G. This will pop you into what's called the immediate window. From here, you can give a command and get information back.

    To get to the full application path, type "Debug.Print Application.CodeProject.FullName" omitting the quotes.

    You'll then be able to see exactly where on your hard drive or network your Access application lives.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email


    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886