MORE THAN 255 FIELDS IN AN ACCESS TABLE???

[Hermes]

Is possible to have more than 255 fields in an Access table? <br>I know that the default for number of fields in an Access table is 255, but I would like to know if is there any option or procedure to have more than 255 fields in an access table.

 

[misterstick]

no, it's not.<br><br>255 is a lot of fields, however, and if think you need this many you're probably not structuring your data properly.<br><br>if you really really need this many, here are some messy fixes:<br><br>1. use two files, link them together with a unique key and set the cascade update and cascade delete properties. how about splitting the whole thing into tables of screenfuls of data anyway?<br><br>2. use a memo field for the extra fields and fill with a vbNullString separated list of subfields, using vb to parse them out.<br><br>that's all i can come up with off the top of my head. hope it helps.<br><br>mr s. &lt;

 

[JimHorton]

Or...<br>You could 'verticalize' the data. A 'second table' approach, but different from misterstick, this one gives unlimited fields.&nbsp;&nbsp;The limitation here is the data types need to be similar or compatible--usually not a problem if you use a string field--if you have total control of i/o then there is little chance for bad conversions.&nbsp;&nbsp;Do like this:<br>First table (tblMain--whatever structure you have)...A second table (tblExt) with this structure (3 Fields):<br><br>tblMainKey&nbsp;&nbsp;&nbsp;&nbsp;'&lt;---whatever key was in tblMain<br>tblextFieldname '&lt;---Unique fieldname as data values--1 rec per field<br>tblextFieldValue '&lt;--probably text 10 or 20, handles dates, numbers, short text.<br><br>Key is<br>tblMainKey <br>tblextFieldName<br><br>Now lets say you have 1000 fields. For *each* record in tblMain, there are *1000* records in tblExt.&nbsp;&nbsp;This is not a big space problem, since it's a narrow table (only 3 fields).&nbsp;&nbsp;It *is* workable, I've done it as an academic excercise a while back.&nbsp;&nbsp;But, as misterstick alludes, you should analyze the data a bit further.&nbsp;&nbsp;I'm not saying it's out of the realm to need that many fields--there are indeed many things that have thousands of different pieces of information to store about a single item, but often closer data model examination can prevent kludges like mine and the others.<br>--Jim