Why is my DB so HUGE?!

[RachelBedi]

Hi Guys - I need help - I'm hoping that this really isn't a big deal but I'd prefer to check it out before just saying "hard disk is cheap"



My database has around 100 forms - each with code and macros attached to them - then there are the same number (plus a few extra) queries, on the front end

the back end has 5 tables - with a total of around 10K records

my FE file size is now up to 400MB (I'm not kidding - 400,000KB)

I tried decompiling and recompiling - compact and repair - and importing all objects into a black DB and compact and repair and guess what - the file size is exactly the same

We did experience a SERIOUS slow down yesterday which prompted me to decompile/recompile but that only reduced the file size by about 100MB

Do you think that this is just the size of my DB and not to worry about it or am I seriously effed here somewhere?

[pbaldy]

100 forms seems like a lot, but that alone shouldn't bloat the db that much. Do the forms or reports have embedded images in them? That can create a lot of bloat.

[RachelBedi]

they do - which is exactly what I was thinking so I'm going to try removing them - the DB can look pretty later.

My only concern is that the DB was only 100MB lighter before I installed all those images - I checked with IT for the file size of last week prior to loading the images

I'm going to try getting rid of those though and see what kind of difference it makes

[RachelBedi]

Could a custom font have been the culprit?

[RachelBedi]

that's the problem - went from 400MB to 80MB removing most of the embedded images and special font. DAMN.

[pbaldy]

I know embedded images can cause a large amount of bloat in relation to the actual image size. I've never used a special font, but I assume that may have a similar effect. Most of my front ends are between 5 and 15Mb, but I don't normally use embedded images. I'm good at making things work, not good at making them pretty.

[RachelBedi]

hahahaha! well I am good at making things work the problem is the fact that upper management wants the application to look "pretty" because the head of our company is coming to look at my application in the coming weeks - thanks for your reply Paul I REALLY appreciate it - sometimes I need to just talk something out even if I know I'm being silly!

I'll try reducing the size of the image - since it is so pretty - and see where that gets me!

THANK YOU!

[pbaldy]

Why so many forms? I'd wonder if you have a normalization issue. Also, you might be able to put the image(s) in a central location and have the application look there for them, so they're not embedded.

[RachelBedi]

well the reason for so many forms is that each person needs 4 different forms - (the db tracks enrollments in a system - what employee each enrollment is assigned to and the status) of course there would be an easier way to have ONE form that opens with the record source specific to the person logging in however - I don't know how to do that :-( I'm a complete newb!

How can I have the application look for them in a central location? That would be really handy!

[pbaldy]

And if you need to change a form for some reason you have to change 100 of them? Yuck! You don't want to go that way. Several ways to limit a form to a given criteria. This is one:

BaldyWeb wherecondition

another would be basing the form on a query that got the person from a login form or used the Windows login.

[RachelBedi]

No I don't want to go that way! It sucks because I'm getting design change requests left and right!

This article is getting me closer I'm just scared of it :-P

Hmm...seems I could set the query as a parameter query to enter username....

That being said the usernames in my database are the same as the Windows Enviro username (both lazy and clever at the same time I suppose!) is there a way that I could have the query pull the username from the windows environment?

[pbaldy]

That might work (I can't remember offhand if Environ() works outside VBA), or this adapted from my link:

DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Environ("UserName") & "'"

[pbaldy]

Oh, and if after fixing the forms problem you still want to store the pictures outside the db, this type of thing in the open event of the form:

Me.Picture = "FullPathToPictureHere"

[RachelBedi]

you're the BEST- that's exactly what I'm going to with the photos! I was able to find a module I could set up to capture the Windows Username;



Public Function Environ(Expression)
On Error GoTo Err_Environ

Environ = VBA.Environ(Expression)


Exit_Environ:
Exit Function


Err_Environ:



MsgBox Err.Number & " - " & Err.Description
Resume Exit_Environ


End Function



In the query's criteria I just have to enter Environ('Username') and....DONE

[pbaldy]

Excellent! Glad you got it working.