Compressing a .mdw file

  • Thread starter Thread starter Roger Hampson
  • Start date Start date
R

Roger Hampson

We have a .mdw file that has grown to 61MB. It is use from
06:00 to 20:00 every day by a large number of people. If
we copy it and compress it using Tools | Database
Utilities | Compress and Repair Database..., it creates a
perfectly usable .mdw file of 520K. However, we cannot
replace the old one until everyone has finished using it,
which is not practical as it needs doing every day. It
grows to over 3MB in a day.

We decided to compress it by writing a program and using

DBEngine.CompactDatabase SrcName:=DBName,
DstName:=NewDBName

then renaming the original to x.old and renaming the new
one to x.mdw.

When we try to use the new .mdw, we get the message "Not a
valid account name or password."

Any suggestions?

RH
 
Roger Hampson said:
We have a .mdw file that has grown to 61MB. It is use from
06:00 to 20:00 every day by a large number of people. If
we copy it and compress it using Tools | Database
Utilities | Compress and Repair Database..., it creates a
perfectly usable .mdw file of 520K. However, we cannot
replace the old one until everyone has finished using it,
which is not practical as it needs doing every day. It
grows to over 3MB in a day.

We decided to compress it by writing a program and using

DBEngine.CompactDatabase SrcName:=DBName,
DstName:=NewDBName

then renaming the original to x.old and renaming the new
one to x.mdw.

When we try to use the new .mdw, we get the message "Not a
valid account name or password."

Any suggestions?

RH
Click to expand...

I use this small snippet of code from an vb.net application to compact my
database
Maybe this give you an idea to solve your problem.

Dim jro As JRO.JetEngine

jro = New JRO.JetEngine

Dim OldName, NewName As String

OldName = Yourpath & "Mydatabse.mdb"

NewName = Yourpath & "MyTMPdatabse.mdb"

Try

Rename("Your path\Mydatabse.mdb","your path\MyTMPdatabase.mdb")

jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Password=Yourpassword;
User ID=Your ID;Data Source= " & Yourpath & "MyTMPdatabse.mdb ; ;Jet
OLEDB:System database=" & Yourpath & "YourWorkgroupFile.MDW;Jet
OLEDB:Database Password=Yourpassword;", _

"Provider=Microsoft.Jet.OLEDB.4.0;Password=Yourpassword;User ID=Your
ID;Data Source= " & Yourpath & "Mydatabse.mdb;Jet OLEDB:System database=" &
Yourpath & " YourWorkgroupFile.MDW;Jet OLEDB:Database
Password=Yourpassword;")



Kill(Yourpath & " MyTMPdatabse.mdb")

Regards
Jens Burup
 
Two things.

(1) As you may or may not know, a workgroup file is actually a Jet database.
And you generally can't create a working backup copy of a Jet database that
is currently open. (You have to wait for the database to close.) So, IMO,
there is no guarantee that using CompactDatabase on a currently-open
workgroup file, will necessarily produce a *working* compacted output file.
(In fact, I'm surprised that the CompactDatabase method did not object to
the source file being already open.) But I've never tried that myself, so
this is just an educated guess.

(2) Even if (1) is not a problem, you still can't overwrite the
currently-open workgroup file with the new (compacted) version. You have to
wait for the current file to close before you can overwrite it.

You seem to have a large time window (6pm - 8am) in which the file is
closed. Can't you schedule some kind of automated process to compact the
workgroup file in that period? It's hardly a 24/7 operation, from what you
say ...

HTH,
TC
 
TC

You have misunderstood my original post somewhat. I am
well aware that it is a normal Jet database, and I am
using the period when it is not in use, which is actually
between 8pm and 6am, to compress and replace the security
file while it is closed. However the VBA command
DBEngine.CompactDatabase will not produce a usable copy.

Further investigation has revealed that an error table
MSysCompactError is produced which contains the following
record

-1507, Could not find field 'OLDSID'., , MSysAccounts

When I look at table MSysAccounts in the original .mdw,
the OLDSID column is present, but blank. It is not present
on the compacted table.

RH
 
Jens

I am using the DAO equivalent of your code. However the
VBA command DBEngine.CompactDatabase will not produce a
usable copy.

Further investigation has revealed that an error table
MSysCompactError is produced which contains the following
record

-1507, Could not find field 'OLDSID'., , MSysAccounts

When I look at table MSysAccounts in the original .mdw,
the OLDSID column is present, but blank. It is not present
on the compacted table.

RH
 
Why don't you just create a little batch program with
"path to msaccess.exe" "path to mdw" /compact

and use your OS scheduler to run it at night?
 
Joan

We have tried that but we still get the same error file
created saying "Could not find field 'OLDSID'." Presumably
it is using the same code as DBEngine.CompactDatabase.

RH
 
I would not presume that at all; it works for me. Create a new workgroup
file for testing,
and add some groups and users to it.

Are you able to compact this one via the batch file?

It could be that your mdw is corrupt.
 
OLDSID is part of the SID obfuscation techniques that were added after
Access 97/Jet 3.x. So maybe you are compacting a Jet 4 workgroup file into a
Jet 3 version then trying to use it with Access 2k? Or vice versa?

Check the CompactDatabase parameters (I don't have Access here to check).

HTH,
TC
 
TC

The .mdw file was created from scratch when we moved over
from A97 to A2K. It might have been created using the DAO
3.51 DLL, although we have re-created it from scratch
since then. We are now using the DAO 3.6 DLL to compress
it. Will that cause any problems?

It now has too many Users and too many Groups to want to
do it again. Although it looks as if we might have to.

RH
 
Hi Roger

It does sound to me as if it is a Jet 3 vs. Jet 4 issue.

Jet 3 workgroup files do not contain the OLDSID column. Jet 4 ones do.
Clearly, when you do the compact, someone (Access or Jet) is looking for
that column - ie. expecting a Jet 4 file, not a Jet 3 one.

You're sure that you are running the dbengine.compactdatabase from A2k (not
A97), to compact a Jet 4 mdw (not a Jet 3 one)?

Maybe try specifying *all* of the compactdatabase parameters, explicitly.

Apart from that, I really can't think what else to suggest :-(

Anyone else got any ideas?

HTH,
TC
 
Back
Top