Flaws in Microsoft Access

Provided by Allen Browne, January 2004. Last updated: April 2010.


Problem properties

Recent versions of Access have introduced new properties or changed the default setting for existing properties. Accepting the new defaults causes failures, diminished integrity, performance loss, and exposes your application to tinkerers.

Databases: Name AutoCorrect

Any database created with Access 2000 or later, has the Name AutoCorrect properties on. You must remember to turn it off for every new database you create:

The problems associated with this property are wide-ranging. For details, see: Failures caused by Name Auto-Correct.

You may also wish to turn off Record-level locking:

Although record-level locking may be desirable in some heavily networked applications, there is a performance hit. Even more significantly, if you have attached tables from Access 97 or earlier and record-level locking is enabled, some DAO transactions may fail. (The scenario that uncovered this bug involved de-duplicating clients - reassigning related records, and then removing the duplicate.)

In Access 2007 and later, you will also want to uncheck the box labelled Enable design changes for tables in Datasheet view (for this database) under File (Office Button) | Access Options | Current Database. In Access 2007 and later you can create a template database that sets these settings for every new database. For details, see Default forms, reports and databases.

Fields: Allow Zero Length

Table fields created in Access 97 had their Allow Zero Length property set to No by default. In Access 2000 and later, the property defaults to Yes, and you must remember to turn it off every time you add a field to a table.

To the end user, there is no visible difference between a zero-length string (ZLS) and a Null, and the distinction should not be forced upon them. The average Access developer has enough trouble validating Nulls without having to handle the ZLS/Null distinction as well in every event procedure of their application. The savvy developer uses engine-level validation wherever possible, and permits a ZLS only in rare and specific circumstances.

There is no justification for having this property on by default. There is no justification for the inconsistency with previous versions.

Even Access itself gets the distinction between Null and ZLS wrong: DLookup() returns Null when it should yield a ZLS.

You must therefore set this property for every field in the database where you do not wish to explicitly permit a ZLS. To save you doing so manually, this code loops through all your tables, and sets the property for each field:

Function FixZLS()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Const conPropName = "AllowZeroLength"
    Const conPropValue = False
    
    Set db = CurrentDb()
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.Name <> "Switchboard Items" Then
                For Each fld In tdf.Fields
                    If fld.Properties(conPropName) Then
                        Debug.Print tdf.Name & "." & fld.Name
                        fld.Properties(conPropName) = conPropValue
                    End If
                Next
            End If
        End If
    Next
        
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

How crazy is this? We are now running code to get us back to the functionality we had in previous versions? And you have to keep remembering to set these properties with any structural changes? This is enhanced usability?

If you create fields programmatically, be aware that these field properties are set inconsistently. The setting you get for Allow Zero Length, Unicode Compression, and other properties depends on whether you use DAO, ADOX, or DDL to create the field.

Prior to Access 2007, numeric fields always defaulted to zero, so you had to manually remove the Default Value whenever you created a Number type field. It was particularly important to do so for foreign key fields.

Tables: SubdatasheetName

In Access 2000, tables got a new property called SubdatasheetName. If the property is not set, it defaults to "[Auto]". Its datasheet displays a plus sign which the user can click to display related records from some other table that Access thinks may be useful.

This automatically assigned property is inherited by forms and subforms displayed in datasheet view. Clearly, this is not a good idea and may have unintended consequences in applications imported from earlier versions. Worse still, there are serious performance issues associated with loading a form that has several subforms where Access is figuring out and collecting data from multiple more related tables.

Again, the solution is to turn off subdatasheets by setting the property to "[None]". Again, there is no way to do this by default, so you must remember to do so every time you create a table. This code will loop through your tables and turn the property off:

Function TurnOffSubDataSh()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property
    Const conPropName = "SubdatasheetName"
    Const conPropValue = "[None]"
    
    Set db = DBEngine(0)(0)
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 Then 'Not attached, or temp.
                If Not HasProperty(tdf, conPropName) Then
                    Set prp = tdf.CreateProperty(conPropName, dbText, conPropValue)
                    tdf.Properties.Append prp
                Else
                    If tdf.Properties(conPropName) <> conPropValue Then
                        tdf.Properties(conPropName) = conPropValue
                    End If
                End If
            End If
        End If
    Next
    
    Set prp = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose:   Return true if the object has the property.
    Dim varDummy As Variant
    
    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

Forms: Allow Design Changes

The Allow Design Changes property for new forms defaults to True ("All Views"). This is highly undesirable for developers. It is also undesirable for tinkerers, as there is some evidence that altering the event procedures while the form is open (not design view) can contribute to corruption. (In Access 2007 and later, this property seems to be removed from the Property Sheet and ignored by the interface, though it is still present and still defaults to True.)

Again, we find ourselves having to work around the new defaults. Rather than setting these properties every time you create a form, consider taking a few moments to create some Default Forms and Reports.

Find Dialog

You should also be aware that the Find dialog (default form toolbar, Edit menu, or Ctrl+F) now exposes a Replace tab. This allows users to perform bulk alterations on data without the checks normally performed by Form_BeforeUpdate or follow-ons in Form_AfterUpdate. This seems highly undesirable in a database that provides no triggers at the engine level.

A workaround for this behavior is to temporarily set the AllowEdits property of the form to No before you DoCmd.RunCommand acCmdFind.


Home Index of tips Top