MS Access cannot link to SQL Server table if a column name is 64 characters or more (i.e., effective limit 63 characters)

Question

When the Linked Table Manager sees a column name in a SQL Server table with 64 characters, it only uses the first 63 characters of that column name.

After linking, an attempt to view table data will fail, with the message

"ODBC--call failed" 

"[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name "'123456789012345678901234567890123456789012345678901234567890123'."

The column name in my table on SQL Server is:

"1234567890123456789012345678901234567890123456789012345678901234" 

So Linked Table manager is truncating that for some reason.

There is a 64 character limit for object names.

I cannot find any documentation anywhere saying there is such a limit of 63 characters in ODBC linked tables.

Earlier before installing ODBC v17 (v17.3) driver, I had ODBC v13 and in that I also could not use a column name of 64 characters or more, though I believe I was seeing no error message (can't remember for sure), just that data view showed #NAME or #ERROR or something like that as the value for data in each column and row. 

The version of SQL Server is SQL Server 2017, on Windows 10 Pro.   The version of MS Access is from Office 365, (Access Version 1903), and is installed on the same Windows 10 Pro instance as the SQL Server is using.  For the Linked Table manager, my results are the same whether using the Windows Integration authentication as the SQL Server (login) authentication. 

Is this known and/or expected behavior?

Answer 1

I can reproduce the problem both with ODBC 13 and ODBC 17.1, but unfortunately I have no solution except, of course, to shorten the column name in the SQL-Server table (!). I have not found any information on this, but I think this is a bug in the driver.

Sorry this is not much help... You might want to report this on Access User Voice

Matthias Kläy, Kläy Computing AG

Answer 2

Try creating a view on sql server, and go:

select FirstName, lastName, MyHuge64ColumnName as MyShortName, etc., etc., etc

From tblZoo

Now, in access link to that view in place of the table.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 3

> You might want to report this on Access User Voice.

Done, though MSDN won't let me link it here until I've verified or something.  Can search access(dot)uservoice(dot)com and search on the keywords: odbc issue 64