Skip Navigation LinksHome > SSW Standards > Rules > SSW Rules to Better Access Applications

Do you agree with them all? Are we missing some? Let us know what you think.

Rules to Better Access Applications
  1. Is your Access application split into two databases?
  2. Do you always check the validity of linked tables?
  3. Do you always check bindings are OK?
  4. Do you only select the records you need?
  5. Do you fill combo boxes and listboxes only when you need them?
  6. Do you use tools to ensure database integrity?
  7. Do you decompile your code?
  8. Do you know the feature of decompile switch?
  9. Do you use Linked tables instead of ADP when using SQL Server as a backend?

  1. Is your Access application split into two databases?

    Most Access developers split their Access application into two files the application database and the data database. The application database (the forms, reports, saved queries and programming) is installed on the local hard disk, and the data file is stored on the network drive for access by multiple users over the network.

    This is useful for when you want to share your Access data across the network. Read our guide on how to link your Access database to an Access or SQL Server backend.
     

  2. Do you always check the validity of linked tables?

    Linking a table from an external database allows you to read data, update and add data (in most cases), and create queries that use the table in the same way you would with a table native to the database. With the Microsoft Jet database engine, you can create links to tables in Access databases, as well as other data formats supported by Microsoft Jet's installable ISAM drivers (Excel, dBase, Paradox, Exchange/Outlook, Lotus WKS, Text, and HTML) and ODBC drivers.

    It is essential that all linked tables in the front end application are in sync with a corresponding back end database.
    Reasons for validating linked tables are:

    • In the front end application linked tables seem to get corrupted occasionally after compression of back end tables
    • A shared drive containing the back end database was renamed
       
    We have a program called SSW Performance PRO! to check for this rule.

  3. Do you always check bindings are OK?

    It is essential that if any of your forms and controls have their RecordSource or ControlSource set to a table (as is likely the case), you must ensure that linkages to the table are not broken. The last thing you want is for a user to open a form, only to find a whole lot of errors because the underlying fields or tables have had their name changed or been removed.

    In databases (particularly ones with a lot of tables) it is difficult to keep bindings in sync. The manual way to do this is to compare a list of the names of the tables in your backend database with the form record sources in your front-end and reconcile. This can be very time-consuming, which is why we use a program called SSW Performance PRO! to automatically scan your front-end for any broken table bindings.

    Figure: If a field or table is renamed or deleted, the user will only get an error when they open the form

    Figure: SSW Performance PRO! automatically scans your front-end for broken bindings and produces an easy-to-follow report

     
    We have a program called SSW Performance PRO! to check for this rule.

  4. Do you only select the records you need?

    Access developers commonly make the mistake of retrieving all the records from a table when opening a form, that is, setting the forms Record Source to a whole table or query. When the number of records increases to the thousands, selecting all records for a form can cause major performance issues such as sluggish forms and network lag.

    In most cases, your users do not need every single record from a table or query (for example, they only need to view 1 contact record at a time). To get the best performance out of your application, implement a record search form to select only the record/s that the user wants to view. For more information on how to implement this, see the Microsoft whitepaper, How to Migrate from Access to SQL Server under Step 6: (Optional) Fix the Performance of Very Slow Forms in Part C.

     
    We have a program called SSW Performance PRO! to check for this rule.

  5. Do you fill combo boxes and listboxes only when you need them?

    Access developers make the common mistake of retrieving all records for dropdown lists on a form, that is, setting a controls Control Source to an entire table or query column. Because every single value is retrieved on form load, the form's loading time can become very slow, especially if there is more than one combo box on the form or if the data is being retrieved over the network from a SQL Server database.

    The solution is to populate the drop-down lists when the user activates them.

    Tip: Use an event procedure or a button to set the row source for the drop-down list. For example, Me!myDrop List.Row Source = Q where Q is, once again, either the name of a query or an SQL string.

    Tip: Drop-down lists themselves will be more responsive if they return fewer records. Try cascading criteria so that successive lists are limited by the selection in a previous list. The row source query for a list could depend on the item selected in a previous list as in this example: Q = " SELECT Field1, Field2 FROM Table1 WHERE Field3 = " & Me!DropList1.Value

    For more information on this topic, see the Microsoft whitepaper, How to Migrate from Access to SQL Server under Step 6: (Optional) Fix the Performance of Very Slow Forms in Part C.
     

  6. Do you use automated tools to ensure database integrity?

    It is nearly impossible to manually check complete consistency across your Access database - are all relationships valid? Are there any corrupt tables? We use FMS Total Access Analyzer to conduct a deep scan of our databases to ensure that all data is clean and everything is in working order.

    Read this guide on using FMS Total Access Analyzer to detect and correct errors, and document your database objects.
     

  7. Do you decompile your code?

    Decompiling your code can significantly reduce the size of your Access file and correct some corruption.

    Access stores code as both source and compiled code streams. Sometimes some of the compiled code streams become corrupt and need to be flushed. Running Access in decompile mode causes all the compiled code streams to be marked as invalid and therefore removed.

    Since the compiled code streams are removed, the database size is also significantly reduced when the database is compacted.

    Note: the decompile option is an undocumented feature of MsAccess.
    To run access in decompile mode, use the command line "MSACCESS.EXE /decompile"

  8. Do you know the feature of decompile switch?

    Michael wrote:

    I saw you at the Microsoft Seminar earlier this month in Perth, which was very useful thanks. If you have time I do have a question.

    You probably won't remember but I asked you in the break about copying and pasting reports and forms in Access 97 causing Access to perform an illegal operation and 'hang' next time you opened it. Your reply was to use the "/decompile" switch in the command line. Not only has this worked extremely well but it reduced the file size from 8Mb (a lot of code in this database) to 4Mb.

    However, I can find no information at all anywhere about decompile either in the Access Help, Knowledge Bases or anywhere on the Microsoft web site.   Maybe its there, but I can't find it. 

    My concern is that the message you get after decompiling is something like "Access has upgraded your code to the version of Visual Basic on your system", and I'm worried that if I distribute my database to end users (either 'as is' or as a setup with runtime Access) that if they don't have MS Office SR1 and SR2 or the Jet SR pack or whatever  else I might knowingly or inadvertently have on my computer, will that cause  a problem? In other words, what does "the version of Visual Basic on your system" really mean and is there any danger to using this apparently undocumented decompile feature, particularly regarding distribution of the end result?  

     If you have a minute to answer or point to a resource (Access  97 Developers Handbook, Litwin/Getz/Gilbert doesn't appear to mention it either) I would be very grateful. 

    Thank you.

    Michael.

    Adam Answered:

    This is an undocumented feature. Here is real deal on the /Decompile switch (Originally posted 5/22/99 by Michael Kaplan) This post is to give a little historical background and info on this undocumented command line switch in msaccess.exe. 

    To use it, you simply run:  

     msaccess /decompile <your database name>                             

    and that's all there is to it. But what exactly does it do?????

    VBA AND THE 11 STATES OF COMPILATION

    That's right, internally there are 11 different compilation levels between decompiled and fully compiled like you would find in an MDE. As you make objects dirty, you will decompile the project, but dirtying Module1 does not remove all the "compiled" info about Module2 or Form1, for example. The exact levels are impossible to even detect unless you have source and debugging symbols for VBA, and insanely difficult even then.... so lets just leave it as read that the yes/no question of "is it compiled?" has many subcategories under the NO answer that essentially mean its not compiled but some parts of it are kind of.

    P-CODE VERSUS CANONICAL TEXT

    Your code is stored in two forms, each one of which is a Stream object in the project's storage(s). One form is the canonical text that you look at and edit and such, the other is the compiled version of the code that runs.

    VBA must always compile before it runs, so in an app that runs you will always find p-code. And unless you are running in an MDE (where the canonical code is stripped out) you will always have the canonical text too.  Any time VBA thinks that the compiled code is invalid (such as when you make a change or the binary format changes, which is so far only during beta cycles), it will "decompile" the module and then compile it again from the canonical text.

    ACCESS BETAS: BINARY FORMATS, ETC.

    People who were on the betas for Access 95, 97, and/or 2000 will remember the binary format issues. From build to build, changes would be made in VBA or in the Access OM which would make old compiled code invalid. Usually a crash is the best you could expect. To help fix this, some work was done to have a global way to decompile *ALL* code that is present in a project so that you do not risk having any invalid code that might crash.

    **********

    This is the reason the flag is there and the only reason. The command line switch is undocumented because there is never a binary format change except during betas and in internal builds.... so there is no reason to document something never intended to be used.

    **********

    NOW there are some positive benefits that are side effects that people have made use of:

    1) SIDE EFFECT: CORRUPTED PROJECTS

    Now, as a side effect, you have a way to deal with corrupt projects! You see, the canonical text is never what is corrupted, it is always some compiled portion of a project, like a module or most commonly the typeinfo of a form or report. By globally telling Access that the compiled portion should be thrown away, you get rid of whatever the bogus piece of code is.

    Now this kind of fix is what would have taken care of the old Access93 vba232 page faults and other problems where Access was walking off the end of a vtable and crashing, as well as a zillion other such little problems. This is what made PSS first expose this flag for.... if a project is corrupted, this is the best way to uncorrupt it.

    2) SIDE EFFECT: PROJECT SIZE

    It was found that there are times where an object would be decompiled and while the Stream object from the storage would be properly invalidated, it would be orphaned and left in the Storage, and then would not be cleaned up later. 

    There are many applications that use structured storage that have such a problem in their garbage collection... VBA/Access is just one of them, that's all. Over time, these orphaned streams will contribute some bloat to the project. People noticed that a fully compiled app would take up more space than the same app fully compiled with all objects imported to a new database..... and that is the very issue being discussed here. As you may have guessed, the /decompile switch, which invalidates *all* streams that contain compiled code, does an effective job of garbage collection and removes these orphaned streams. Thus, a /decompile /compact will make for the smallest possible size of as database.

    RISKS TO DECOMPILE: WHY YOU SHOULD NOT USE IT CONSTANTLY

    If you think about the mechanism, you are relying on the canonical text always being completely valid, and you are relying on the ability to globally invalidate a compiled state. If there is ever a problem in either area, /decompile will take a project that was working fine and turn it into cottage cheese. And while such bugs should not happen.... it is impossible to make a /decompile bug happen without using /decompile. They simply did not extensively test a command line switch that was never meant to be used.... nor should they have to, really.

    SO, PLEASE REMEMBER that this is a very powerful technique that was added for reasons having nothing to do with any of the reasons that you may want to use it now. It may help you save an otherwise hopelessly corrupted project. But use it sparingly as you may end up in a worse situation than you started by just globally using the switch on projects that do not need it.

    IF IT AIN'T BROKE, DON'T FIX IT.

    Adam Cogan
    Sydney, Australia
    SSW
    adamcogan@s*w.com.au

  9. Do you use Linked tables instead of ADP when using SQL Server as a backend?

    Linked tables are a more flexible solution than ADP. They have the ability to have local queries and local tables, and the ability to connect to multiple data sources. Any performance considerations of reports are negated by using SQL Server Reporting Services for reports. Most developers also prefer modifying SQL tables directly within SQL Server's management studio.

    The preferred way to connect to SQL Server is MDB file format or ACCDB file format. This enables you to use the full flexibility of local tables and local queries, while leveraging the full power of SQL Server. In addition, MDB and ACCDB files link to multiple SQL Servers and a wide variety of other data sources. Office Access 2007 contains many new features available in both MDB and ACCDB file formats, but only a subset of those features are available in ADPs.

    Because of the layers required to get from Access to SQL Server in the ADP architecture, it is often easier to optimize MDB/ACCDB file solutions. However, there are some scenarios where a report might be generated significantly faster in an ADP file.

Acknowledgements

Jatin Valabjee