Microsoft Access Database Names and Reserved Word Problems

Microsoft Access Database Names and Reserved Word Problems

Designing your Microsoft Access database requires a plan, some dedicated time and vision, and without some scope for your new database, you can easily fall at the first hurdle when building and implementing a system with the lack of attention to detail of using key names and reserved words.

Some of the reserved names and words can be used depending where it’s being utilised but some will prompt you with a warning as being ambiguous because they are properties of forms, reports, sections, tables, or global names and constants from the Application object and the DAO, ADODB and VBA access database reserved names and words

Because it would not be practical to provide a full list of all reserved names and words, like built-in function names, VBA constant keywords or MS Access user-defined name references, you will need to research and check the online documentation.

Microsoft Access Database Names and Reserved Word Problems

To help you along with a quick check, I have a link to a useful tool for you from Allen Browne.

I’m a fan of Allen Browne (he’s an Access guru too) and he offers a simple Microsoft Access database issue checker utility that reports any potential issues with your database structure.

To make life easy for yourself, don’t use the obvious keywords and names like ‘Text‘ which is a reserved data type word. In some cases, a warning will appear and either prevent you from applying and abusing the rule or passively accept your change.

Different objects will have different syntax checks so a table name will vary to its fields which will be different for queries and so on through to macros – don’t get me started on VBA code and constants!

There are at least six main categories or libraries that keyword conflict that can occur and includes the Access application, JET engine (which drives the database), SQL language for your queries, built-in functions, user-defined functions (VBA) and VBA constants.

You will need to study and research the libraries which is why the issue checker tool will help as well as the the online Microsoft web pages.

The best way to avoid or reduce conflicts is to add your own convention or prefix to items that may interrupt the smooth running of the database. Also, expand on words if this helps. For example, when adding a date field, don’t call it just ‘Date‘ which will be accepted in some case but be more explicit like ‘OrderDate‘ or ‘DateCreated‘. 

Finally, avoid using spaces in your Microsoft Access database object names as at the higher level using VBA code and without the convention of wrapping square brackets around your objects will reduce other potential errors.

Don’t forget to check out my MS Access Database eBooks which covers good design techniques and working practices including naming conventions.

This entry was posted in Database Theory, MS Access, Utilities and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.