MS Access Reserved Word Checker

If you aren’t already aware of it, there are a set of words which should never be used to name

  • Database objects (Tables, Queries, Forms, …)
  • Table/Query Fields
  • Form/Report Controls
  • VBA procedure names, variable, …

these are referred to as Reserved Words and thus Reserved Words need to be avoided at all cost as they can generate strange behaviors or outright failure of your database.

There are numerous examples of oddities caused by Reserved Words, but below is one of them as an example:

Compiling Deletes Code
... Just today, VBA started deleting any new code entered when I compile...

 

The issue with Reserved Words is that there is simply no way to remember them all.  Furthermore, when taking over another developer’s work, it is next to impossible to review every object, control, … for such terms.

As such, I decided to try and automate the verification process to speed up and guarantee the results.  Hence was born the MS Access Reserved Word Checker.  A very simple tool that searches through:

  • Tables and their Fields
  • Queries and their Fields
  • Forms and their Controls
  • Reports and their Controls
  • Macros

looking for the use of:

  • Reserved Words
  • Special Characters

in their names.

The results are then tabulated in a form for review

Download a Demo Database

Feel free to download a demo copy by using the link provided below:

Download “Access Reserved Word Checker (Access 2013 x32 accdb)” ReservedWordChecker.zip – Downloaded 7563 times – 119.27 KB

Disclaimer/Notes:

If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

All code samples, download samples, links, ... on this site are provided 'AS IS'.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

Version History

V2.001 (2022-10-18)

  • Fixed issue with Space not being flagged as a Special Character
  • Updated Form Copyright

V2.000 (2017-07-10)

  • Results are now pushed/stored in a table
    • Enabling you come review the results at a later date/time without needing to rerun the entire analysis
  • A report has been created so the results can be printed
  • Ability to export the results to Excel has been added

V1.001 (2017-06-22)

  • Added verification of Queries and Query Field Names

V1.000 (2017-06-21)

  • Initial release

Useful Links

11 responses on “MS Access Reserved Word Checker”

  1. Gordon McWee

    Would it be possible for you to include a button on the results form to allow the results to be printed.

    1. Daniel Pineault Post author

      I have a couple plan to export the result to Excel, reporting, further scans, but just haven’t had a chance to work on it any further as of late. I will do my best to look at it this week and try and push out another update.

  2. Gordon

    Brilliant. I didn’t realise until your app that I was incorrectly using so many reserved words! With a printed report it will be so much easier to work through and correct things. Thank you so much.

  3. Gordon

    Don’t mean to be picky just helpful, but you need to amend your info form to say that the rpt_reservedWords_Results report has to be imported also.
    Thanks again.

    1. Daniel Pineault Post author

      Thank you for pointing that out.

      I was trying to push out an update as quickly as possible to help you out that I overlooked the info form. I have just updated it to include these omissions. Everything should be good now.

  4. Gordon

    Sorry, it’s me again. My main interest in v2 is to be able to use the print function which works great. However, I thought I would try out the Export to Excel feature but can’t get it to work. I get a “Sub or function not defined” error at the line:
    Call Export2XLS(rs)
    I though this could be because the sub is called “Private Sub cmd_Export2XLS_Click()” but changing the Call line to “Call cmd_Export2XLS(rs)” still produced an error.

    1. Daniel Pineault Post author

      This is probably due to the same issue that I hadn’t originally mentioned you needed to import the module which included the necessary function. If you re-download the sample file, the instructions include the fact that you need to do so.

  5. Gordon

    Sorry, my bad! Foolishly I only imported the bits that I thought had changed. Lesson learned. All is well now.
    Thanks.

  6. Ian Woodward

    Thanks, this is great.

    At the top of the module there are two different copyright notices which appear to my layman’s eyes to contradict each other.
    1. ” It may not be copied, reused or modified in any way without prior authorization from its author(s).”
    2. “The following may be altered and reused as you wish so long as the copyright notice is left unchanged”

    I am not trying to change functionality, but I am currently wondering whether I should leave downloaded code “as-is”, or if I should adapt its names to conform to my naming convention.

    1. Daniel Pineault Post author

      Sorry about that. No.2 applies to what I release publicly. So feel free to do as you please, just please retain the copyright noticed (not like many people see it.