Does Access store its data in fixed length or variable length rows

[Dennis]

--
Dennis

 

[Tony Toews [MVP]]

I would assume variable length. Of course it may not even store all of
the the data for a particular row in a contiguous section of the file.

With the exception of Memo and OLE fields a row is always stored in
one 4K page. (2k page sizes in Jet 3.5/Access 97 and earlier) You
can test this yourself by created a table with about 20 text fields
set to 255 character length. You will be able to save slightly less
than 4096 characters. But once you hit the limit you will get an
appropriate error message.

Of course multiple records can be stored in one 4K page if there is
sufficient room.

You
could do a quick and dirty test to see if it is variable or fixed length.
Create a new database with one table with a text column of 200 or so
characters. Create a method that lets you specify to append a thousand or so
rows of data. Make a copy of the database. Run the process on one database
telling it to save a short value, say a single character. In the other run
the process on the second telling it to save a long value, say 200 characters.
Close and compact both. Compare their sizes. What do you get?

Given that Jet extends the Access files in 4K chunks this might be not
very precise.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Tony Toews [MVP]]

But that does not answer the question of how it stores the data within a
row.

It is variable length.

Databases
decompiled and compacted. Respective sizes: 1,636KB and 8,296KB.

Yup, that would be large enough that the 4 kb wouldn't be significant.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/