Max Number of Fields Brought In When Linked To Excel Table With 24 columns

[NuNnDaDdY]

Hello,


I am using Microsoft Access 2003 and currently have a linked table from Excel. The template spreadsheet only have 24 columns for users to fill out and submit. However, we are encountering some scenarios where the linked spreadsheet brings in the user columns and then includes columns 'F25' ... 'F256'. This is turn causes a 'Too Many Fields' error to occur. Is there something with the spreadsheet that is causing this or is a setting not being set correctly in Access. The method of import is currently using a spreadsheet with the first row containing columns names. Thank you very much.

[pkstormy]

As you've discovered, you're probably encountering the maximum limitation of the number of fields a single MSAccess table can have (I believe it's 255). You may want to break up the spreadsheet into multiple sheets or excel files and if linking them into MSAccess, try to establish 1 column as a relational field type of column to join the different excel rows on. If you're importing them into MSAccess, again, minimize the # of columns to less than 255, establishing 1 column (probably the first column) as a 'unique key' type of column to join back together again with the other excel files.

For example, you could number the first column with ascending numbers (uniquely) and break the excel file into multiple pieces making sure all the broken apart excel files maintain the same ascending (unique) numbers for the first column (and each excel file has less than 255 columns) so that when they are imported into MSAccess, there's no potential problems of records from one excel row not matching the appropriate record row in the 2nd, 3rd 4th excel file (with all the excel files having less than 255 columns).

Kind of like breaking apart a jigsaw puzzle but numbering each piece so when the pieces are all put together again (after importing), you know where each piece (ie. record row) goes and how it matches to another piece of the puzzle. I hope that makes some kind of sense.

[NuNnDaDdY]

Hello,

Thank you for your reply; however, the problem is that we only have 22 fields in the spreadsheet but Microsoft Access is reading in the blank columns as F23, F24..etc. I'm not sure why it thinks these are fields that contain data.