Please Educate Me.......

[bluenose76]

Ladies and Gentlemen,

I have my db stored on a server and have split the db so that i can have a front end back end scenario.

Prior to splitting the db the size of it was 104 MB (yes quite large)

after splitting the db my back end is only 7.5 MB with the front end sitting at 104 MB?

this has confused me immensly as i would have though that the largest part of the db would be the back end where all the data is stored in the tables?

If this sounds correct to you? is there a way that i can reduce the size of muy Front end? if so then i am open to all suggestions.

Thank you in advance and i look forward to your replies.

 

[rsmonkey]

you're quite right the back end should be the larger file. how did you split the db & have you compacted & repaired the db recently?

 

[GaryPanic]

and have you moved all the tables to the back end ???

on an empty FE/BE yes the front can be larger than the back (but not for long)

 

[David Eagar]

Basics first - are you sure that you linked the tables to the front end?

 

[bluenose76]

Gentlemen,

I have split the db by going to "TOOLS > DATABASE UTILITIES > DATABASE SPLITTER"

This created the backend with just the tables in it and the front end has everything else with linked tables (table symbol with an arrow on it)

does this change anything????

 

[boblarson]

Gentlemen,

I have split the db by going to "TOOLS > DATABASE UTILITIES > DATABASE SPLITTER"

This created the backend with just the tables in it and the front end has everything else with linked tables (table symbol with an arrow on it)

does this change anything????

That is a normal way of doing it and should be fine. Now, as to the size issue - It is NOT out of the ordinary to have the front end be bigger than the backend IF, and I say again IF, you are using graphics in the forms, you have a lot of queries, forms, macros, modules, and reports. One of the biggest space hogs is going to be graphics. It is cool to make your forms look different than the plain jane stuff already in Access. But, you have to accept that the file size is going to be much larger than if you don't use them.

So, I don't know if that is your situation, but that is just a little of why your frontend can be bigger than your backend. (plus that happens if you drink too much beer )

 

[bluenose76]

Bob,

I do have a few macros, forms queries and a couple of modules but all of this is standard!

My forms are straight out of the can with a plain white background and no inages in the entire db.

I have not as yet tried the compacting databse but shall do when i get back to work tomorow (I forgot to bring it home with me) and hopefully this will make a difference?

Bev

 

[boblarson]

I forgot to mention - deleting tables and using make table queries can do it too. Yes, try compacting as it will remove any space that is still there from deleted objects. That space does not get recovered until a compact.

 

[bluenose76]

Gentlemen,

I have done the compact and repair option and it has reduced my db size for 100 + MB to just 2 MB which is fantastic,

Can anyone tell me exactly what it is that has happened by conducting the compact and repair procedure just so that i know in my own mind? I find it amazing that the db has been reduced in size by approximatly 100MB.

Thank you all once again for your help.

Regards
Bev

 

[boblarson]

See if this helps:

http://www.officeusers.org/see/26287

 

[rsmonkey]

Its weird I'm not sure if this is a general trend within the Access world but in my experience I have found that documentation on Compacting & Repairing a db has always been minimal to say the least. I know at uni we were barely told about it.. just a thought

 

[GaryPanic]

also any temp tables in there ..

 

[CraigDolphin]

From Bob's linked article...

Microsoft Access databases are uniquely designed to allow databases to consistently increase in size, unless you compact them.

Does anyone have any idea why Access would have been designed with this 'feature'? Is there some kind of rational explanation for it, or is this just the usual M$ quality experience we've all come to expect?

 

[ChrisO]

Not a complaint but more just as a comment of some age…

“Do you de-frag on the fly or do you wait until a more opportune time? The choice Access made is to do all its de-frag work at once during the compact process. This gives you the best performance.”

That’s a good point but I think it describes a lazy attitude on the part of the system designers of late, if indeed it exists.

If we consider ‘System Availability 24/7’ then there would be no time allocated to do a compact. Yet some computers going back to the 70’s, and perhaps even further, did compact on the fly.

The method employed then was to shift the ‘garbage collection’ task to a low priority. That way it should not interfere with more important tasks operating in the foreground but only execute the ‘garbage collection’ task in the background as time permits.

So in the ~70’s if we sat at the system terminal and typed something like ‘Memory’ and would get an answer.
If we then deleted a task and typed it again we would get a slightly higher response.
If we typed ‘Memory’ again we would get a slightly higher response.
Eventually the system response stabilizes…the system ‘garbage collection’ has done its job.

These days computers have a lot of idle time so why not keep them busy doing ‘House Keeping”.

Consider the notice we get on shutdown…”Windows is Saving Your Settings”.
Why does it take so long?
Is there no background task that could run concurrently at low priority to save our ‘Dirty’ settings?
Is there no background task that could run concurrently at low priority to collect garbage?

I don’t know.

Kind regards,
Chris.

 

[CraigDolphin]

Pat, thanks for the very helpful explanation. At least there is a rationale for it

I think I agree with you Chris. It would make sense for most of my databases to use idle time to do the house-keeping on a strictly low-priority basis.