Provided by Allen Browne, January 2004. Last updated: April 2010.
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.
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.
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.
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
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.
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 |