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.

AccessRelationshipsMsg1

"<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.

AccessRelationshipsMsg2

"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”

One thought on “Misleading error message when creating relationship between two tables with lengthy names”

  1. A heartfelt thankyou for posting this information. You have helped me to solve the ‘Invalid argument 3001′ error that kept popping up when I tried to create a relationship in vba.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>