Can Compact on Close Affect User Security?

[Brian Smith]

Given how corrupted databases can sometimes have some rather strange
consequences, I was wondering if there is any documented cases where a
database corrupted by a failed compact on close operation has caused user
security to get screwed up? I've been searching Google and have come up
empty so far but, then again, I could be using the wrong search terms. Do
any of you security experts have any comments on this possibility?

Thanks.

Brian

 

[TC]

Any corruption to an mdb file, from any source, including but not
limited to compact-on-close problems, could cause unpredictable effects
to that database, including but not limited to, changing the security
settings. So the short answer to your question is Yes - documented or
not!

HTH,
TC

 

[Brian Smith]

Any corruption to an mdb file, from any source, including but not
limited to compact-on-close problems, could cause unpredictable effects
to that database, including but not limited to, changing the security
settings. So the short answer to your question is Yes - documented or
not!

HTH,
TC

Thanks for the answer. I thought that was the case but I take it that it is
safe to assume that Compact on Close is not known to be the cause of any
major problems; i.e., its use shouldn't be avoided.

Brian

 

[TC]

There is nothing wrong with compact on close as far as I know.

But remember that in a split datbase, it will only compact the /code/ -
not the /data/. So in my applications, I have code to close all open
(bound) forms, so it can then compact the back-end database - the one
that actually has all the data.

If you're not sure what I mean by split database or front/back ends,
google the Access groups for those terms (and FE, BE) for more
information on what they mean & why a split design is a good thing.

HTH,
TC

 

[Brian Smith]

There is nothing wrong with compact on close as far as I know.

But remember that in a split datbase, it will only compact the /code/ -
not the /data/. So in my applications, I have code to close all open
(bound) forms, so it can then compact the back-end database - the one
that actually has all the data.

If you're not sure what I mean by split database or front/back ends,
google the Access groups for those terms (and FE, BE) for more
information on what they mean & why a split design is a good thing.

HTH,
TC

I think I must be missing something here. I am using a split database model
and do understand how that works. For both the FE and the BE I have Compact
on Close checked. My understanding is that once the last user exits the
application that the BE will close down and that Compact on Close will run.
Why would the data in the BE also not be compacted? If it isn't, then that
doesn't seem to make much sense.

Brian

 

[TC]

Urk, sorry. For some reason I assumed that you meant that you had
"compact on close" set for the FE files(s), but not necessarily for the
BE file. I'm not sure why I made that assumption. Presumeably, if it is
set for the BE file in an FE/BE situation, that would compact the BE
ok.

Cheers,
TC

 

[Douglas J. Steele]

I think I must be missing something here. I am using a split database
model
and do understand how that works. For both the FE and the BE I have
Compact
on Close checked. My understanding is that once the last user exits the
application that the BE will close down and that Compact on Close will
run.
Why would the data in the BE also not be compacted? If it isn't, then that
doesn't seem to make much sense.

Sorry, but I believe your understanding is incorrect. Having "Compact on
Close" checked in the back-end database will only cause the back-end
database to be compacted if you were to actually open the back-end database
in Access, as opposed to connecting to it via the front-end. (In other
words, it's a pretty useless thing to do! <g>)

To automatically compact the back-end, you could add code to your front-end
that checks whether or not anyone's currently using the back-end (check
whether the corresponding LDB file exists), and, if not, uses the DAO
CompactDatabase method to compact the back-end. What I typically do is
rename the existing back-end file to something meaningful (such insert a
date into it), then use CompactDatabase to compact the renamed file back to
the properly named file. That way, I have a backup if necessary.

 

[TC]

Sorry, but I believe your understanding is incorrect. Having "Compact on
Close" checked in the back-end database will only cause the back-end
database to be compacted if you were to actually open the back-end database
in Access, as opposed to connecting to it via the front-end. (In other
words, it's a pretty useless thing to do! <g>)

Ow, I stand corrected. I wasn't 100% positive, which is why I said
"presumeably". But that was a cop-out, I was foolish not to test it
myself. Thanks for the correction.

To automatically compact the back-end, you could add code to your front-end
that checks whether or not anyone's currently using the back-end (check
whether the corresponding LDB file exists), and, if not, uses the DAO
CompactDatabase method to compact the back-end.

I have this on a menubar option (so the user has to choose it
manually). The option just tries the compact & checks the result. If it
works, it worked. If it doesn't, he can try again later.

Cheers,
TC

 

[Douglas J. Steele]

I have this on a menubar option (so the user has to choose it
manually). The option just tries the compact & checks the result. If it
works, it worked. If it doesn't, he can try again later.

Since users usually forget, I try and put it as a cleanup activity when I
shut down the switchboard (or hidden form if I've got one)

 

[TC]

I guess another approach, to spread it out a bit, would be to do it
(automatically) on every n'th close.

Cheers,
TC

 

[Douglas J. Steele]

I guess another approach, to spread it out a bit, would be to do it
(automatically) on every n'th close.

Funny you should mention that! One app I built, I put a counter in that
reminded the user to backup to diskette every nth time they used the
application (n was configurable). I'd compact first. That was years ago: I
don't remember whether I put in logic to handle the backend database
exceeding the size of a diskette (although I'm sure I must have). However,
given it was a membership application for a fairly small club, I doubt that
would have been an issue even yet (if, in fact, they're still using the
database)

 

[Brian Smith]

Ow, I stand corrected. I wasn't 100% positive, which is why I said
"presumeably". But that was a cop-out, I was foolish not to test it
myself. Thanks for the correction.

I'm starting to get confused with how all of this works and, unfortunately,
Microsoft's documentation isn't helping too much. Is there a property or
function which returns the path of the backend database? I thought I could
use the Connect property but after using it a few times I keep getting an
error message that indicates that I cannot open anymore databases. This is
the code I'm using to determine the path of the BE.

Public Function DbBELocation()
DbBELocation() = Mid(Application.CurrentDb("LinkTable").Connect, 11)
End Function

Also, I'm assuming that there should be an LDB file that corresponds to the
BE database file. Yet, all I'm seeing are LDB files for the FE and for the
Workgroup file. Is there any reason why I wouldn't be seeing this file?

Thanks.

Brian

 

[TC]

Well, now that I think of it, my own application is inconsistent. It
monitors the length of time since the last backup, and gives the user
an increasingly strident warning about the impact of not backing-up.
(So the backup is still a manual action, but the system warns the user
when do do it.) But it doesn't do the same with compact. It /does/
remember how long it was since the last compact, but it does not give
the user a warning that it is time for another.

I will file a bug report!

TC

 

[John Mishefske]

Given how corrupted databases can sometimes have some rather strange
consequences, I was wondering if there is any documented cases where a
database corrupted by a failed compact on close operation has caused user
security to get screwed up? I've been searching Google and have come up
empty so far but, then again, I could be using the wrong search terms. Do
any of you security experts have any comments on this possibility?

Brian, you don't state what errors/problems you are experiencing or what version of Access
you are using. If you are using Access 2000 and experiencing permission related issues
then the problem may be related to how A2K compacts.

Its the only version that copies the .mdb file outside the current app folder before
compacting (it copies the file to the defined TEMP folder). Because of this the compacted
version picks up the permissions of the TEMP folder (under certain conditions) and when
copied back to the app folder (after the compact) the permissions may be different from
what you expect.

There is an MSKB article about this (can't find it off hand). Wait... Goggle has it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;295234