Hi,
I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database
while it is open regularly fails, seemingly at random. This is especially
annoying as I usually set the Compact on Close setting in Tools/Options to
true.
Has anyone else experienced this and is there a fix? Thanks for any help,
Jim F.
13  5069 
Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.
"Compact-on-close" what? It can only compact the FE.
All sensible software uses an FE/BE arrangement, where the main data is held
in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)
Why would you compact-on-close, presumably multiple times a day, every time
you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however slight,
of corrupting the database.
Do it properly and create a separate shortcut to compact the database, by the
administrator when everyone is out of it, and only as necessary.
Chris
"James Franklin" <ja************ @ntlworld.com> wrote in message
news:hb******** **********@news fep1-win.server.ntli .net... Hi,
I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database
while it is open regularly fails, seemingly at random. This is especially
annoying as I usually set the Compact on Close setting in Tools/Options to
true.
Has anyone else experienced this and is there a fix? Thanks for any help,
Jim F.
Hi,
Thanks for the input Chris. All my databases are FE/BE, however the FE
bloats everytime recordsets are created etc. Some of the files are only
opened once a day, and users are not Access experts, so seems logical to
Compact on Close. (If I put a shortcut, it will never get used...)
In addition, it not only happens then, but also sometimes when compacting
manually, which concerns me. I agree that maybe Compact on Close isnt the
best solution, but if it is in there, it should at least work!
Jim F.
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:uj******** ******@TK2MSFTN GP12.phx.gbl... Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.
"Compact-on-close" what? It can only compact the FE.
All sensible software uses an FE/BE arrangement, where the main data is
held in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)
Why would you compact-on-close, presumably multiple times a day, every
time you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however
slight, of corrupting the database.
Do it properly and create a separate shortcut to compact the database, by
the administrator when everyone is out of it, and only as necessary.
Chris
"James Franklin" <ja************ @ntlworld.com> wrote in message
news:hb******** **********@news fep1-win.server.ntli .net... Hi,
I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a
database while it is open regularly fails, seemingly at random. This is
especially annoying as I usually set the Compact on Close setting in Tools/Options
to true.
Has anyone else experienced this and is there a fix? Thanks for any
help,
Jim F.
Compaction of the FE >does< make sense. Normally, the queries will be in the
FE. When each query is compiled, Jet uses performance statistics from the BE
tables do determine (& save) an optimal "execution plan" for the FE query.
This plan is stored in the FE, not the BE.
When the BE is compacted, the performance statistics are recomputed. (That
is one of the benefits of doing a compact). However, in a FE/BE situation,
the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.
So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.
HTH,
TC
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:uj******** ******@TK2MSFTN GP12.phx.gbl... Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.
"Compact-on-close" what? It can only compact the FE.
All sensible software uses an FE/BE arrangement, where the main data is
held in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)
Why would you compact-on-close, presumably multiple times a day, every
time you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however
slight, of corrupting the database.
Do it properly and create a separate shortcut to compact the database, by
the administrator when everyone is out of it, and only as necessary.
Chris
"James Franklin" <ja************ @ntlworld.com> wrote in message
news:hb******** **********@news fep1-win.server.ntli .net... Hi,
I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a
database while it is open regularly fails, seemingly at random. This is
especially annoying as I usually set the Compact on Close setting in Tools/Options
to true.
Has anyone else experienced this and is there a fix? Thanks for any
help,
Jim F.
A database is never compacted when it is open. Even compact on close would
close the database before compacting it.
HTH,
TC
"James Franklin" <ja************ @ntlworld.com> wrote in message
news:hb******** **********@news fep1-win.server.ntli .net... Hi,
I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database
while it is open regularly fails, seemingly at random. This is especially
annoying as I usually set the Compact on Close setting in Tools/Options to
true.
Has anyone else experienced this and is there a fix? Thanks for any help,
Jim F.
Did you actually test this TC? I did.
Your post sounded to me like mumbo-jumbo mixed with fact, so what should I do?
I tested.
1) Access 2002, production s/w
2) FE (compacted) 38,137,856
3) BE (uncompacted) 4,513,792
(compacted) 4,272,128
4) Change the BE to a 450KB one, run the FE. After running the FE, in the
meantime suffering a complete computer failure requiring a repair disk:
FE: 38,178,816
5) Run EVERY stored query I have:
FE: 38,178,816
6) Change back to the 4+mB BE, run every stored FE query again:
FE: 38,309,888
These figures do of course support your assertion, theoretically. But...SOME
INCREASE.
As I asserted, these are not the sort of figures which require a
compact-on-close (many times a day?). They are the sort of figures suggesting
once every 6 months, so let's compromise on once-a-month. James quite rightly
says "but the customer will never do it" (quite rightly that's my experience
too). However, the BE is where most of the changes occur and therefore is in
much more need of regular compacting. So, how are they gonna do (the more
important) (unless you hassle them to do it, put it in large red letters on
every communication with the customer, no-no don't go that far :-) )
I await you posting your test results to justify your statements, as affects
practical purposes, TC.
"TC" <a@b.c.d> wrote in message news:1070333907 .286595@teuthos ... Compaction of the FE >does< make sense. Normally, the queries will be in the
FE. When each query is compiled, Jet uses performance statistics from the BE
tables do determine (& save) an optimal "execution plan" for the FE query.
This plan is stored in the FE, not the BE.
When the BE is compacted, the performance statistics are recomputed. (That
is one of the benefits of doing a compact). However, in a FE/BE situation,
the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.
So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.
HTH,
TC
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:uj******** ******@TK2MSFTN GP12.phx.gbl... Whyever would you set "Compact-on-close"? This is about the most useless
option MS ever invented.
"Compact-on-close" what? It can only compact the FE.
All sensible software uses an FE/BE arrangement, where the main data is
held in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)
Why would you compact-on-close, presumably multiple times a day, every
time you close the database? Compacting needs doing typically once a month or
whatever. Every time you compact, there is additional danger, however
slight, of corrupting the database.
Do it properly and create a separate shortcut to compact the database, by
the administrator when everyone is out of it, and only as necessary.
Chris
"James Franklin" <ja************ @ntlworld.com> wrote in message
news:hb******** **********@news fep1-win.server.ntli .net... Hi,
I have a number of databases in A2K, written on different machines with
different installations of Office. I have found that compacting a database while it is open regularly fails, seemingly at random. This is especially annoying as I usually set the Compact on Close setting in Tools/Options to true.
Has anyone else experienced this and is there a fix? Thanks for any help,
Jim F.
"James Franklin" <ja************ @ntlworld.com> wrote in message
In addition, it not only happens then, but also sometimes when compacting
manually, which concerns me.
Hell, that concerns me too. In some 500 or 600 sites, I get (at a guess) about
95% recovery from asking them to run repair/compact. On the BE (who cares
about the FE, they can reload that from CD)
The other 5%? My figures are guesswork of course. Perhaps once or twice a year
in that many sites, I need to take them aside and stress the importance of
backups and ARCHIVES. After trying all the usual repair facilities including
PK Solutions.
The absolute LAST thing I want them to do is repair/compact unnecessarily. And
certainly they must do it when everyone is out of it (this last should not
apply to an FE of course)
If your FE creates say temp tables (your post suggested it might, and I
sometimes do), Tony Toews has a suggestion to create a temp database for that
reason - temp tables. Personally, I know of the idea and reason for it, just I
never found it a big enough deal to be necessary. What exactly is your
problem? (bloat sizes, after doing what?)
Chris
To suppport TC,
I did some testing along these lines back in MSA97, and found
extensive bloat in the FE. Mind you that mdb was running trough
millions of records each day running reports
Once I discovered CompactOnClose I breathed a huge sigh of relief and
forgot about the issue. Maybe they fixed it in later releases.
Even if the bloat is minor, sooner or later, the users will hit the
limit either through performance degradation or hitting MaxFileSize.
My solution in Pre 200 days was to send out regular "Urgent" updates
to the FE these were usually nothing but a compacted FE, or at most a
few minor changes. these were always installed. To my knowledege, the
Compact Database option I set up in the start menu never was
(Who said I'm no good at social engineering :)>
Regards Greg Kraushaar
Wentworth Falls Australia
(Do not email - the reply address is a Spam spoofer)
(If you really must, remove all UCase and numbers)
On Wed, 3 Dec 2003 21:36:25 +1300, "Chris Mills"
<ph*********@cl eardotnet.nz> wrote: Did you actually test this TC? I did.
Your post sounded to me like mumbo-jumbo mixed with fact, so what should I do?
I tested.
1) Access 2002, production s/w
2) FE (compacted) 38,137,856
3) BE (uncompacted) 4,513,792
(compacted) 4,272,128
4) Change the BE to a 450KB one, run the FE. After running the FE, in the
meantime suffering a complete computer failure requiring a repair disk:
FE: 38,178,816
5) Run EVERY stored query I have:
FE: 38,178,816
6) Change back to the 4+mB BE, run every stored FE query again:
FE: 38,309,888
These figures do of course support your assertion, theoretically. But...SOME
INCREASE.
As I asserted, these are not the sort of figures which require a
compact-on-close (many times a day?). They are the sort of figures suggesting
once every 6 months, so let's compromise on once-a-month. James quite rightly
says "but the customer will never do it" (quite rightly that's my experience
too). However, the BE is where most of the changes occur and therefore is in
much more need of regular compacting. So, how are they gonna do (the more
important) (unless you hassle them to do it, put it in large red letters on
every communication with the customer, no-no don't go that far :-) )
I await you posting your test results to justify your statements, as affects
practical purposes, TC.
"TC" <a@b.c.d> wrote in message news:1070333907 .286595@teuthos ... Compaction of the FE >does< make sense. Normally, the queries will be in the
FE. When each query is compiled, Jet uses performance statistics from the BE
tables do determine (& save) an optimal "execution plan" for the FE query.
This plan is stored in the FE, not the BE.
When the BE is compacted, the performance statistics are recomputed. (That
is one of the benefits of doing a compact). However, in a FE/BE situation,
the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.
So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.
HTH,
TC
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:uj******** ******@TK2MSFTN GP12.phx.gbl... > Whyever would you set "Compact-on-close"? This is about the most useless
> option MS ever invented.
>
> "Compact-on-close" what? It can only compact the FE.
>
> All sensible software uses an FE/BE arrangement, where the main data is
held > in the BE database. Therefore, it's the BE which needs compacting.
> (Front-End/Back-End)
>
> Why would you compact-on-close, presumably multiple times a day, every
time > you close the database? Compacting needs doing typically once a month or
> whatever. Every time you compact, there is additional danger, however
slight, > of corrupting the database.
>
> Do it properly and create a separate shortcut to compact the database, by
the > administrator when everyone is out of it, and only as necessary.
>
> Chris
>
> "James Franklin" <ja************ @ntlworld.com> wrote in message
> news:hb******** **********@news fep1-win.server.ntli .net...
> > Hi,
> >
> > I have a number of databases in A2K, written on different machines with
> > different installations of Office. I have found that compacting a
database > > while it is open regularly fails, seemingly at random. This is
especially > > annoying as I usually set the Compact on Close setting in Tools/Options
to > > true.
> >
> > Has anyone else experienced this and is there a fix? Thanks for any
help, > >
> > Jim F.
> >
> >
>
>
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:O8******** ******@tk2msftn gp13.phx.gbl... Did you actually test this TC? I did.
Your post sounded to me like mumbo-jumbo mixed with fact, so what should I
do? I tested.
1) Access 2002, production s/w
2) FE (compacted) 38,137,856
3) BE (uncompacted) 4,513,792
(compacted) 4,272,128
4) Change the BE to a 450KB one, run the FE. After running the FE, in the
meantime suffering a complete computer failure requiring a repair disk:
FE: 38,178,816
5) Run EVERY stored query I have:
FE: 38,178,816
6) Change back to the 4+mB BE, run every stored FE query again:
FE: 38,309,888
These figures do of course support your assertion, theoretically.
But...SOME INCREASE.
As I asserted, these are not the sort of figures which require a
compact-on-close (many times a day?). They are the sort of figures
suggesting once every 6 months, so let's compromise on once-a-month. James quite
rightly says "but the customer will never do it" (quite rightly that's my
experience too). However, the BE is where most of the changes occur and therefore is
in much more need of regular compacting. So, how are they gonna do (the more
important) (unless you hassle them to do it, put it in large red letters
on every communication with the customer, no-no don't go that far :-) )
I await you posting your test results to justify your statements, as
affects practical purposes, TC.
"TC" <a@b.c.d> wrote in message news:1070333907 .286595@teuthos ... Compaction of the FE >does< make sense. Normally, the queries will be in
the FE. When each query is compiled, Jet uses performance statistics from
the BE tables do determine (& save) an optimal "execution plan" for the FE
query. This plan is stored in the FE, not the BE.
When the BE is compacted, the performance statistics are recomputed.
(That is one of the benefits of doing a compact). However, in a FE/BE
situation, the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.
So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.
HTH,
TC
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:uj******** ******@TK2MSFTN GP12.phx.gbl... Whyever would you set "Compact-on-close"? This is about the most
useless option MS ever invented.
"Compact-on-close" what? It can only compact the FE.
All sensible software uses an FE/BE arrangement, where the main data
is held in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)
Why would you compact-on-close, presumably multiple times a day, every
time you close the database? Compacting needs doing typically once a month
or whatever. Every time you compact, there is additional danger, however
slight, of corrupting the database.
Do it properly and create a separate shortcut to compact the database,
by the administrator when everyone is out of it, and only as necessary.
Chris
"James Franklin" <ja************ @ntlworld.com> wrote in message
news:hb******** **********@news fep1-win.server.ntli .net...
> Hi,
>
> I have a number of databases in A2K, written on different machines
with > different installations of Office. I have found that compacting a
database > while it is open regularly fails, seemingly at random. This is
especially > annoying as I usually set the Compact on Close setting in
Tools/Options to > true.
>
> Has anyone else experienced this and is there a fix? Thanks for any
help, >
> Jim F.
>
>
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:O8******** ******@tk2msftn gp13.phx.gbl...
(snip) Your post sounded to me like mumbo-jumbo mixed with fact,
My post was 100% fact.
I await you posting your test results to justify your statements, as
affects practical purposes, TC.
Sorry, ain't gonna happen. You can believe it, or not, as you choose.
TC
"TC" <a@b.c.d> wrote in message news:1070333907 .286595@teuthos ... Compaction of the FE >does< make sense. Normally, the queries will be in
the FE. When each query is compiled, Jet uses performance statistics from
the BE tables do determine (& save) an optimal "execution plan" for the FE
query. This plan is stored in the FE, not the BE.
When the BE is compacted, the performance statistics are recomputed.
(That is one of the benefits of doing a compact). However, in a FE/BE
situation, the query plans in the FE are >not< recreated against the updated
performance statistics, unless & until the >FE< is compacted.
So, FE queries do not actually benefit from the updarted performance
statistics in the compacted BE, unless/until the FE is compacted also.
HTH,
TC
"Chris Mills" <ph*********@cl eardotnet.nz> wrote in message
news:uj******** ******@TK2MSFTN GP12.phx.gbl... Whyever would you set "Compact-on-close"? This is about the most
useless option MS ever invented.
"Compact-on-close" what? It can only compact the FE.
All sensible software uses an FE/BE arrangement, where the main data
is held in the BE database. Therefore, it's the BE which needs compacting.
(Front-End/Back-End)
Why would you compact-on-close, presumably multiple times a day, every
time you close the database? Compacting needs doing typically once a month
or whatever. Every time you compact, there is additional danger, however
slight, of corrupting the database.
Do it properly and create a separate shortcut to compact the database,
by the administrator when everyone is out of it, and only as necessary.
Chris
"James Franklin" <ja************ @ntlworld.com> wrote in message
news:hb******** **********@news fep1-win.server.ntli .net...
> Hi,
>
> I have a number of databases in A2K, written on different machines
with > different installations of Office. I have found that compacting a
database > while it is open regularly fails, seemingly at random. This is
especially > annoying as I usually set the Compact on Close setting in
Tools/Options to > true.
>
> Has anyone else experienced this and is there a fix? Thanks for any
help, >
> Jim F.
>
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Mike MacSween |
last post by:
Is it?
|
by: Larry L |
last post by:
Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that works
great. Now after everyone's advice I split my database, so the data is in
a second (back-end) database with all the tables linked. However, now when
I close the database, it compacts the front end, since that's what's open,
and the back-end grows.
I now have to manually open and close the...
|
by: Trevor Hughes |
last post by:
Hello All
I have a database (Access 2000, running on Win 2000), which suffers from
bloat over a period of time. In order to solve the problem I set the
option to compact on exit. This however has caused a problem. The
permissions of the mdb file which are set to Everyone-Full control, are
reset when the database is compacted. The end result is the the users
get a message saying Access cannot locate the database. I can run it
with...
|
by: smitty mittlebaum |
last post by:
I have a strange problem that has reared its ugly head in the last few
weeks.
I have the option "Compact on Close" (Tools, Options, General tab)
enabled on my development machine. I did this because every time I
worked on my app it just grew and grew.
What started out as a 14Meg file kept growing into a monster of over 90
Megs!
System info:
Windows XP Pro with SP1
|
by: Mark |
last post by:
Hi all,
I have a Access 2002 FE/BE scenario where the BE is stored on a network
drive. Due to the amount of importing/deleting, the database bloats quite a
lot. UP until now, I have been booting the users out of the database and
manually compacting it. What I am trying to do is automate the process so I
don't have to worry about it. My VB skills are few and far between so this
is proving somewhat of a challenge. Because I am trying to...
| | |
by: robert demo via AccessMonster.com |
last post by:
In my startup routine, I have the following code:
s = CLng(FileLen(filespec) / 1000000)
If s > 5 Then
'FIRST, BACKUP THE FRONT END
If BackupFrontEnd = False Then
Exit Function
End If
|
by: G Gerard |
last post by:
Hello
The more I use an application ( an mdb) created using MSAccess I notice
that the Byte size of the application keeps on increasing. Once in a while I
do a Compact and Repair Database (under Tools in MSAccess) on the
application and the size then returns to normal.
|
by: Ron |
last post by:
New discovery.
If I take a perfectly good database, and "compact/repair" on it with Access
2000 (seems to be at multiple sites--I've tried it with my system here, at
another office on an entirely different network), it damages the file
somehow. The user's machine that did the compact/repair can see the file
fine. But any networked user can't get in. I can double click on a good
database file from any user (over the network) and it...
|
by: Neil |
last post by:
I would like to compact on close only if the database size goes over a
certain amount, rather than each time. Thus, I'd like to check the file size
and then perform the compact through code as the mdb's closing. Is that
possible?
I suppose one option would be to set the Compact On Close option in the
switchboard's On Close event, and then clear it whenever the database is
opened. That would probably work. But I'd prefer a cleaner...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
