Reserved Words and Special Characters
There are numerous words that should not be used to name fields, objects, and variables. For the most part, these are called reserved words. Reserved words have a specific meaning to Microsoft Access, the Jet database engine, and the new Access database engine (ACE). We also list reserved words that have specific meaning to SQL Server or ODBC drivers. Depending on how your application interfaces with other programs, it may be prudent to avoid using words that have specific programmatic meanings to those as well. If you want to start a list of reserved words, begin with the list of all the properties of database objects, all Visual Basic keywords, and all third-party and user-defined names and functions.
Access 2007 creates an error message when select reserved words are used as field names. For the most part, however, using reserved words often creates error messages that do not indicate the source of the problem. For example, it is far from intuitive that the following error message:
The wizard was unable to preview your report, possibly because a table needed by your report is exclusively locked may have been triggered by the use of a reserved word. Consequently, a developer may unnecessarily spend time troubleshooting the wrong problem. When you are working with an application that uses reserved words, particularly as the name of tables or fields, rename the database objects if it is at all possible and feasible to do so. If it isn't possible to rename them, then be sure to enclose the names in brackets when they are called in code or in queries. Here's an example showing the name of the table in brackets because the term tableName is a reserved word:
SELECT fieldX
FROM [tableName]
When writing code, it is often handy to use the IntelliSense feature and just select from the available list of objects and actions. This requires the use of Me. rather than Me! However, if the name of a field is a reserved word that could also be the object's property, the code will not compile. The debugger will stop and highlight the problem object, as shown in Figure K-1. In this instance, merely changing the syntax to use Me! (bang) instead of Me. (dot) will allow the code to compile. You cannot avoid the problem simply by not compiling the code, because that merely ensures that the code will break and stop the application from running.
Figure K-1
We recommend that you develop the practice of debugging promptly after making changes to any c ode. This enables you to catch errors promptly and prevent them from being repeated or compounding. Considering all the things that can go wrong, and the propensity for something to go wrong at the worst times, why tempt fate by not doing everything you can to ensure that your code will run smoothly?
Additionally, search and replace utilities such as Speed Ferret are designed to find and replace the offending word(s) throughout the application, including the code project or VBA. In the past, a couple of products were consistently recommended by developers, but it will likely be a while before similar tools are released for the new file format. The Access news groups and MVP sites are great resources for learning about these types of tools. Some are free, but even a purchased program typically pays for itself with its first use.
It is clear that if reserved words are causing a problem with a database, it is worth enforcing naming conventions. Implementing a comprehensive naming policy can help you avoid most of the problems associated with reserved words. Appendix L discusses some of the well-accepted naming conventions.
Post a comment