One of the joys of Microsoft is interoperability between applications such as Access and Excel. A stunning example of this is importing Excel data from a worksheet which is done via an easy-to-follow wizard-style feature on the External Data menu tab.

Column/field naming conventions

One of the “gotchas” however, is possible problems stemming from column headings in Excel. I am busy with some testing to see exactly what characters are and are not allowed and how Access handles them.

Spaces

In Excel I commonly use headings that contain spaces. In Access I follow strict naming conventions for fields, including the rule: no spaces allowed. Access does, however allow spaces in the names of table fields and this will not cause any problems during import.

It does not interestingly enough accept leading spaces – one would think it would simply run a CLEAN-type function to remove leading, trailing and other extra spaces in a string. Having said that, it drops trailing spaces with no problem and simply accepts multiple spaces between characters with no problem.

Question marks

Question marks are allowed, even as a leading character. When I tried to use a question mark on its own as a name for a field, Access completed the import, but then crashed and restarted when I tried to open the table.

Name AutoCorrect Save Failures table

Underscores

Underscores are accepted before, between and after characters. Underscore and space combinations as well as consecutive underscores are also accepted.

Exclamation Marks

Interesting! A heading with an exclamation mark in any position results in a warning and a conversion to the generic name, Fieldn

By MisterFoxOnline

CAT Educator

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.