Is there a way to find the size of each database object?

[Chrisso]

Hi All

I have a reasonably simple database but even after running "Compact &
Repair" it seems the whole .mdb file is much larger than it should be
compared to other MS Access database that I have created.

Is there an easy way to find the size of each database object? I have
forms with VB code and reports and macros.

I have started a new .mdb file and importing the objects over one by
one to find the culprit but this is (of course) a long and painful
process.

I performed a similar test where I imported each set of objects
(tables, queries, forms, reports, macros and modules) sequentially and
checked the size of the .mdb after a compact and repair and the main
culprits are my forms and reports. What I really want to be able to do
is to ask Access or use a tool to find out which form or which report
is taking all the size.

Does anyone have any hints for me?

Thanks in advance,
Chrisso

 

[Allen Browne]

Access does not expose this kind of information to you.

The worst culprits are any forms/reports that have a Picture background, or
an image embedded on them.

After that, any tables with an OLE field. In Access 2007, the Attachment
field can also be an issue (though less so than previous versions.)

Decompile before compact/repair may also help.

 

[Chrisso]

Access does not expose this kind of information to you.

The worst culprits are any forms/reports that have a Picture background, or
an image embedded on them.

After that, any tables with an OLE field. In Access 2007, the Attachment
field can also be an issue (though less so than previous versions.)

Decompile before compact/repair may also help.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.












- Show quoted text -

Thanks Allen. I do have a company logo on my main screen and some
images on buttons. This makes my .mdb look really professional but I
would like the smallest size .mdb as possible. Is there anyway to have
both the embedded images and a small .mdb? The pictures I am embedding
are tiny in size but I guess from what I have read that means nothing
- it must be the way that Access stores those images - probably as
large .bmp files or something. Sigh....

Thanks Allen,
Chrisso

 

[Allen Browne]

What version of Access? A2007 stores images compressed, but previous
versions did not.

If you have that picture repeated on lots of forms, perhaps you could create
just one subform, and put it behind the other stuff on your form. Messy to
maintain, but at least it's only in one place.

One of the things I do is to put an image control named imgLogo on each
form, and set its Picture property to (none). Put the function below into a
standard module, and call it in the Open event of each form:

Private Sub Form_Open(Cancel As Integer)
Call LoadLogo(Me.imgLogo)
End Sub

Public Function LoadLogo(img As Image)
Dim strFile As String
strFile = CurrentProject.Path & "\logo.jpg"
If Dir(strFile) <> vbNullString Then
img.Picture = strFile
End If
End Function

Now if the client has a file named logo.jpg in the same folder as the
database, it displays on the forms without being stored in the database at
all.

 

[Chrisso]

What version of Access? A2007 stores images compressed, but previous
versions did not.

If you have that picture repeated on lots of forms, perhaps you could create
just one subform, and put it behind the other stuff on your form. Messy to
maintain, but at least it's only in one place.

One of the things I do is to put an image control named imgLogo on each
form, and set its Picture property to (none). Put the function below intoa
standard module, and call it in the Open event of each form:

Private Sub Form_Open(Cancel As Integer)
    Call LoadLogo(Me.imgLogo)
End Sub

Public Function LoadLogo(img As Image)
    Dim strFile As String
    strFile = CurrentProject.Path & "\logo.jpg"
    If Dir(strFile) <> vbNullString Then
        img.Picture = strFile
    End If
End Function

Now if the client has a file named logo.jpg in the same folder as the
database, it displays on the forms without being stored in the database at
all.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -

Thanks for the tip Allen. I will try this out.

Chrisso

 

[Chrisso]

Simon Lloyd;628232 Wrote:

What version of Access are you using?, the maximum file size in Access
is limited to 2GB that means the database including all objects etc.

To find the size of a record then look up 'Data Type Summary' on
onboard help, and calculate the number of bytes for each column, and add
these up to provide a 'estimated' number of bytes for each record,
naturally with each record there are other overheads too.

Have you tried this:
1. Compact the database and close
2. In Windows Explorer check the MDB file size
3. Re-open the MDB file
4. Using your table, double the number of records.
5. Repeat steps 1 & 2

it will give you an idea of the growth of a specific table and the
relevant size.

Did you see this reply?, it may help you determine the size of your
database.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=174830

Microsoft Office Help

Thanks Simon. I will try this out and report back.

From other posts though I think my size problem is related to embedded
pictures on forms.

Thanks again,
Chrisso

 

[Paul Shapiro]

You could create a new blank database. Import a set of objects and check the
file size. I would compact the db after the import before recording the
size. Start by importing all your tables, so that gives you a good estimate
of the data size. Then import the modules and any macros, which probably
don't add much size. Once you determine the object type responsible for the
size, you can create a new blank db and import half of those objects. Etc.

Simon Lloyd;628232 Wrote:

What version of Access are you using?, the maximum file size in Access
is limited to 2GB that means the database including all objects etc.

To find the size of a record then look up 'Data Type Summary' on
onboard help, and calculate the number of bytes for each column, and add
these up to provide a 'estimated' number of bytes for each record,
naturally with each record there are other overheads too.

Have you tried this:
1. Compact the database and close
2. In Windows Explorer check the MDB file size
3. Re-open the MDB file
4. Using your table, double the number of records.
5. Repeat steps 1 & 2

it will give you an idea of the growth of a specific table and the
relevant size.

Did you see this reply?, it may help you determine the size of your
database.

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this
thread:http://www.thecodecage.com/forumz/showthread.php?t=174830

Microsoft Office Help

Thanks Simon. I will try this out and report back.

From other posts though I think my size problem is related to embedded
pictures on forms.