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********@gmai l.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********@gma il.com" <da********@gma il.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********@gmai l.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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQt05QIechKq OuFEgEQJX0gCfZz n57/xNPCYatkIK0VFhX 49smZUAoMBs
Qoo32bcKNdpHhMo EEZZF4vWy
=s9Li
-----END PGP SIGNATURE-----

[David W. Fenton]

"da********@gma il.com" <da********@gma il.com> wrote in
news:11******** *************@f 14g2000cwb.goog legroups.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********@gma il.com" <da********@gma il.com> wrote in
news:11******** *************@g 49g2000cwa.goog legroups.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******** ******@newsread 3.news.pas.eart hlink.net:

da********@gmai l.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.c om:

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