Monday, March 25, 2013

ColdFusion and "Reserved Words" for MS Access

While I normally only use SQL Server, Oracle, or PostgreSQL, the need inevitably arises to work with an Access database in ColdFusion. Sometimes it is solely for data migration, sometimes to meet a client's specs, but regardless of the reason... there is this annoying thing that must be dealt with when using Microsoft Access databases: "Reserved Words".

You see the issue is that you can't use Microsoft Access reserved words in ColdFusion queries or they will blow up... and the kicker is: there are a LOT of reserved words. Check out this list of reserved words for Access 2000 alone. This means you will inevitably encounter issues when writing queries.

There is a very simple solution though! Simply escape these reserved words by placing inside [brackets]. For example, a column named DATETIME will need to be referenced as [DATETIME].

Hopefully you found this blog post helpful. Sometimes the simplest solutions are the hardest to find!