Split database Compacting

[bcooler]

I currently have a 2007 database that is not split which currently is about 10meg and is probably only about 1 meg of actual information. I'm interested in splitting because I think it might make the system run a little faster, but I have concerns before I do it.

1a.) Since I currently compact the un-split database now at the end of the session, will the split one continue to compact the front AND backend?

1b.) Maybe I'm missing the point. Is the strategy to make the FE a .ACCDE executable file so I don't have to compact? If so, do the tables have to be compacted?

2.) Is there anyway to push FE updates w/o user effort (especially .ACCDE updates)? I seem to make more updates than I'd like to admit and would have to inconvenience everyone each time I make a change.

3.) Are there any pitfalls for splitting the DB?

Thanks for the help. I'm a little worried I'll mess up something by doing this, but can see some good reasons to try.

 

[RoyVidar]

1a) - No - a compact & rapair effects the current db
1b) hm - the frontend should normally not need any compacting, as it shouldn't contain any data
2) Check aut Tony Toews FEupdater - very good and free
3) NOT splitting, has all the pitfalls, like corruption, loss of data etc...

Roy-Vidar

 

[bcooler]

Thanks for the response!

A followup couple of questions:

1a)If I split the DB, can/should I need to continue to compact the BE so it is as small (manageable and less error prone)?

1b) Is a split database the only reasonable way to use ADDCE executables?

Thanks!

 

[RoyVidar]

1a) You should perhaps periodically compact & repair the DB, yes (every fortnight, month...). But do not fall for the temptation of using the "Compact on close" option that can be found somewhere, do it manually.

1b) A split database is the only reasonable way to use any kind of ACE/Jet database, as non-split databases is the most known recipe for corruption in a multi user environment. Some say split is needed also for single user apps.

Roy-Vidar

 

[Hap007]

There are many reasons to use a Split Frontend/Backend.
Everyone of the reasons is probably good enough to justify this concept.

Here is just two great reasons to do it.
1) If you need to make modifications, you can make those modifications on a seperate test verson of the frontend. When the changes are complete and tested, you just give the user(s) the new frontend version. If you were using a single mdb, then your users would have to stop data entry while you were making revisions.

2) If you use a FrontEnd, you can compile it into an mde and give that to your users. By giving the user an mde, there is no chance that they might drop into the code and accidently change something.

Hap...



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[bcooler]

Great answers! My only fear is I forget to compact the BE database occasionally. I suppose I could write some code to occasionally do so..

Thanks fore the help!

 

[MajP]

http://support.microsoft.com/kb/158937

 

[MajP]

Also maybe interested in:

http://support.microsoft.com/kb/295334

 

[bcooler]

Thanks all! I think I feel comfortable enough to give it a shot!

 

[MajP]

You may want to read on compact and repair in the help file. There is a pretty good discussion that explains some of the why's of compaction. Also I disagree with the statement that there is no need to compact the front end because it does not contain any data. I always "compact on close" the front end. "Access creates temporary, hidden objects to accomplish various tasks. Sometimes, these temporary objects remain in your database after Access no longer needs them." This does not just apply to data.

 

[bcooler]

Thank you for the info. If I save as a ACCDE, do I need to worry about it?

 

[MajP]

I do not now if you need to "worry", but even an accde will grow somewhat. I compact on close everything, except for a shared backend. If it is not shared, then i compact on close the backend as well.

An accde really has nothing to do with compaction. The purpose of an accde is to compile your code and secure your code, forms, and reports. I think you may be confusing "compiled" code to a "compacted" database file.

 

[bcooler]

From your comments, it sounds like my assumption is wrong.

My assumption is that a compiled code is sort of a "read-only" file that is used to interact with the tables, but does not grow or shrink due to usage since there is no need for any changes to the code, right? If so, it is both compiled and compacted and no further maintenance is necessary.

 

[Hap007]

Hi,

All access files grow over time.
mde, mdb, ACCDE,ACCDb they all grow

Any time you query data, temporary tables are created by access. Both in the FrontEnd and Backend

If you are worried about this growth, I would suggest this approach.

1) Compile and place the mde or ACCDE on the user's local drive as the FrontEnd
2) Link the FrontEnd to the backend on a shared network drive.
3) Set the Compact on close for the frontEnd, and that will take care of the FrontEnd growth. I also open the FrontEnd using the exclusive switch for better performance
4) At some interval (weekly or monthly) have everyone exit their copies of the FrontEnd and then compact the Backend.

Here is how I would compact the backend
1) Have everyone exit the FrontEnd copies.
2) Rename the backend. ie MyData.mdb to MyData-hold.mdb
4) Make a copy of the backend at this point
5) If it is a large file, copy the backend to a local drive because compacting a large mdb across a network can be slow.
6) Compact the Backend
7) Copy/rename the compacted backend back to the original name and location.

This works well for me.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121