Access record 2k size limit

[alanpaterson]

I have no idea about access, so this may sound dumb. I've been told that Access has a 2k record size limit and I have been asked how to change that.

Any ideas?

Many thanks

Alan

 

[ZylaWonder]

Alan:
I do not think you can change the size limit. However, you can work around this by using linked tables.

 

[ZylaWonder]

OK here is the long version:

Tables are designed in terms of columns and data is stored in ROWS. Columns are sometimes called fields and ROWS are sometimes called RECORDS. A RECORD really means the complete set of data for any 1 business transaction. A ROW is the complete set of data for a given record from any one (single) table.

The actual specs as defined in Knowledge Base Article Number Q302524 are as follows:
Database Size: 2Gig less System Space
# of fields in a table : 255
Table Size: 2Gig less System Space
# of Characters in a text field : 255
# of characters in a record: 2000 excluding Memo & OLE

It seems to me that Microsoft used the definition of record in their specs as : "ROW". In all reality if a RECORD could only contain 2000 characters then what is the point of a relational database? My interpretation would be: "A given ROW in a single table" could not exceed 2000 characters. In a real world database I have RECORDS that span several tables that contain more than 2000 characters and have no problems. I do not have a ROW in a single table that exceeds 2000 characters. Each table would in fact have the same ROW limit. The workaround is: In a normalized database, most RECORDS are not going to be contained in a single table. Therefore your size limit for a RECORD is only limited to the number of tables involved in a single transaction. By "linking" or creating relationships between the tables each RECORD or transaction is limited only your table design. Just as your database size is limited to 2 gigabytes because your database can include linked tables in other files, its total size is limited only by available storage capacity.

Gates: Hope I have clarified myself. Thanks for making me step up to the plate and use my brain.

Alan: Hope this helps. No question is ever dumb.

 

[mac318]

Here's a real dumb question. If a row (record) can only contain 2000 characters, how come I can embed, not link, a 10000 byte word document in a single field?

mac

Hey, I admit. I'm dumb on this.

 

[ZylaWonder]

"Do as I think not as I say". Example: the above reference article states that a table size may be 2Gig. Given that a database may only be 2Gig logic would lead us to believe that if your database held only 1 table all would fit. However, on the version specific note, MSDN tells us that a table is limited to 1Gig. Both articles indicated that the specs are for Access2000. Go figure? Anyway..

Alan: First you really need to know the difference between a row and a record. In larger applications or if you move to another development platform in can really make a difference. It's really just how you think about it!! Ok now, the specs indicate that the size of the ROW does not include MEMO and OLE fields. I do remember reading somewhere in Knowledge Base land the size for these objects, but cannot specifically remember.

 

[alanpaterson]

Many thanks for everyone's help, really helpful.

Alan