Clarity needed re: compacting a database

[CB]

Hi everyone,

I thought I was clear on how to use compact/repair but after reading various
posts on these Access boards I find I’m now confused.

First let me say that I have a small, split database that employs USL. The
BE is on a server and the FEs are on the various users’ desktops. There is
virtually never more than one person using the database at one time. I’m in
the process of creating an Admin manual for anyone else who may take over the
database in the future and doesn’t have tons of Access experience. To be
honest, it’s as much a reminder for myself since I don’t get to spend much
time on the development side now that I have a useable database.

Anyway, I’m at the point in the manual of discussing the importance of
compacting the database when I turned to the Access discussion groups for
assistance with my explanation.

Imagine my surprise when I read a post that says you should never compact an
open database! (Darned if I could find the post again to reference it here.)
It might have been a post by an MVP but I don't recall for sure as I read
many posts.

Might I have misunderstood? Clearly, you wouldn’t compact from an open
database if there was a chance that someone else might be using it at that
time. But, is there really something wrong with compacting an open database
otherwise?

In fact, I tried to compact my database when it was closed and I couldn’t,
which makes sense to me. I can’t open the database by simply double-clicking
the file to open it so why would I be able to compact it by simply selecting
the database file to compact. BTW, when I try to compact in this manner, I
get an error message that tells me I don’t have permissions to use the object
– the very same error message as if I tried to open the database without
using the desktop shortcut.

Thanks to all who can help clear up this muddiness!

Regards,

Chris

 

[Tom Wickerath]

Hi Chris,

First let me say that I have a small, split database that employs USL.

I think you meant "ULS", which stands for User Level Security.

Imagine my surprise when I read a post that says you should never compact an
open database!

I think what the person meant is you should never attempt to compact the BE
(back-end "data" database) by initiating the operation from the FE (front-end
or "application" database). You need to open the BE in exclusive mode (ie. no
other users) in order to compact. Personally, I recommend copying the BE file
from the network share to your local hard drive, do the compact and repair on
that copy [which can also serve as a back-up or a local test copy], and then
copy it back to the file server. I am really leary of attempting to initiate
a compact and repair where a network separates your machine from the actual
data file.

BTW, when I try to compact in this manner, I
get an error message that tells me I don’t have permissions to use the object
– the very same error message as if I tried to open the database without
using the desktop shortcut.

That's actually a good sign, because it indicates that you have implemented
ULS correctly. If one implements ULS on an unsplit application, and then uses
the Database Splitter wizard, the wizard will happily create an unsecured BE
data file. It appears as if your BE data file is properly secured. So, you
simply need to create a second shortcut for database administrators use only,
that includes the path to msaccess.exe, the path to the BE database
(preferably on your local hard drive), the /wrkgrp switch, and the path to
your .mdw file (also preferably on your local hard drive).



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jeff Boyce]

Chris

You described two pieces to your application, a FE and a BE. Which one are
you trying to compact? From where?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[CB]

Hi Tom,

Thanks for the quick reply! Yep, "USL" was a typo.

And thanks for the reinforcement ... I thought I had it right and looks like
I did. As for making a copy, that's exactly what I was recommending. I'm
curious though why you are leary about compacting over the network.

Chris

Hi Chris,

First let me say that I have a small, split database that employs USL.

I think you meant "ULS", which stands for User Level Security.

Imagine my surprise when I read a post that says you should never compact an
open database!

I think what the person meant is you should never attempt to compact the BE
(back-end "data" database) by initiating the operation from the FE (front-end
or "application" database). You need to open the BE in exclusive mode (ie. no
other users) in order to compact. Personally, I recommend copying the BE file
from the network share to your local hard drive, do the compact and repair on
that copy [which can also serve as a back-up or a local test copy], and then
copy it back to the file server. I am really leary of attempting to initiate
a compact and repair where a network separates your machine from the actual
data file.

BTW, when I try to compact in this manner, I
get an error message that tells me I don’t have permissions to use the object
– the very same error message as if I tried to open the database without
using the desktop shortcut.

That's actually a good sign, because it indicates that you have implemented
ULS correctly. If one implements ULS on an unsplit application, and then uses
the Database Splitter wizard, the wizard will happily create an unsecured BE
data file. It appears as if your BE data file is properly secured. So, you
simply need to create a second shortcut for database administrators use only,
that includes the path to msaccess.exe, the path to the BE database
(preferably on your local hard drive), the /wrkgrp switch, and the path to
your .mdw file (also preferably on your local hard drive).



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi everyone,

I thought I was clear on how to use compact/repair but after reading various
posts on these Access boards I find I’m now confused.

First let me say that I have a small, split database that employs USL. The
BE is on a server and the FEs are on the various users’ desktops. There is
virtually never more than one person using the database at one time. I’m in
the process of creating an Admin manual for anyone else who may take over the
database in the future and doesn’t have tons of Access experience. To be
honest, it’s as much a reminder for myself since I don’t get to spend much
time on the development side now that I have a useable database.

Anyway, I’m at the point in the manual of discussing the importance of
compacting the database when I turned to the Access discussion groups for
assistance with my explanation.

Imagine my surprise when I read a post that says you should never compact an
open database! (Darned if I could find the post again to reference it here.)
It might have been a post by an MVP but I don't recall for sure as I read
many posts.

Might I have misunderstood? Clearly, you wouldn’t compact from an open
database if there was a chance that someone else might be using it at that
time. But, is there really something wrong with compacting an open database
otherwise?

In fact, I tried to compact my database when it was closed and I couldn’t,
which makes sense to me. I can’t open the database by simply double-clicking
the file to open it so why would I be able to compact it by simply selecting
the database file to compact. BTW, when I try to compact in this manner, I
get an error message that tells me I don’t have permissions to use the object
– the very same error message as if I tried to open the database without
using the desktop shortcut.

Thanks to all who can help clear up this muddiness!

Regards,

Chris

 

[Tom Wickerath]

Hi Chris,

I'm curious though why you are leary about compacting over the network.

Because any little network noise could turn a perfectly working database
into chopped liver. Are you 100% sure that your network never has any noise
or lost packets? See the following site, from Access MVP Allen Browne:

Preventing Corruption
http://allenbrowne.com/ser-25.html

Network
Intermittent cable connectors, hubs/switches, network cards, inherently
unstable networks such as WiFi.

For me, I'd rather eliminate the possibility of network related corruption.
Access MVP Dirk Goldgar and myself helped a good friend of mine about a year
and a half ago, who had corrupted his BE database by compacting while it was
on the file server. This friend works for the Transportation Security Agency
(TSA). Presumably, the TSA has the resources to maintain their networks in
good working order. The error my friend immediately started getting after the
compact operation was "AOIndex is not an index in this table". Lucky for him,
we were able to recover his database using the code shown here:
http://allenbrowne.com/ser-47.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[CB]

Hi again,

I suspected you were going to mention network issues. So, it isn't safe to
think that if the LAN is stable enough to use a split database over that it
is stable enough to compact over?

I know little about how Access is actually transferring info over the LAN
but if you answer 'no' to my question above I'm guessing that much more data
is passed back and forth during a compact than when simply entering info.
Right?

Thanks for your help1

Chris

 

[CB]

Hi Jeff,

I was thinking that the manual would discuss compacting in two places (in
the manual).

First, as routine maintenance the admin would compact the BE on a regular
basis. I hadn't really given much thought as to whether the admin would
compact a copy over the network or on their hard-drive but Tom's post has
given me food for thought. I didn't see much point in having users compact
their FEs as I didn't see those changing in any way (they don't have
permission to change anything).

Second, when the admin modifies anything in the FE or BE (in copies - not
the working files), I was going to suggest that as one of the last steps -
make a copy of their final work, compact the copy, if all goes okay, deploy
those files. Again, I hadn't given much thought to whether or not the admin
would be working over the network or not.

Looks like I've been lucky in doing all my development over the network! I
must say that I never got into the habit of backing up my own machine. I
almost always work over the network (in any application) since I know the
servers are backed up daily. My bad ....

Chris

 

[Tom Wickerath]

Hi Chris,

I suspected you were going to mention network issues. So, it isn't safe to
think that if the LAN is stable enough to use a split database over that it
is stable enough to compact over?

That's my thinking. The database I mentioned, in use by the TSA, had been in
use for 3 years before suffering any corruption. My friend had compacted the
BE database in the past, without first copying it to his local hard drive.
So, it's kind of like playing Russian Roulette (no offense intended for my
Russian friends). But, I think you get the idea...spin the barrel too many
times, and you might just end up with a loaded chamber! I'm just being
conservative, by choosing to *never* take a spin of the barrel!

I'm guessing that much more data
is passed back and forth during a compact than when simply entering info.
Right?

Yes. In fact, the entire database file gets re-written during a compact and
repair operation. The compact process involves creating a temporary file in
the same folder that is a copy of your database. The idea is that the
original .mdb (or .accdb for Access 2007) file gets deleted, and the newly
created temporary file gets renamed as the original filename. Sometimes this
delete and rename process occurs even though the newly created temporary file
is not a complete [for whatever reason] copy of the source database file.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[CB]

Hi Tom,

Thanks for the feedback. I'll keep this information in mind when preparing
my manual - for that matter, when I make modifications myself.

Chris

Hi Chris,

I suspected you were going to mention network issues. So, it isn't safe to
think that if the LAN is stable enough to use a split database over that it
is stable enough to compact over?

That's my thinking. The database I mentioned, in use by the TSA, had been in
use for 3 years before suffering any corruption. My friend had compacted the
BE database in the past, without first copying it to his local hard drive.
So, it's kind of like playing Russian Roulette (no offense intended for my
Russian friends). But, I think you get the idea...spin the barrel too many
times, and you might just end up with a loaded chamber! I'm just being
conservative, by choosing to *never* take a spin of the barrel!

I'm guessing that much more data
is passed back and forth during a compact than when simply entering info.
Right?

Yes. In fact, the entire database file gets re-written during a compact and
repair operation. The compact process involves creating a temporary file in
the same folder that is a copy of your database. The idea is that the
original .mdb (or .accdb for Access 2007) file gets deleted, and the newly
created temporary file gets renamed as the original filename. Sometimes this
delete and rename process occurs even though the newly created temporary file
is not a complete [for whatever reason] copy of the source database file.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi again,

I suspected you were going to mention network issues. So, it isn't safe to
think that if the LAN is stable enough to use a split database over that it
is stable enough to compact over?

I know little about how Access is actually transferring info over the LAN
but if you answer 'no' to my question above I'm guessing that much more data
is passed back and forth during a compact than when simply entering info.
Right?

Thanks for your help1

Chris

 

[Tom Wickerath]

Hi Chris,

Looks like I've been lucky in doing all my development over the network!

Yes, you have been lucky. I recommend doing all design work on local copies
of the FE & BE database. For one thing, you should notice a much faster save
process, when saving any changes to a bound form or report in design view.

I must say that I never got into the habit of backing up my own machine. I
almost always work over the network (in any application) since I know the
servers are backed up daily. My bad ....

You can get minor corruptions that creep into a JET database, which the
server will happily back up for you. I have heard of folks in the past trying
to recover a database from a saved server backup, just to find that all past
backed up copies also had the same subtle corruption.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John W. Vinson]

Second, when the admin modifies anything in the FE or BE (in copies - not
the working files), I was going to suggest that as one of the last steps -
make a copy of their final work, compact the copy, if all goes okay, deploy
those files. Again, I hadn't given much thought to whether or not the admin
would be working over the network or not.

I'd go a step further.

I've had all sorts of wierd problems with FE's after making structural changes
(even minor ones) in the BE tables. It may be overkill, but I've taken to
routinely opening the frontend, deleting all the links, compacting (holding
down Shift to prevent startup actions), and using File... Get External Data...
Link to relink to the tables in the backend, after ANY change to a backend
object. I'm sure you could just delete and relink the one or two tables that
were modified, if you prefer.

I'll also usually make a new .MDE file (after compiling and compacting) for
distribution to the users. You'll keep the .mdb file for future work, of
course, but for the users a .mde will be smaller, often faster, and will
prevent prying eyes and sticky fingers from getting into your form and report
design or into your code.

 

[CB]

Hi John,

Thanks for the tip re: re-linking tables. Actually, I've already deployed
..MDEs files but thanks for reminding me to include it in my manual.

Regards,

Chris