MDW file size

[Richard Walker]

Hello,

I am using a Security Group file to secure about 10
Applications with between 1 - 30 users for each of the
applications. I notice though that my MDW file size keeps
growing It's now upto 60 MB, however if I compact and
repair this the size drops to about 150 KB and then starts
to grow again as time goes by


Anyone know why this happens?

 

[TC]

Hello,

I am using a Security Group file to secure about 10
Applications with between 1 - 30 users for each of the
applications. I notice though that my MDW file size keeps
growing It's now upto 60 MB, however if I compact and
repair this the size drops to about 150 KB and then starts
to grow again as time goes by


Anyone know why this happens?

Are any of your applications repeatedly creating & deleting users or
groups through code (CreateUser etc.)?

TC

 

[Richard Walker]

-----Original Message-----
"Richard Walker" <[email protected]>

wrote in message [email protected]>...

Are any of your applications repeatedly creating & deleting users or
groups through code (CreateUser etc.)?

TC
.

No all the administartion of the workgroup is done
manually and does not happen often, say once a week. In
the code of the databases I only reference the security
file to see which group they are a member of, this is done
at startup and I set a flag to indicate which group they
are a member of.

 

[david epsom dot com dot au]

On other rare possibility for any database file that
is growing wildly is file corruption.
To exclude this possibility, delete and re-create
the MDW file.

(david)

 

[Guest]

We've done this a number of times but it still keeps
growing. With the number of people in the security file,
it takes some time to recreate it now.

 

[TC]

You say: "In the code of the databases I only reference the security
file to see which group they are a member of,"

The only other thing that I can think of, is that tbere might be
something wrong with that code. Very unlikely, but worth a quick
check. It shouldn't be more than a few lines. Post it here, for us to
check.

HTH,
TC

 

[Joan Wild]

I am using a Security Group file to secure about 10
Applications with between 1 - 30 users for each of the
applications. I notice though that my MDW file size keeps
growing It's now upto 60 MB, however if I compact and
repair this the size drops to about 150 KB and then starts
to grow again as time goes by

I have noticed the same thing - I just compact the mdw occasionally.

 

[Richard Walker]

TC

Here is the main code

Public Function is_InGroup(strGroup As Variant) As Boolean
Dim User1 As User
Dim intI As Integer
is_InGroup = False
Set User1 = owner_ws.Users(CurrentUser())
For intI = 0 To User1.Groups.Count - 1
If User1(intI).Name = strGroup Then
is_InGroup = True
Set User1 = Nothing
Exit Function
End If
Next intI
Set User1 = Nothing
End Function

owner_ws is set to the current workspace

It is called with

DMSAdmin = is_InGroup("DMSAdmin")

DMSAdmin is a boolean variable

Thanks
RW

 

[TC]

Hi Richard

Three things.

(1) There's nothing in your code that would cause the MDW to bloat, as
far as I can see. :-(

(2) You could make that code much simpler and slightly more efficient:


(untested)

Public Function is_InGroup(strGroup As String) As Boolean
dim s as string
on error resume next
s = dbengine(0).users(currentuser).groups(strGroup).name
is_ingroup = (err.number = 0)
exit function

That code basically says: "go to the current workspace, find the
workspace users, go to the current user, find that user's groups, go
to the specified group, & get its name." If that >works<, then, that
user must be in that group. No need for a loop etc.

(3) Regarding the bloating issue, it has been asked a few times over
the years, but not satisfactorarily answered AFAIK. I've had another
thought, on how you could proceed. Compact the wgf and take a copy.
Then run for a few days (or whatever) until it has bloated
substantially. Then take a second copy. Send both copies to an Access
data recovery service, & ask them to find out what has changed
internally within the file. That might give them an idea, as to what
is causing that change. I have the knowledge to do this myself, but I
do not offer such services publically, & I have enough of my own work
at present! So you would have to contact a commercial service & see
what they'd charge. PK Solutions is sometimes mentioned here, maybe
they would take it on.

HTH,
TC

 

[david epsom dot com dot au]

Your mileage may vary, but I've been trying to avoid
deliberate exceptions. In A97 I could demonstrate a
(rare) memory leak: In A2K I got GPFs, and too many,
too often.

For example, I had to replace this code:
On Error Resume Next
UpdateDiaryNote
(...qdf.execute...)

because the GPF hid the fact that A2K was having a
problem with a (legal) parameter query.

(david)

 

[Richard Walker]

We converted the Access apps from Access 97 to Access
2000, although the security file was built from scratch.

When we ran the apps under Acc 97 we never noticed this
bloating phenomenon with the security file. Is it an
Access 2000 problem?

RW

 

[TC]

Sorry, you've lost me there. What did you replace that code with, & why?

Cheers,
TC

 

[david epsom dot com dot au]

A security file is a Jet database: it will always grow
a bit when used. Excessive database bloat is associated
with some versions of Jet 4.0, particularly with shared
databases. (You should make sure that all users have the
most recent service pack for Jet). I would also recommend
that you use a separate security file when doing any
design work on the application database.

But Joan has indicated that the growth you have seen is,
if not normal, at least not unexpected, so that may be
the best you can do.

(david)

 

[david epsom dot com dot au]

This code was present because I wanted to ignore
exceptions:
On Error Resume Next

When I changed to A2K, I got an unexpected exception
because a parameterised QDF would not allow me to
set a (memo or date) field under certain conditions.

Because this was an exception caused by a consistent
bug in A2K, it was repeatable and easy to demonstrate.

Because it was repeatable and easy to demonstrate,
it was easy to demonstrate that it caused a GPF under
On Error Resume Next.

I had to modify the QDF and code to avoid the GPF/
Exception.

This was not the only place that gave us GPF's on
exceptions in A2K: just the easiest to fix.

Since the function of 'on error resume next' in this
case was to give me a GPF rather than an unhandled
exception, it was worse than useless.

Since it was worse than useless, I removed it.

Because I removed 'on error resume next', I had to
further modify the code to avoid other exceptions.

Now I just do it that way from the design stage: I avoid
deliberate exceptions.
This also makes it easier to debug code with 'break on
all errors'.


(david)

 

[TEST]

Sorry, I still don't understand.

Is this what you're saying:

1. A parameterized QDF worked fine in earlier versions of Access.
2. In A2k it started getting unexpected exceptions.
3. You added an On Error Resume Next statement to handle that.
4. But the On Error Resume Next statement would GPF!
5. So you had to remove the On Error Resume Next statement, and recode the
parameterized query to stop the error occurring.
6. You now avoid all On Error Resume Next statements in A2k, because they
can GPF.

Is that correct?

I need to be sure what you're saying, cos I haven't tested my main
application much in A2k.

Cheers,
TC

 

[david epsom dot com dot au]

6. You now avoid all On Error Resume Next statements in A2k,
because they can GPF.

Yes. It's not a terrible burden (computers are fast enough
now to handle code loops like that which started this discussion)

And I'm not obsessive about it: I don't code in deliberate
exceptions, but we still have plenty of places where they
haven't caused any problems - including places that run every
time and all the time in our main application.

Again, this thread would not have started if this coding practice
caused a major, consistent problem. I'm not saying that your
code is going to have problems on conversion to A2K.

But our code did: it was much more work for us to port from
A97 to A2K than it was to port from A2.0 to A97, and one of
the reasons was because A2K sometimes crashes on exceptions.

(david)

 

[Richard Walker]

Exactly my conclusion.

My problem is that we have a Task Manager running 24/7
which uses the security file. Therefore it is not possible
to compress it because it is always in use. I have now set
the Task Manager to use a different security file. One of
the overnight tasks for the Task Manager will therefore be
to compress our main production security file. Problem
solved, I hope.

RW

 

[TC]

Ok. Thant's news to me! I'll need to grab more time with A2k & do more
testing.

Cheers,
TC

 

[JacquesWvdH]

I encountered the "growing .mdw-file" problem too.

I am using access 2002, the application had an mde-frontend and a
secured backend.

There are less then 100 logins per week, Only rarely a user is added or
changed.
People can change their password, but I am sure they make little use of
that.

If I try to compact the mdw file
*
....
Call compactDB(strFolder(CurrentDb().Name) & "kbformat.mdw")
....

Public Sub compactDB(strDB As String)
On Error GoTo HandleErr
Dim strTmpFile As String
If (Len(strDB) > 0) Then
If (Dir(strDB) <> "") Then
strTmpFile = strFolder(strDB) & "mag.weg"
killIfExists (strTmpFile)
Debug.Print STR_MODULE & ".compactDB " & strDB & " naar tijdelijke file
= " & strTmpFile
DBEngine.compactDatabase strDB, strTmpFile
killIfExists (strDB)
Name strTmpFile As strDB
End If
MsgBox strDB & " gecompacteerd "
End If
ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, STR_MODULE & ".compactDB"
Resume ExitHere
End Sub
*

I get an error 3356: db already open.
So, I compact manually, decreasing the file size to 160 Kb. In less
then a week the mdw file then grows to 1.7 Mb.
In the mean time, the mdb-backend hardly increased in size and after
compression the difference in size was nil.
People use this application moslty for data entrance!!

I only noticed the problem because for a reason unknown to me the
execution speed had slowed down dramatically.
I don't know if speed has anything to do with the mdw-filesize,
however.

The questions are:
- why does the mdw-file grow so fast?
- has mdw-file size anything to do with execution speed?

Looking foreward to getting answers
Jacques