Compact & Repair

[Emily848]

Ever since updating to Access 2007 a database I created for work will not
compact. It has grown from 30MB when it was Access 2003 to currently 180MB,
and it continues to grow. Even though it seems like it is compacting and
repairing when I select that from the menu, the file size does not get any
smaller. The larger file size is slowing down running of the program.

The database runs off of a server and is accessed by about 9 users, and I
tried to troubleshoot by following microsoft's instructions for speeding up a
database that is run off of a server, but it did not help.

Has anyone else run into a non-functioning compact & repair in 2007? I'm
self-taught on Access, so any help would be greatly appreciated!

 

[Arvin Meyer [MVP]]

First, you need to fix the database. It cannot be run completely from a
server. I don't think that Microsoft recommends running a complete database
from a server only the tables. Here's what to do:

1. Create a new, empty database and import only the tables into it.

2. Put that database on the server

3. Map a drive to it.

4. From your workstation, create a new empty database and import everything
except the tables into it.

5. Link the front-end on your workstation to the tables on the server.

6. Make a copy of the new front-end and put it on the server.

7. Map every user to the same share as the database tables are.

8. Move a copy of the front-end on the server to each workstation.

All of your problems are solved and you will have greatly reduced the chance
of corrupting your database again. To be clear, users should NEVER share a
front-end. Users should not run a front-end from a server unless using a
Terminal Server, and then they still need a separate copy of the front-end.

 

[Windows Liveâ„¢]

Ever since updating to Access 2007 a database I created for work will not
compact. It has grown from 30MB when it was Access 2003 to currently
180MB,
and it continues to grow. Even though it seems like it is compacting and
repairing when I select that from the menu, the file size does not get any
smaller. The larger file size is slowing down running of the program.

The database runs off of a server and is accessed by about 9 users, and I
tried to troubleshoot by following microsoft's instructions for speeding
up a
database that is run off of a server, but it did not help.

Has anyone else run into a non-functioning compact & repair in 2007? I'm
self-taught on Access, so any help would be greatly appreciated!

 

[Emily848]

I'll try splitting the database again, it sounds like that is what you are
describing. I tried that once because Microsoft suggesting it as a
troubleshooting measure to speed up the database, but it didn't seem to help
and I was afraid of what else might not work, so I reverted to the back-up
copy.

Something doesn't seem right with the filesize though, it was only a 30MB
file in Access 2003, now it is 180MB and will not compact, and there is not a
significant amount of additional data or other information.

I guess no one else has run into problems with compact and repair not
seeming to do anything?

Thank you

 

[Emily848]

OK, I split the database, now there is a "_be" database that is 22MB with the
tables, and the database that users open up is still 180MB, still runs slow,
and still won't compact. Any help would be greatly appreciated. Also, now
that I've split it I'm concerned that there may be conflicts with the
database that users have open not updating for changes made by other users,
can that happen?

I'm hearing that it is important to split the database, but I'm not hearing
why, and I'm not sure that is related to the problem.

The only reason the database was 30MB in 2003 was because of some picture
files saved in one of the tables, without those picture files it was less
than 15MB. I don't get why it has grown to 180MB in Access 2007.

Any ideas? Thank you very much.

 

[Arvin Meyer [MVP]]

Let me give you some more information. When you split a database, you can
use a wizard, but I've been doing it simply before the wizard was available.
Here's what I do.

First I make 2 copies of the database. On copy 1 which will be the front-end
(FE), I delete all the tables, then I compact it. On copy 2, which will be
the back-end (BE), I delete everything except the tables, then I compact it.
Then I link the BE tables to the FE.

If either one won't get appreciably smaller, I decompile (read this on
decompiling)

http://www.mvps.org/access/bugs/bugs0008.htm

then I compact the database and import all the objects into a new empty
database, compact and compile. That cleans out any garbage. I then check all
the references. If files are not smaller, it is because of embedded images,
which eat tremendous amounts of space in a database.

I then move the BE to the server and relink the tables using the Link
Manager. Now a make a copy of the FE and move it to the server. I go to each
user's machine and import the FE from the server. If the drive mappings are
the same, you are done. If not, you'll need to relink each user to the BE.

Now why is all this necessary?

1. The bigger the file, the bigger the chance that a packet will drop on the
network and cause corruption, particularly if there's a marginal piece of
hardware.

2. Everything runs faster since only data must traverse the network.

3. There is very little chance of corruption if the FE is local.

4. Even if it did corrupt, the entire database won't corrupt, just that
user's FE, so all you need to do, it delete it and import another copy from
the server.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Emily848]

Thank you for the additional information. I'll try pulling all objects into
a new database for both the BE and the FE database that were created using
the wizard. I don't really like having to go to everyone's machine and
update them, right now they each have a link to the same database that is
stored on the server so I don't have to make changes on each persons machine.
If the FE database were stored on each machine, then I would have to replace
it each time I made any changes, right? I don't really want to have to do
that.

Also, I'll clarify that compact & repair appears to work, but the filesize
does not get any smaller, so it's not that it's not doing it, it just doesn't
seem to be doing any good. I am assuming that the slowing down of the
database is related to the database size growing, but I don't really know.
What I do know is that it worked absolutely fine in Access 2003 and the
database has not substantially changed, but this problem started immediately
when we updated to 2007. I was hoping others were having the same problem
and that it was a bug that would be fixed by MS, but that doesn't sound like
the case.

Yes, users have full rights, but no one knows how to do anything to the
database except push buttons and fill in data.

 

[gllincoln]

Hi Emily,

You don't have to go to everyone's machine - you might create a 'push tool'.

For instance, if you have net administrator's rights (and based on what I've
read, you should), then set up a folder right off of C: root on each system
where the front end (user part) is stored, and give yourself read/write
permissions to that folder.

Then you could create a batch file or a VBA procedure that systematically
looped through the user's PC's, overwriting the existing user front end with
the newest version, redirecting the results or error messages to a log file.

Alternately, you could create a batch file called GetNewDB.bat (or
whatever) and install that on the user's PC desktops - then when there is an
update, send them an email telling them to update their db front end by
clicking on that icon, (telling them to close the db front end first, if
they have it open).

The batch file could be something as simple as:

copy /Y \\myserver\c$\mydevfolder\current_version\myfrontend.mdb
C:\myapp\myfrontend.mdb

Note: the /Y switch suppresses the confirmation prompt regarding whether you
wish to overwriting the existing file - since you always do in this
instance.

Hope this helps,
Gordon

 

[Tony Toews [MVP]]

I don't really like having to go to everyone's machine and
update them, right now they each have a link to the same database that is
stored on the server so I don't have to make changes on each persons machine.
If the FE database were stored on each machine, then I would have to replace
it each time I made any changes, right? I don't really want to have to do
that.

I specifically created the free Auto FE Updater utility so that I
could make changes to the FE MDE as often as I wanted and be quite
confident that the next time someone went to run the app that it would
pull in the latest version. For more info on the errors or the Auto
FE Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on the Citrix server to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[rightcoast]

I specifically created the free Auto FE Updater utility so that I
could make changes to the FE MDE as often as I wanted and be quite
confident that the next time someone went to run the app that it would
pull in the latest version. For more info on the errors or the Auto
FE Updater utility see the free Auto FE Updater utility athttp://www.granite.ab.ca/access/autofe.htmat my website to keep the
FE on each PC up to date.

In a Terminal Server orCitrixenvironment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on theCitrixserver to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

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 athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Tony,

Using the AutoFEUpdater in a Citrix environment - you mention that a
directory named after the user is created on the server - are those
temporary? Would those directories be deleted once the user closed
the FE, or do they persist once created? The Citrix server does not
have a full copy of Access, only the runtime version - does that make
a difference when using your utility? Still struggling to understand
the Access/Citrix set-up (even after reading many articles on the
web), so any help will be much appreciated.

Cheers.

 

[Tony Toews [MVP]]

Using the AutoFEUpdater in a Citrix environment - you mention that a
directory named after the user is created on the server - are those
temporary? Would those directories be deleted once the user closed
the FE, or do they persist once created?

They would persist. However the IT staff could delete them at any
time and the Auto FE Updater would automatically create them next
time.

The Citrix server does not
have a full copy of Access, only the runtime version - does that make
a difference when using your utility?

No difference. You can even run an .exe with the Auto FE Updater.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I just want to thank you for this terrific piece of software.
But with VB6's demise, do you have any plans for offering a
dot-net version of the above ?

What in the *world* would porting such a relatively simple executable
to .NET accomplish?

 

[Tony Toews [MVP]]

I just want to thank you for this terrific piece of software.
But with VB6's demise, do you have any plans for offering a dot-net version
of the above ?
Just curious.

No plans on converting it to .Net. VB6 does everything I want it to.
It works in Vista and I expect it to work in Windows 7.

Besides who is going to pay me for it? <smile>

Mind you I keep meaning to get a code signing certificate. Just
haven't got around to it yet.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

What in the *world* would porting such a relatively simple executable
to .NET accomplish?

Well, it's not so simple actually. There are slightly over 4,000
lines of code in it. Granted lots of that is API calls of one sort or
another.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Well, it's not so simple actually. There are slightly over 4,000
lines of code in it. Granted lots of that is API calls of one
sort or another.

While 4000 lines of code is nothing to sneeze at, it's still
relatively small in comparison to a full-fledged application with a
UI that supports complex user interaction and so forth.

I just don't get why anyone who has a clue would think it would
benefit from porting to .NET. Or maybe it's me who doesn't have a
clue?