|
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.
| 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.
|
| Conclusion |
|
|
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.
|
|
