How do I determine the size of an existing Access 2000 table 3

[koln]

Please help..thank you

 

[OhioSteve]

Open it. The number of records displays at the bottom of the window.

 

[koln]

If I open the table, it shows me the number of records, BUT what Im interested in is how big is that table in megabytes ?

 

[willir]

I have often wonder but never worried about this.

There is no "size" option.

You can probably get a good feel for the size of a table by looking at the table design. Remember in terms of bytes, each field uses...
[tt]
- 1 interger
- 2 interger, boolean
- 4 autonumber, long, single, object reference
- 8 double, currency, date
- 14 decimal
- 16 replicaiton ID
[/tt]

String is tougher...
- 10 bytes + length of string (max 255)

Variant is wierd
- 16 for number
- 22 + lenght of string for string

Hyperlink, Memo
- max of 64,000 characters

OLE object
- max 1 GB

You do some basic math to get an approximation.

Or write a module that looks at the fields and calcualtes this for you.

Coding would be interesting. You have to check the field type...


dim dbs as dao.database

debug.print dbs.TableDefs(x).Fields.Type
Boolean - 1
Long - 4
Currency - 5
Date - 8
String - 10
Memo - 12

The number fields, simple math (bytes for record type * (records)

But for the string type, I suspect you would have to loop through and count the length of each string.

Memo, objects and perhap hyperlinks amy be tougher since you can not access the entire variable. For example, you can only access the first 255 characters in a memo field.

Basically, there appears to be nosimple answer. But ball parking is fairly easy. A programming answer would be "interesting". And you can not get the values for the object type fields.

Richard

 

[PHV]

Does the indexes count also ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mph1]

I am not sure why you need to know the exact size of the tables. What Richard pointed out would most likely be your best bet. What's important is the database size as a whole. As you approach the db size limitation (depends on the version you are using)then a decision will have to be made. In addition, what you may want to check out are the size characteristics of the db and that which is within the db.

You pretty much have to look at the db as a whole in terms of size then the individual parts.
If you type in specifications in help, the following is what you get (using A2k)
Microsoft Access database general specifications
Attribute Maximum
Microsoft Access database (.mdb) file size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255


HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[CajunCenturion]

You can use the following code to determine the size of record, excluing Memo fields.

Set lRst_ReptMast = New ADODB.Recordset
lRst_ReptMast.ActiveConnection = CurrentProject.Connection
lRst_ReptMast.Open "tblReportMaster", , adOpenDynamic, adLockPessimistic, adCmdTable
   
Dim RecLen As Long
Dim Idx As Integer
lRst_ReptMast.MoveFirst
RecLen = 0
For Idx = 0 To lRst_ReptMast.Fields.Count - 1
   If (lRst_ReptMast.Fields(Idx).Type <> adLongVarWChar) Then
      RecLen = RecLen + lRst_ReptMast.Fields(Idx).DefinedSize
   End If
Next Idx

Once you have size of a record, then multiply that by the number of records, and then if you have memo field(s), you'll have to loop through the entire table adding in the length of each individual memo using the Len function.

That being said, even that is not an exact answer because Access has in system tables overhead data about the table itself, and any defined relationships, and PHV ask a good question about indexes.

I have another question: Why?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Of course, you also need to take into account the factors that willir mentioned.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[koln]

Thank you for all your replies, some really smart thinking out there.

To CajunCenturion: No special reason..Im more familiar with SQL Server and just wanted a little bit more knowledge of a inherited Access database and some of the major tables.

Thanks again to everybody

 

[willir]

Cajun - I knew if you read this post, you have the answer for the coding. It would have taken me a while to have figured it out. Well done!

 

[CajunCenturion]

Thanks willir. I'm glad that you made your post, because I had completely overlooked the embedded overhead bytes for the various data types. We were posting at the same time, and when I went back and saw your post, I realized the oversight. I guess the code should be updated, replacing the If statement that checks for the Memo field, to a case statement that based on the field type, also adds in the overhead bytes.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[willir]

Peanuts in the scheme of things.

PHV comment on the indexes is extremely valid since indexes on a heavily indexed table can be larger than the actual data.

Compared to the dBase and like databases with their numerous seperate data and index files, I like Access since it is one (albeit big) file. But there is a lot of black box magic that goes on inside.

 

[OhioSteve]

This is a really interesting topic. Here's a less technical approach which I think would still work.

1) Create a brand new, blank .mdb. Call it test.mdb. Save it and close it.
2) In Windows, right-click test.mdb and learn its size.
3) Open test.mdb again. Then import the table you want to measure into it. Then close test.mdb.
4) In Windows, right-click test.mdb and learn its new size.
5) The size of the table should be the difference between the two measurements.

 

[cwarner]

Hi,

A related question:

what is the maximum size a recordset can get?
Is it limited by Access2000's 2GB limitation?
Are recordsets stored permanently or in a buffer?

I am wanting to know this because I'm switching
to an Access front-end w/ SQL Server back-end and
don't want to limited in any way by Access's 2GB
limitation.

Thanks,
Christy.

 

[OhioSteve]

I have a database with many linked tables. Its size is only 9 mb, and the linked tables contain millions of records. So I would guess that Access does not include linked tables in its .mdb size calculation.

Even if you had the records in the .mdb, it is unlikely that you would hit the 2 gb limit. I maintain a HUGE Oracle database, and its backup file is only 1.3 gb.