Compress On Close on server

[NetworkTrade]

Hi,

An Access2000 225M file is sitting on Windows Server 2003 for Small Business
Server and takes forever to compress - - very very long hang time when one
closes out of the file.

So I have had to remove the Compress On Close option.

Am assuming it is the server/network/desktop interaction - but that is just
a guess. Was wondering if others out there have advice on this scenario.
Gracias.

 

[susiedba]

the problem is that MDB is completely obsolete; and it shouldn't be
used by ANYONE.

SQL Server has 'autogrow' and 'autoshrink' turned on by default

-Susie

 

[Tom Wickerath]

You should definately be running a split database, with the front-end (FE)
installed on the local hard drive of each user. This way, the compact on
close option will only apply to each user's local FE file.

As for compacting the back-end (BE) of a split database, my recommendation
is to never attempt this operation over a network. Wait until all users are
out of the database (there are ways to lock new users from opening the file).
Copy the BE database to your local hard drive, do the compact and repair, and
then copy it back to the file server. Do this on whatever schedule that you
deem necessary.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi Aaron,

I see that you have taken to wearing a skirt these days.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[NetworkTrade]

thanks for the reply Tom (that first reply from Suzie/aka Aaron was off the
wall obviously...)

definitely wanted to split - - but the Split/LinkTable Managers not
installed in the original owner's install of Access2000 & owner can't find
their Office2000Professional CD in order to add them..... I don't have an
A2000 CD either - so wasn't able to get there.

Will keep note of your warning against compressing the -be over a network.
Lots of small users don't have actual Windows Server software - and just have
regular XP often with the Access/Office license...not sure if this would be
different experience - - thought maybe it was due to Windows Server software
not having/needing an Access license with perhaps the data transiting from
the server to the desktop license in order to compress/close - just not
sure....... in any case - lesson learned in not attempting to compress in
this situation but now one is going to have to keep an eye on the file size,
transfer from server to desktop and do some manual compression now and
then... I see no other alternative until they become able to split....

 

[David W. Fenton]

As for compacting the back-end (BE) of a split database, my
recommendation is to never attempt this operation over a network.

I've never had a single problem with such a thing. Given that a
compact doesn't overwrite the original file until the compact
completes, there's virtually no chance of corruption due to doing it
over a network connection. That is, the probability of corruption is
just as great during the copy operation as it is during the compact.

 

[Tom Wickerath]

David,

I have experienced failed compact and repair operations, where the only
thing I was left with was the new (now corrupt) database with the original
(good database) having been deleted. Microsoft even advises backing up before
one compacts. There's a reason they say that.

Networks introduce a greater probability of incomplete writes due to dropped
packets, versus the same write operation on a local hard drive. As far as I
know, Access does not perform any type of test of the integrity of the new
file prior to deleting the original. A check sum would obviously not work,
given the reduced size of the file.

As they say, YMMV (Your Mileage May Vary). I think you've just been luckier
than I have been.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi NTC,

You can actually split the database manually. There is no need to use either
the database splitter or table re-linking wizards. To split a database
manually, make a copy of it. To one copy, delete all queries, forms, reports,
macros, modules and DAP shortcuts. This becomes your new back-end (BE)
database. To the other copy, delete all tables (except for any local tables
that you may choose to keep in the front-end, for example, to store
relatively static data such as a list of States, or to store user
preferences). This becomes your new front-end (FE) file. In the FE file, use
File > Get External data > Link tables to establish new linked tables to the
BE file. You may want to have the BE in the correct shared folder prior to
doing this.

As for avoiding the use of the linked table manager, there is code available
on the MVPS web site for replacing this functionality:

Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm
and
http://www.mvps.org/access/tables/tbl0012.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi NTC,

I forgot to answer this part of your question:

Lots of small users don't have actual Windows Server software - and just have
regular XP often with the Access/Office license...not sure if this would be
different experience - - thought maybe it was due to Windows Server software
not having/needing an Access license with perhaps the data transiting from
the server to the desktop license in order to compress/close - just not
sure.......

Most of the work likely occurs on the local hard drive anyways, because
Access just sees a file share just like any other hard drive. I don't know
this for a fact, but I believe the data must do a round trip from the server
to the local installation of JET and then back to the shared folder where it
is re-written.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[David W. Fenton]

I have experienced failed compact and repair operations, where the
only thing I was left with was the new (now corrupt) database with
the original (good database) having been deleted. Microsoft even
advises backing up before one compacts. There's a reason they say
that.

I was just assuming a backup before the compact, or that you'd
compact to a different name.

Networks introduce a greater probability of incomplete writes due
to dropped packets, versus the same write operation on a local
hard drive.

This is equally likely in the copy operation.

As far as I
know, Access does not perform any type of test of the integrity of
the new file prior to deleting the original. A check sum would
obviously not work, given the reduced size of the file.

As they say, YMMV (Your Mileage May Vary). I think you've just
been luckier than I have been.

I think it's completely unnecessary if you've done the backup
beforehand. If you haven't, then, well, you're making a different
mistake.

 

[Tony Toews]

As for compacting the back-end (BE) of a split database, my recommendation
is to never attempt this operation over a network. Wait until all users are
out of the database (there are ways to lock new users from opening the file).
Copy the BE database to your local hard drive, do the compact and repair, and
then copy it back to the file server.

Whereas I have routinely compacted largish, 200 or 300 Mb MDBs, on the
server and have never had a problem.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tom Wickerath]

That's why I said YMMV, which, I believe, is a term that you use quite
frequently.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________