 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Security / August 2004Compressing a .mdw file
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 > 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 [quoted text clipped - 19 lines] > > RH 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 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 >-----Original Message----- > [quoted text clipped - 56 lines] > >. 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? > Jens > [quoted text clipped - 78 lines] >> >> .  SignatureJoan Wild Microsoft Access MVP 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 >-----Original Message----- >Why don't you just create a little batch program with [quoted text clipped - 66 lines] >>> >>> jro.CompactDatabase ("Provider=Microsoft.Jet.OLEDB.4.0;Password=Yourpassword; >>> User ID=Your ID;Data Source= " & Yourpath >> & "MyTMPdatabse.mdb ; ;Jet [quoted text clipped - 13 lines] >>> >>> . 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. SignatureJoan Wild Microsoft Access MVP > Joan > [quoted text clipped - 3 lines] > > RH 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 > 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 [quoted text clipped - 19 lines] > > RH 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 >-----Original Message----- >Two things. [quoted text clipped - 45 lines] > >. 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 > [quoted text clipped - 86 lines] > > > >. 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 >-----Original Message----- >OLDSID is part of the SID obfuscation techniques that were added after [quoted text clipped - 98 lines] > >. 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 > TC > [quoted text clipped - 126 lines] > >> >> > >> >> RH |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.