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.

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?

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)

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

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????

Compact the front end. Access doesn't actually delete anything until you compact the database so you need to to this on a regular basis for production databases and at least every day while you are in development.

I don't remember if Access deletes the data tables from the front end when the database is split. I think it just renames them. So, once you verify that the BE is working properly, make a back up of the FE just in case, then delete the local tables from the FE and compact it again.

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 :D )

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

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.

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

See if this helps:

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

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

also any temp tables in there ..

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?

It takes a lot of overhead to constantly regain waste space. The problem is similar to that faced by the operating systam and why frequently updated files get fragmented. think about it this way -
Rec1 = 100 bytes, Rec2 = 60 bytes, Rec4 80 bytes.... 50,000 records. If you expand the size of Rec2 from 60 to 100 bytes, do you really want Access to read and rewrite 50,000 records while you wait? No you don't, so it marks the 60 as no longer needed and writes the updated record in the next available free space. If you update Rec2 and don't change the length or make the length shorter, the record is rewritten in place.

The issue is similar with work space. Access allocates space to do something. Before that thing finishes, Access allocates space to do something else. You have the same issue here with space management. 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.

In a production database that doesn't create a lot of wasted space by creating and deleting records and using SQL strings rather than querydefs, there will be little if any growth in the FE and the BE will grow as more records are added. In that case you won't need to compact the FE at all. You should still compact the BE on a regular schedule because as part of the compact process, Access updates its table statistics and that can affect execution plans for queries. Tables of a certain size will be read with a full table scan. If they get larger, or useful indexes are added, Access will switch its access plan to use the index rather than reading every record.

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.

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.

My overview was symplistic. The process of managing record inserts, deletes, and updating in place is much more complex. It also requites some knowledge of anticipated activity so that appropriate levels of free space can be interspersed throughout the data area of the file. No RDBMS actually re-writes the entire file to insert a record, but still large numbers of records could be impacted.

Now to the issue of Access. Lazy has nothing to do with it. Access is a file server rather than a data base server. That means that all processing takes place on the client rather than on the server. Access databases are shared so having multiple instances of Access trying to de-frag the same physical file is simply not manageable. That is really why it works the way it does. With a server, you have ONE application instance managing the physical data and with Access you may have many.