Access 2007 SP1

T

Tony WONG

After installation of SP1, the size of database is not reduced by compact
function in Access.

is it bug? Thanks.

Tony
 
T

Tony Toews [MVP]

Tony WONG said:
After installation of SP1, the size of database is not reduced by compact
function in Access.

is it bug? Thanks.
Click to expand...

No, I wouldn't expect a Service Pack to reduce the size of an MDB
after compacting.

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/
 
T

Tony WONG

Thanks Tony

let me make it more clear

Before installation of SP1, from time to time, i have to compact the accdb
database which grow from 1m to 10m. After compact, it go back to 1m.
After installtion of SP1, it stays on 10m
then i try to copy it from network drive to local drive. it can be reduced
to 1m. But the procedure is troublesome to me.

As i remember, i got similar problem before installation of SP1. but it is
NOT related to size.
when i compact accdb database at Network drive, it create database.mdb
(compressed) file. then i have to delete old accdb and ren mdb to accdb
manually.
when i compact accdb database at Local drive, it will have compressed accdb
only.

Any ideas? Thanks.

Tony
 
J

John W. Vinson

After installation of SP1, the size of database is not reduced by compact
function in Access.

is it bug? Thanks.

Tony
Click to expand...

A couple of questions:

- Are you sure that it SHOULD reduce in size? It might already be as compacted
as it will get.
- Have you encrypted the database, for example by implementing workgroup
security? Doing so will prevent compaction from changing the size (since much
of the waste space is in repeated blanks or zeros, and these will be replaced
by nonrepeating randomized characters when the database is encrypted).
- If you create a new database and use file... get external data... import,
does the result end up smaller than the original?

John W. Vinson [MVP]
 
T

Tony WONG

Hi John

Yes, i have to do it from days to days. otherwise the size of database
would be too big

No encryption.

the previous version of database is runnning under workgroup. Because of
slow access 2007, i have migrated the tables to SQL2000

The accdb contains mainly forms, queries and reports.

The accdb is shared by a few users on a network drive.

i have set a form to auto run once the database starts. i wonder it is
related to hindering the compact action. But the result seems same after i
disable the autorun of the form.

Any info i can provide. Thanks.

Tony
 
B

boblarson

You said:
The accdb contains mainly forms, queries and reports.

The accdb is shared by a few users on a network drive.
Click to expand...

This should NOT be used by multiple users. EACH user should have a copy of
the front end on THEIR machine.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________
 
J

John W. Vinson

the previous version of database is runnning under workgroup. Because of
slow access 2007, i have migrated the tables to SQL2000
Click to expand...

Well, of course, Access cannot compact your SQL2000 database; you'll need to
use SQL tools to do so, and the whole paradigm is quite different.
The accdb contains mainly forms, queries and reports.

The accdb is shared by a few users on a network drive.
Click to expand...

As noted... you really should NOT be sharing an accdb. I'd suggest that you
make an accde file; give each user their own copy; and if it starts to bloat,
*just delete it* and give them a fresh copy (there should be no data in it so
you aren't losing anything).

I find it peculiar that an accdb frontend is bloating to that extent - do you
use temp tables in your code? If you have complex queries, could they be
instantiated as SQL views or stored procedures that you link to, rather than
being run by Access? That should markedly improve performance, as well.

John W. Vinson [MVP]
 
T

Tony WONG

yes, but in my case, the users need to create / share query for sorting out
data as they wish

secondly the users are not familize with SQL language. they like access
(drag and drop query interface)

any way, thanks for your help
 
B

boblarson

Hate to break it to you but by having your users share an MDB frontend file,
you are greatly (and I mean GREATLY) increasing your risk that the frontend
will go corrupt. It isn't a matter of IF it will corrupt, but when. When
you share an Access file over the network and someone is in it and a network
disruption occurs (no matter how slight) BAM! you now have a corrupt file and
it may, or may not be recoverable.

If you haven't been doing it, be sure to back up that file at least DAILY so
you can recover in the event of corruption.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________
 
J

John W. Vinson

yes, but in my case, the users need to create / share query for sorting out
data as they wish

secondly the users are not familize with SQL language. they like access
(drag and drop query interface)
Click to expand...

It would be worth your effort to create some VBA code to a) let the users
create queries in the query grid, *in their individual frontends*, and b)
export that query to a shared resource of some sort so that everyone could
import the query to their own frontend.

I realize this increases the complexity of your app but you're really skating
on thin ice with this database. The fact that it's bloating so rapidly is a
bad sign right there.

How do these queries differ? Could Parameter Queries help, or are the users
changing the joins, changing the fields being included, etc.?

John W. Vinson [MVP]
 
T

Tony WONG

Thanks to all advices and reminders

i do realise the risk, so the data is saved at SQL and accdb is backuped
daily.

so far so good, the database is only shared by not more than 4 users

Thanks for your input.

it assist me very much. thx.
 
D

Dirk Goldgar

Tony WONG said:
After installation of SP1, the size of database is not reduced by compact
function in Access.

is it bug? Thanks.
Click to expand...


You mention elsewhere in this thread that the database is in a shared
network folder. Is this an offline folder? If so, the SP may be protecting
you from a serious bug. I don't know the details, but I seem to recall that
there were issues involving loss of data when compacting files in offline
folders. I wish I could remember the details -- with luck, someone who does
will jump in here. But if your database is stored in an offline folder,
maybe that's why Access is not compacting it.
 
T

Tony Toews [MVP]

Tony WONG said:
yes, but in my case, the users need to create / share query for sorting out
data as they wish
Click to expand...

I would suggest giving your users their own individual "sandbox".
That is create an MDB linked to all your tables and some sample
queries. Give each user thier own copy. Don't update it. Let them
do with it as they will.

What you may also want to do though is point them to a copy of your
backend MDB which you refresh on a nightly basis. This way if they
ever discover that button on the toolbar called Action queries and
decide to update data they are only updating a copy of the BE.

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/
 
Top