.mdw file size

[Rick]

I have noticed the system.mdw file size has grown to
almost the same size of the accompanying database without
any significant changes to the workgroup or group
permissions.

Is this event something I should be concerned about?

Any way to compact the .mdw file?

 

[TC]

Not sure why it would grow, unless you are creating & deleting lots of
users, groups, or user/group memberships (manually, or via code).

The workgroup file is a Jet database, so you should be able to compact it
using the dbengine CompactDatabase method, or the compaction option of
Access. I personally would go for the former, because using the latter will
add some Access system objects to the workgroup file. (It usually does not
have those objects.)

So here's what I'd do.

- close Access;
- copy the workgroup file (W) to a new name (N);
- open Access;
- run some code that uses the dbengine compactdatabase method to compact
file N;
- close Access;
- rename W to some backup name;
- rename N to W.

That should work.

HTH,
TC

 

[Joan Wild]

The workgroup file is a Jet database, so you should be able to compact it
using the dbengine CompactDatabase method, or the compaction option of
Access. I personally would go for the former, because using the latter will
add some Access system objects to the workgroup file. (It usually does not
have those objects.)

Such as? I've never seen this.

 

[TC]

Such as? I've never seen this.

I'm thinking of things like the forms, reports, scripts (macros) & modules
containers. Those are all created by Access. They do not exist in a simple
database created by CreateDatabase.

If you create an empty database with CreateDatabase, then open it in Access,
you'll see a brief message "Verifying system objects" while Access creates
those container objects. If you check the db size before & after opening it
with Access, you'll see that its size has increased, reflecting the addition
of those extra objects.

Cheers,
TC

 

[Joan Wild]

I'm thinking of things like the forms, reports, scripts (macros) & modules
containers. Those are all created by Access. They do not exist in a simple
database created by CreateDatabase.

If you create an empty database with CreateDatabase, then open it in Access,
you'll see a brief message "Verifying system objects" while Access creates
those container objects. If you check the db size before & after opening it
with Access, you'll see that its size has increased, reflecting the addition
of those extra objects.

But I don't follow what that has to do with compacting a mdw using the
compact option.

 

[TC]

But I don't follow what that has to do with compacting a mdw using the
compact option.

Workgroup files are created (by jet) without any of those container objects.
(Just as a database created by CreateDatabase is created without any of
those container objects.)

Opening a workgroup file in Access (for the purpose of compacting it), will
add those objects, which are not relevant to, or required for, the correct
functioning of the workgroup file.

I'm not saying this will cause a problem. In fact, clearly it doesn't,
because people >do< compact workgroup files, using Access. I'm just saying
that given the choice, I would rather not add those irrelevant Access
objects to the workgroup file.

So, given the choice, I personally would prefer to use compactdatabase,
which will not add those objects. But I would have no problem using Access,
if that was the only method available to me (for some reason).

Cheers,
TC

 

[Joan Wild]

Workgroup files are created (by jet) without any of those container objects.
(Just as a database created by CreateDatabase is created without any of
those container objects.)

Opening a workgroup file in Access (for the purpose of compacting it), will
add those objects, which are not relevant to, or required for, the correct
functioning of the workgroup file.

I'm not saying this will cause a problem. In fact, clearly it doesn't,
because people >do< compact workgroup files, using Access. I'm just saying
that given the choice, I would rather not add those irrelevant Access
objects to the workgroup file.

So, given the choice, I personally would prefer to use compactdatabase,
which will not add those objects. But I would have no problem using Access,
if that was the only method available to me (for some reason).

I'm not arguing with you TC, just trying to understand. I wonder if this is
the case if you don't open it in Access i.e. you can compact it without
opening it first.

 

[TC]

I'm not arguing with you TC, just trying to understand.

Sorry Joan, I did not mean to take that tone. I was actually trying to sound
as non-argumentative as I possibly could! It just goes to show how difficult
it is to communicate accurately, without being face to face.

I wonder if this is the case if you don't open it in Access i.e. you can compact it without
opening it first.

Good question! I'm not sure, so I'll check that out this week-end, & post
back on Monday. (Today is Friday where I am).

Let's summarize where we currently are. In this discussion, it's actually
not relevant whether we are discussing a workgroup information file, or a
database that has been created with the CreateDatabase method. I'll call
both of those, a "file".

1) when the file is first created (by the workgroup administrator or by
CreateDatabase), the Access objects do not exist;
2) if you open the file with OpenDatabase, the Access objects are not added;
3) if you compact the file with CompactDatabase, the Access objects are not
added;
4) if you open the file with >Access<, Access immediately creates the Access
objects if they do not already exist.
5) Your question is, what happens if you open >Access< (not the file), then
use the compact option to compact the file. I will check that out.

Cheers,
TC

 

[TC]

Sorry Joan, I did not mean to take that tone. I was actually trying to sound
as non-argumentative as I possibly could! It just goes to show how difficult
it is to communicate accurately, without being face to face.
can
compact it without

Good question! I'm not sure, so I'll check that out this week-end, & post
back on Monday. (Today is Friday where I am).

Let's summarize where we currently are. In this discussion, it's actually
not relevant whether we are discussing a workgroup information file, or a
database that has been created with the CreateDatabase method. I'll call
both of those, a "file".

1) when the file is first created (by the workgroup administrator or by
CreateDatabase), the Access objects do not exist;
2) if you open the file with OpenDatabase, the Access objects are not added;
3) if you compact the file with CompactDatabase, the Access objects are not
added;
4) if you open the file with >Access<, Access immediately creates the Access
objects if they do not already exist.
5) Your question is, what happens if you open >Access< (not the file), then
use the compact option to compact the file. I will check that out.

Cheers,
TC

Hi Joan

I checked this out, wrote a detailed reply, then promptly forgot to bring
the reply to this PC! (I have Access on one PC, the net on others, but
never the twain shall meet...) So here it goes again, from memory.

In Access 97, the extra (Access) objects in question are the MSysModules &
MSysModules2 tables, and the Forms, Reports, Scripts, Modules & SysRel1
containers. (Other versions will be slightly different.)

I've established that when you follow the sequence in (5) above, the extra
Acces objects >are not< added to the file.

That is not what I expected! However, on reflection, it does make sense, for
the following reasons.

When you open a file in the Access UI, Access obviously does not know what
you plan to do with it. You >might< just plan to compact it, but you might
also be planning to do others things: for example, creating new forms. So
Access must ensure that the file is ready for any legal operation. For that
reason, it must add the extra Access objects >now<, if they do not already
exist, before it makes the file available in the UI.

But when you open Access, then use Toolsatabase Utilities:Compact
Database, Access knows that the target database >will not< be opened in the
Access UI, by that particular function. So, there is no compelling need to
add the Access objects yet. I imagine that in this case, Access just calls
dbengine compactdatabase to compact the specified database. And
compactdatabase does not add the Access objects; it would not even know what
those objects >are<.

So, here's my summary as it pertains to compacting workgroup files.

<INFO>

"You can compact a workgroup information file in one of three ways.

(1) Through code, using the CompactDatabase method.

(2) From Access, by opening the file in Access, then using Tools : Database
Utilities : Compact Database.

(3) From Access, by opening Access (not the file), then using Tools :
Database Utilities : Compact Database.

Method (2) will add some unnecessary Access objects to the workgroup file.
These will not cause any problems. However, they do take space within the
file, and they are not required for proper operation of the file. You can
avoid adding these objects, by using method (1) or (3) instead of method
(2).

Note: you can not compact the workgroup file that is currently in use by
Access. Instead, close Access, take a copy of that file, compact the copy,
then rename the old one "out" and the new (compacted) one "in".

</INFO>

Of course, A2k muddies these waters by storing the whole project in one big
blob. This raises issues as to what is compacted: the Jet structures, the
content of the big blob, or both? But regardless of that, the following fact
will never change, in any current or future version of Access, IMO: Access
must add at least one new container to a "raw" Jet database, in order to
store its own information. Opening a "raw" Jet database in the Access UI,
will automatically add those objects. Those objects are only required by
Access. They are not required for the correct operation of a "raw" Jet
database. Examples of "raw" Jet databases include, workgroup information
files, and databases created by the DbEngine CreateDatabase method."

As they say: Alert the Media !!

Cheers,
TC

 

[TC]

Hi Joan

Have just replied to my previous reply.

Cheers,
TC

(snip)

 

[Joan Wild]

Great, you confirmed what my testing had found. I must admit that I've
never compacted via method 2, although I certainly remember others
recommending that method. I wonder if there is greater chance of corruption
of the mdw file with these extra objects present.

As for compacting the current workgroup in use, I've only ever compacted
secure mdw files, and knew you had to do that using system.mdw. I don't
think I've ever compacted system.mdw.

 

[TC]

Hi Joan

I've been off for a few days, hence the late reply.

I doubt that the extra objects would increase the risk of corruption. It's
just the pedant in me, that does not want them there!

Cheers,
TC