Interesting issue with compaction. Any ideas to eliminate?

[darcykahle]

I have stumbled upon an interesting issue with regard to compaction of
an access frontend database which resides on a DFS target that utilizes
FRS to mirror the sharepoint, and I was wondering if anyone knows a way
to overcome it.

For those who are unaware of DFS or FRS, DFS stands for Distributed
File System, and basically involves "mounting" various sharepoints that
reside on various computers underneath one special sharepoint. When
you access the mounted directory under the DFS share, you are invisibly
redirected to where the share really is. FRS stands for File
Replication System (I think. it could be Service, tho). If you
indicate that a particular mount point in the DFS share has two or more
target sharepoints, you can tell Windows to use FRS to ensure changes
to the files in the sharepoint are replicated to the other targets.
Both of these are built-in technologies of Windows 2003 server.

On to what I have observed. We have a frontend/backend Access 2003
database application residing on one of these FRS replicated
sharepoints, and have the option to compact the database on close
turned on. What I am seeing is that when the compaction occurs, the
frontend database is renamed to DB1.mdb, and this name change is then
replicated to the other servers. The name is never changed back again,
leaving the shortcuts that the users use to access the database broken.
I have turned off the compact on close option, and the effect stops,
so the issue is linked to compacting on a FRS replicated sharepoint. I
would like to turn the compaction back on, as the database tends to
grow rather large, but I do not want the database's filename to change.
Any ideas?

[Rick Brandt]

da********@gmail.com wrote:

I have stumbled upon an interesting issue with regard to compaction of
an access frontend database which resides on a DFS target that
utilizes FRS to mirror the sharepoint, and I was wondering if anyone
knows a way to overcome it.

For those who are unaware of DFS or FRS, DFS stands for Distributed
File System, and basically involves "mounting" various sharepoints
that reside on various computers underneath one special sharepoint.
When you access the mounted directory under the DFS share, you are
invisibly redirected to where the share really is. FRS stands for
File Replication System (I think. it could be Service, tho). If you
indicate that a particular mount point in the DFS share has two or
more target sharepoints, you can tell Windows to use FRS to ensure
changes to the files in the sharepoint are replicated to the other
targets. Both of these are built-in technologies of Windows 2003
server.

On to what I have observed. We have a frontend/backend Access 2003
database application residing on one of these FRS replicated
sharepoints, and have the option to compact the database on close
turned on. What I am seeing is that when the compaction occurs, the
frontend database is renamed to DB1.mdb, and this name change is then
replicated to the other servers. The name is never changed back
again, leaving the shortcuts that the users use to access the
database broken. I have turned off the compact on close option, and
the effect stops, so the issue is linked to compacting on a FRS
replicated sharepoint. I would like to turn the compaction back on,
as the database tends to grow rather large, but I do not want the
database's filename to change. Any ideas?

Just an opinion. One should never allow multiple users to open a single
shared file as this leads to frequent corruptions and degrades performance.
The recommended approach is to split your app into a shared "data only" file
and individual local copies of a "front end file" for each user. With this
approach "Compact On Close" is rendered pretty useless as it only compacts
the front end file meaning that you are back to doing regular manual
compactions on the data file and your original issue becomes moot.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Steve Jorgensen]

I believe the cause of this is that Access renames the current copy, then
compacts it into a file with the old name, and finally deletes the renamed
original. I can't think of what a work-around might be for this problem.

On 19 Jul 2005 07:13:47 -0700, "da********@gmail.com" <da********@gmail.com>
wrote:

I have stumbled upon an interesting issue with regard to compaction of
an access frontend database which resides on a DFS target that utilizes
FRS to mirror the sharepoint, and I was wondering if anyone knows a way
to overcome it.

For those who are unaware of DFS or FRS, DFS stands for Distributed
File System, and basically involves "mounting" various sharepoints that
reside on various computers underneath one special sharepoint. When
you access the mounted directory under the DFS share, you are invisibly
redirected to where the share really is. FRS stands for File
Replication System (I think. it could be Service, tho). If you
indicate that a particular mount point in the DFS share has two or more
target sharepoints, you can tell Windows to use FRS to ensure changes
to the files in the sharepoint are replicated to the other targets.
Both of these are built-in technologies of Windows 2003 server.

On to what I have observed. We have a frontend/backend Access 2003
database application residing on one of these FRS replicated
sharepoints, and have the option to compact the database on close
turned on. What I am seeing is that when the compaction occurs, the
frontend database is renamed to DB1.mdb, and this name change is then
replicated to the other servers. The name is never changed back again,
leaving the shortcuts that the users use to access the database broken.
I have turned off the compact on close option, and the effect stops,
so the issue is linked to compacting on a FRS replicated sharepoint. I
would like to turn the compaction back on, as the database tends to
grow rather large, but I do not want the database's filename to change.
Any ideas?

[teddysnips]

Steve Jorgensen wrote:

I believe the cause of this is that Access renames the current copy, then
compacts it into a file with the old name, and finally deletes the renamed
original. I can't think of what a work-around might be for this problem.

You *might*, and this is strictly thinking aloud, be able to capture
such an event or cause such an event to be written to the Event Log,
and you could write a Windows Service that would clean up the whole
sorry mess in response to the event. Of course, this is quite a lot of
work for such a bitty problem, and it might not even work at all!

Edward

[darcykahle]

As I indicated, it is a front-end/back-end split database. It just
happens that both the front-end and back-end reside in the same
directory on the server. I see your point that the compact on close
will only affect the front-end. It does appear, though, that this
compaction still is effective, as the frontend tends to grow in size
during the normal process of use. I figure that this means temporary
tables are being generated and then deleted on the frontend in order to
load a form or report. I have no way of tracking this behaviour down,
as I was not the one who designed the app, and I wouldn't know where to
start to look. My preference is to keep it the way it is, and get the
compaction to work.

[MGFoster]

da********@gmail.com wrote:

As I indicated, it is a front-end/back-end split database. It just
happens that both the front-end and back-end reside in the same
directory on the server. I see your point that the compact on close
will only affect the front-end. It does appear, though, that this
compaction still is effective, as the frontend tends to grow in size
during the normal process of use. I figure that this means temporary
tables are being generated and then deleted on the frontend in order to
load a form or report. I have no way of tracking this behaviour down,
as I was not the one who designed the app, and I wouldn't know where to
start to look. My preference is to keep it the way it is, and get the
compaction to work.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you missed the point Rick Brandt was making - each user should
have a copy of the front-end on their PC (or work folder, if you're
using a VPN). It sounds like you have ONE front-end on a server and ALL
users are using that one front-end. This is a no-no 'cuz it can lead to
corruption.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt05QIechKqOuFEgEQJX0gCfZzn57/xNPCYatkIK0VFhX49smZUAoMBs
Qoo32bcKNdpHhMoEEZZF4vWy
=s9Li
-----END PGP SIGNATURE-----

[David W. Fenton]

"da********@gmail.com" <da********@gmail.com> wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:

I have stumbled upon an interesting issue with regard to
compaction of an access frontend database which resides on a DFS
target that utilizes FRS to mirror the sharepoint, and I was
wondering if anyone knows a way to overcome it.

Sharing a front end is a stupid thing to do to begin with. Each user
should have their own copy of the front end.

The back end *is* shared, though, and that still needs to get
compacted. But, since no shortcuts point to it, only links in the
tables, there is no problem caused by the propagation of the name
change of the back end, since no one is opening it directly. To
avoid the problem with not propagating the renaming of the file back
from DB1.MDB, don't compact with the actual back end open in the
front end. Instead, run code that renames the back end, compacts it
to the original file name (i.e., creates a new file) then deletes
the original (or, better still, archives it somewhere).

If you're not using an MDB file for your data, there is no problem
at all once you get away from wrongly sharing the front end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"da********@gmail.com" <da********@gmail.com> wrote in
news:11*********************@g49g2000cwa.googlegro ups.com:

As I indicated, it is a front-end/back-end split database. It
just happens that both the front-end and back-end reside in the
same directory on the server. I see your point that the compact
on close will only affect the front-end. It does appear, though,
that this compaction still is effective, as the frontend tends to
grow in size during the normal process of use. I figure that this
means temporary tables are being generated and then deleted on the
frontend in order to load a form or report. I have no way of
tracking this behaviour down, as I was not the one who designed
the app, and I wouldn't know where to start to look. My
preference is to keep it the way it is, and get the compaction to
work.

If you are *sharing* the front end (i.e., multiple users opening it
simultaneously), that is the source of the bloat.

A properly designed front end will grow to a certain point and then
stop growing. If it continues to bloat, then you have design
problems (such as non-compiled code). There are a few things to do
for this problem:

1. move any temp tables to a separate temp.mdb that is either
recreated in code when needed, or copied from an empty reference
copy on the server.

2. distribute your front end as an MDE so it can't decompile (which
results in recompiling on the fly, which results in bloat).

Converting to an MDE will fail in cases where there's something
corrupt in the code. Often corruption in the compiled code is hidden
by VBA's use of flags to tell whether code has been compiled instead
of actually recompiling the whole thing. The result is that invalid
compiled code can exist in a project that throwse no errors when you
compile it.

The creation of the MDE throws away all the compiled code and starts
over from the canonical code, and will thus reveal all errors in the
canonical code. A decompile will do the same thing.

Why am I mentioning all of this? Because recompiling of code because
of invalid compilation is one of the causes of bloat.

Last of all, once you have non-shared front ends and have reduced
bloat to the bare minimum, you never need to compact the front end.
If something goes wrong, you simply replace it with a fresh copy.

Front ends are fungible.

Any particular front end file is completely expendable.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

MGFoster <[e-mail]> wrote in
news:oP**************@newsread3.news.pas.earthlink .net:

da********@gmail.com wrote:

As I indicated, it is a front-end/back-end split database. It
just happens that both the front-end and back-end reside in the
same directory on the server. I see your point that the compact
on close will only affect the front-end. It does appear, though,
that this compaction still is effective, as the frontend tends to
grow in size during the normal process of use. I figure that
this means temporary tables are being generated and then deleted
on the frontend in order to load a form or report. I have no way
of tracking this behaviour down, as I was not the one who
designed the app, and I wouldn't know where to start to look. My
preference is to keep it the way it is, and get the compaction to
work.

I believe you missed the point Rick Brandt was making - each user
should have a copy of the front-end on their PC (or work folder,
if you're using a VPN). It sounds like you have ONE front-end on
a server and ALL users are using that one front-end. This is a
no-no 'cuz it can lead to corruption.

Well, there's one point that suggest this is not the case. If they
have COMPILE ON CLOSE turned on for a shared front end, it would be
failing constantly.

This, of course, could be the cause of the failure to propagate the
name change, because once DB1.MDB is created, the original front end
file can't be deleted, since it's in use.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:26********************************@4ax.com:

I believe the cause of this is that Access renames the current
copy, then compacts it into a file with the old name, and finally
deletes the renamed original. I can't think of what a work-around
might be for this problem.

The way to avoid it is to not compact from the Access UI.

If you rename the original file, that name change should be
propagated by your file replication system. If you then compact that
file to the OLD name and delete the original (renamed) file, you
don't have problems with propagating the name change through your
replication system.

Of course, it may propagate shortcut changes to a deleted MDB, so it
may not be better.

But my suspicion is that the reason the file replication is failing
is *not* because the name change from DB1.MDB back to the original
file name is not propagating, but because the name change itself is
failing because the front end is shared and can't be deleted in
order to make room for DB1.MDB.

But I'm only speculating.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Capper 55]

<da********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I have stumbled upon an interesting issue with regard to compaction of
an access frontend database which resides on a DFS target that utilizes
FRS to mirror the sharepoint, and I was wondering if anyone knows a way
to overcome it.

For those who are unaware of DFS or FRS, DFS stands for Distributed
File System, and basically involves "mounting" various sharepoints that
reside on various computers underneath one special sharepoint. When
you access the mounted directory under the DFS share, you are invisibly
redirected to where the share really is. FRS stands for File
Replication System (I think. it could be Service, tho). If you
indicate that a particular mount point in the DFS share has two or more
target sharepoints, you can tell Windows to use FRS to ensure changes
to the files in the sharepoint are replicated to the other targets.
Both of these are built-in technologies of Windows 2003 server.

On to what I have observed. We have a frontend/backend Access 2003
database application residing on one of these FRS replicated
sharepoints, and have the option to compact the database on close
turned on. What I am seeing is that when the compaction occurs, the
frontend database is renamed to DB1.mdb, and this name change is then
replicated to the other servers. The name is never changed back again,
leaving the shortcuts that the users use to access the database broken.
I have turned off the compact on close option, and the effect stops,
so the issue is linked to compacting on a FRS replicated sharepoint. I
would like to turn the compaction back on, as the database tends to
grow rather large, but I do not want the database's filename to change.
Any ideas?

Sorry for all the bad advice you received in this thread, especially the bad
advice from "David. W. Fenton."

1. The presence of DB1 indicates a compact failure due to other users
accessing the database or an orphaned LDB file.

2. The Compact on Close option cannot be used reliably in a multi-user
environment, no matter if it is a file server or application server
environment.

3. It is OK to use a single MDE copy of the database front end on the
server as long as each user makes no changes to the file whatsoever. But
for maximum speed, stability and security, consider using Terminal Server or
Citrix if that is possible in your environment.

4. There is no known cure for Access file bloat. You must compact the file
manually after you have kicked everybody off the connection.

[David W. Fenton]

"Capper 55" <no****@nospam.net> wrote in
news:1121807122.df90d246334bede8f3243a3966afad70@t eranews:

Sorry for all the bad advice you received in this thread,
especially the bad advice from "David. W. Fenton."
Oh, Don! You need to do a better job disguising your posts!
1. The presence of DB1 indicates a compact failure due to other
users accessing the database or an orphaned LDB file.
Er, I said this in one of my posts.
2. The Compact on Close option cannot be used reliably in a
multi-user environment, no matter if it is a file server or
application server environment.
I said this in one of my posts.
3. It is OK to use a single MDE copy of the database front end on
the server as long as each user makes no changes to the file
whatsoever. But for maximum speed, stability and security,
consider using Terminal Server or Citrix if that is possible in
your environment.
This is blatantly wrong.
4. There is no known cure for Access file bloat. You must
compact the file manually after you have kicked everybody off the
connection.

This is blatantly wrong, too.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc