0

Ive migrated from Access to SQL Server 2012 but when I link my table back into the Access db I have some field names that are 64characters long, which becuase they have spaces and other special characters in them encloses them in square brackets e.g. [this field]. However the square brackets are included in the character count when creating the linked table into Access it truncates the last 2 characters off the field name e.g. [this fie].

Anyone got a way around this to ensure all 64 characters are shown in the table?

tia

M

June7
  • 19,874
  • 8
  • 24
  • 34
Crazypabs
  • 1
  • 1
  • No MS-Access has annoying limitation on objects name lengths. There is a chart with all these limitations in the documentation – Doug Coats Apr 22 '21 at 16:08
  • Another reason to not use spaces and special characters in naming convention. Underscore is only Exception. – June7 Apr 22 '21 at 17:31
  • 1
    Apparently SQLServer has limits as well, just bigger. 128 characters for object names except for temp tables with limit of 116. – June7 Apr 22 '21 at 18:21
  • How are you linking the tables? If you use the VBA [DoCmd.TransferDatabase](https://learn.microsoft.com/en-us/office/vba/api/Access.DoCmd.TransferDatabase) or DAO [Database.CreateTableDef](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-createtabledef-method-dao) method you won't be able to circumvent the 64 character limitation but you can you specify the local table name. – Nicholas Hunter Apr 22 '21 at 18:31
  • Hi All, thanks for your feedback. What is annoying is that the users currently import spreadsheets that contain field names of 64 characters including the horrid characters ":;'#" etc and they import perfectly well from the spreadsheet into the access database, but when i do the same thing from SQL I get error after error. Very very frustrating, there are potentiall over 1000 changes i will have to do manually if I cant find a work around. – Crazypabs Apr 28 '21 at 08:52
  • To iterate, it isnt the table name im having issues with, it is the field names. – Crazypabs Apr 28 '21 at 08:53

0 Answers0