Table Field Name Size (1 Viewer)

BLD · Sep 29, 2018

All,

Is there any way to increase the Default size of a Table Field Name of 64 characters in Access 2016?

I know this is the default but was hopeful it could be done somehow or a work around other than limiting the import field names I am working with.

Thanks,

BLD

isladogs · Sep 29, 2018

64 characters is the maximum allowed for object names in Access.
See https://support.office.com/en-gb/article/Access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

Pat Hartman · Sep 30, 2018

If you don't have any control over the column names, you have two options.
1. Ignore them and when you link to the spreadsheet, tell Access that the first row does not contain column headings. Of course this messes up the data types of all columns and every column will be read as text but you would then use an append query that ignores the first row and append F1 to somename, F2 to someothername, etc.
2. You can use OLE to open the spreadsheet and have your code replace all the column names with meaningful names of reasonable length. 64 characters is excessive for field names. You just make a typing nightmare for yourself.

I'm going to guess that the files you are importing are not normalized and that is why the names got so long. You should probably clean that up as part of the import.

JHB · Sep 30, 2018

BLD said:
..
Is there any way to increase the Default size of a Table Field Name of 64 characters in Access 2016?
..

Not directly, but you can use the Field's Caption property.

The_Doc_Man · Sep 30, 2018

That's a pretty good suggestion, JHB.

Cronk · Sep 30, 2018

I wonder why anyone would want to have a table name that long. I try to name tables meaningfully and as short as possible. I've maybe had some join tables as long as into the teens but nowhere near 64.

CJ_London · Sep 30, 2018

column heading is probably from excel -something like

Balance Friday before last adjusted for fish, chips and rock'n'roll but not Baywatch or Dallas

The_Doc_Man · Sep 30, 2018

Probably imported from something else that includes a really verbose column title. I've been known to be a BIT verbose... but 64 characters as a column/field name? Sheesh!

BLD · Oct 1, 2018

To all,

Thanks for your comments. Yes it is not normalized. I am working with an excel questionnaire of about 50 questions and I am trying to compile all of the answers. I would have to write too many formulas in excel and wanted to move the data into access. I am thinking about creating multiple tables and maybe create some kind of lookup table with new field names. I do like the idea of the caption property.

Many thanks!

Pat Hartman · Oct 2, 2018

No. Normalize the data.

Sounds like each column in Excel is a question and that's why the title is so long. So, Create a new table called
tblQuestions
QuestionID
QuestionText

And add the first row one column at a time to this table. Then in a second loop, add all the answers from each row one column at a time to the second table
tblAnswers
AnswerID
SomethingthatidentifiestherowID
Answer

Depending on how many columns there are, you might just make two queries and change them x times to pick up the data. But there are diminishing returns. Once you get enough columns, you are better off creating the two code loops especially if you need to import more than one set of data.

Table Field Name Size (1 Viewer)

BLD

New member

isladogs

MVP / VIP

Pat Hartman

Super Moderator

JHB

Have been here a while

Attachments

The_Doc_Man

Immoderate Moderator

Cronk

Registered User.

CJ_London

Super Moderator

The_Doc_Man

Immoderate Moderator

BLD

New member

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread