CODEWITHSUNDEEP
ms-accessvbams-access-2007
mjhenry
mjhenry

Reputation: 53

Maximum fields number in Access

I have an Access 2007 database which I am trying to import an Excel spreadsheet into using Excel VBA. The VBA script was informing me that the "record was too large". I investigated by reducing the field number until it transferred successfully. The fields number was reduced from 143 to 102 but my research shows the max number of fields for Access tables is 255 so why is mine stopping at 103?

Note: I did not design the database or gather the data but tasked with importing the data. Personally I would further normalize the data

Upvotes: 1

Views: 824

Answers (1)

HansUp
HansUp

Reputation: 97111

255 is the absolute upper limit for the number of fields a table can contain. That limit also applies to queries and recordsets.

Additionally there is a limit (4000) on the number of characters a record can contain. See Access 2010 specifications, which applies to both Access 2007 and 2010 ...

Number of characters in a record (excluding Memo and OLE Object fields) when the UnicodeCompression property of the fields is set to Yes

So 16 text fields with UnicodeCompression each containing 255 characters would exceed that limit despite the fact that 16 is far below the 255 maximum number of fields.

I'm not quite certain how this applies to your import from Excel situation but will hazard a guess that the combined character count of the source columns may exceed 4000 characters.

Upvotes: 3

Related Questions