Misleading error message when creating relationship between two tables with lengthy names
If you attempt to create a relationship between two tables with a combined table name length greater than 64 characters you will get the following confusing error message.
"<combined table names> is not a valid name. Make sure that it does not include invalid characters and that it is not too long."
This message does not explain exactly where the problem lies. Among other things the create relationships is doing is that Access tries to create an index on the foreign key in the child table which has the name of the two concatenated tables. (Even if the index already exists.)
If you try to create the index yourself with the two concatenated tables names you will get a much better error message.
"The index name is invalid.
The index name may be too long (over 64 characters) or contain invalid characters."
A workaround is to temporarily rename a table so the combined table names are less than the 64 characters. Create the relationship and the rename the table back to the original name.
Thanks to inungh for asking the question titled “Number of characters in a table name”