“… is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.“ error when using ODBC drivers in MS Access

This error typically occurs when tables intended to be imported, contain special characters in their table names or columns, such as square brackets, dots etc. To illustrate the issue at hand, I have utilized the CData ODBC Driver for GoogleSheets as an example.  

I have created a Google Sheets spreadsheet instance named Reports, containing a sheet named Sales and a column named [Total]. When this table is imported in Microsoft Access using the CData ODBC Driver for Google Sheets, the following error is thrown: 

[Total]’ is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. 

To resolve the issue, we will need to set the UseSimpleNames connection property to True in the DSN Configuration window: 

This property makes the driver easier to use with traditional database tools as it converts any nonalphanumeric character to an underscore. When the UseSimpleNames property is set to True the column name is converted from [Total] to _Total_ and the table can be successfully accessed: 

NOTE: You will need to restart MS Access in order for the DSN changes to take effect. For more information about the UseSimpleNames property, please consult the driver’s documentation: https://cdn.cdata.com/help/RLJ/odbc/RSBGoogleSheets_p_UseSimpleNames.htm  

There are also some variations of the error:  

1. If the sheet name would contain special characters, for example our sheet would be called [Sales], we would get the following error: 

• “‘CDATA_GOOGLESHEETS_REPORTS_[SALES]_PRIMARYKEY_INDEX’ is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.”          This issue occurs when special characters are part of the sheet name or the spreadsheet name, as opposed to the first example when they are in the column name. 

2. If our sheet name would have the (*) special character, for example Sales*, the error would be: 

• “The Microsoft Access database engine could not find the object 'GoogleSheets.Reports_Sales*'. Make sure the object exists and that you spell its name and the path name correctly. If 'GoogleSheets.Reports_Sales* ' is not a local object, check your network connection or contact the server administrator.” 

These types of errors can also be eliminated by using the UseSimpleNames property. 

Please reach out to support@cdata.com if this does not resolve the problem for you.