Important Notice: Our web hosting provider recently started charging us for additional visits, which was unexpected. In response, we're seeking donations. Depending on the situation, we may explore different monetization options for our Community and Expert Contributors. It's crucial to provide more returns for their expertise and offer more Expert Validated Answers or AI Validated Answers. Learn more about our hosting issue here.

With MS Access, why can I index on text fields -OR- why do I get “Invalid field definition field\” in definition of index or relationship?

April 26, 2017definition Fields index MS relationship text

0

Posted

With MS Access, why can I index on text fields -OR- why do I get “Invalid field definition field\” in definition of index or relationship?

1 Answer

0

Posted

Text fields are mapped to SQL_LONGVARCHAR by default. As a result MS Access treats these colomns as “Memo” types. The good news is that you can store up to the PostgreSQL block size limit in a text column. PostgreSQL has a tuple limit of just under 8k prior to version 7.1 which includes a new feature called TOAST which allows the storage of much larger strings. You can change the mapping of Text fields to SQL_VARCHAR by unchecking the Advanced driver option “Text as LongVarchar” under Data Type Options. This should allow text fields to be used but you will be limited to the maximum size of a varchar.