The names of tables in all versions of Access are limited to 64 characters.
Names of linked tables in Access 97 to SQL Server tables via ODBC are limited to
1. the length of the name of the owner of that table in SQL Server (usually ‘dbo’) plus
2. an underscore (_) plus
3. The length of the table name from SQL Server plus
4. the length of the longest index name in that table
If the result of this calculation is a name greater than 64 characters the table will not be able to be linked.
e.g. this table cannot be linked as the compound name is too long (65 characters)
1. owner: dbo, length 3 characters
2. underscore (_), length 1 character
3. SQL Server Table Name: CustomerSalesOrder, length 18 characters
4. Longest Index Name in table CustomerSalesOrder: IDX_CUSTOMERSALESORDER_CUSTOMERSALESORDERID, length 43 characters
Compound Name: ‘dbo_CustomerSalesOrderIDX_CUSTOMERSALESORDER_CUSTOMERSALESORDERID’
This is an ODBC limitation and which only causes problem Microsoft Access 97.
Using Microsoft Access 2000 or 2002 the maximum length of a table name linked to SQL Server via ODBC can be calculated as items 1, 2 and 3 from the list above.