How to Find Max. Size of a Record

[Raabi]

Hello wizards

I have a query as mentioned on the subject line.
After designing the Structure of a table, how can I know what will be the maximum size of each record; so that I can estimate the total size of my data table.

Any help is highly appreciated.

 

[Pat Hartman]

Add the lengths of the individual columns to find the maximum record size. If you want to find the length of the maximum existing record, you'll need a query.

Select len(fld1) + len(fld2) + len(fld3)... As totLength

In the query grid sort descending on totLength and set the top property to 1.

 

[Raabi]

There is should be an automated way of calculating the record size accurately.

I will apprciate if someone comes up with a professional solution, please!

 

[WayneRyan]

Raaib,

Exactly what is "unprofessional" about Pat's answer? Bashing Pat isn't the
way to get support here.

Do you want a theoretical maximum?
Do you want an existing maximum in the table?

Wayne

 

[boblarson]

I don't know that you are actually going to be able to come up with a maximum per record size. You are talking about number of Bytes that the record takes up in storage space, correct?

About the only way I can think of is to empty all records, compact the database and then add about 10 records with the maximum amount of data that each field will store (for memo fields that is over 65,000 characters) and then check to see what the size change has been and divide that by 10 to get an average.

I don't think you are going to get it down to a specific amount though. I think the average is going to be about the best way to get an approximate max size.

 

[Raabi]

Hi Wayne

It seems I have offended; which I never mean. I asked for a professional answer just out of frankness. Anyhow, sorry for the unintentional expression.

Regards,

 

[WayneRyan]

Raabi,

That's OK.

But, did you get what you are looking for? What was your requirement/solution?

Wayne

 

[Raabi]

Hi Wayne

Actually, most of the time I am looking for the solutions for academic purpose. Still I could not find fully automated way to find the max. possible size of a record. I am still expecting there mus be a function, or something like; SizeOf(dbStructure) etc. Because, I remember there was such a function available in DB III / DB IV.

Anyhow, I feel grateful for your concern and interest.

Have a great time.

 

[Pat Hartman]

Feel free to write such a function and don't forget to share it with us You can do it by looping through the fields collection of the tabledef collection. I have a table of datatypes somewhere that I could share if you want it. You would need to look up the size for each datatype except for text sicne all the others are fixed. But that is in help also.

 

[gemma-the-husky]

its not just data though is it - access stores relationships, tables designs, query plans etc ALL within the datbase

why worry about the size, unless it gets near the lmiit 1Gb or 2 gbs

out of interest, i have a client storing about 3-400,000 delivery records (quite large records - lots of largish text fields), and related job info etc - the back end database is currently about 150Mb

 

[WayneRyan]

A heavily indexed table also requires additional storage, especially if string
fields are indexed.

Wayne