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.

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