Code for Compacting Database…

Topic

New Reply

Hi there… I am using Access ’97 and trying to write an event procedure to compact my database files…
I am using the DBEngine.CompactDatabase method… Does anyone know why I would be getting error message “Invalid argument”, Run-time error ‘3001’ on this line?:
DBEngine.CompactDatabase DBName, NewDBName
I did define and set the values of the two variables? …I don’t get it… Three different references I have checked shows using this syntax… Any help would be appreciated…

Oh ya… One other thing… I notice that most often in the books I am reading, etc… the database files are compacted to a different file… (I’m assuming this is the same as making a backup copy, but condensed…) …What I want to know is whether I may encounter problems when I compile the actual file itself… (In case I’m not making any sense… I’ll give an example…)
If I have a database file:
DBFile.mdb 278MB’s
and I compact it to a different name… I then have…
DBFile.mdb 278MB’s and
DBFileBU.mdb 14MB’s
Obviously I was compacting the file because I wanted to increase my available disk space … Not at all accomplished by this task…
So I have been compacting the file itself as well… giving me:
DBFile.mdb 14MB’s
DBFileBU.mdb 14MB’s
Does anyone anticipate my running into problems with this?
Thanks for your time… …Have a great Friday everyone!

Reply | Quote

Replies

Take a look at the compacter add in from the access web:
http://www.mvps.org/access/modules/mdl0030.htm

Reply | Quote

Thanks Bart… I Looked…downloaded… unzipped… added the file to the add in list in access… and when I click on Compact and Open… or Compact and Close (from the addin list)… it runs through items for a few seconds and then comes up with “invalid argument, ‘3001’ …. Any idea what’s going on?

Reply | Quote

Alexya:
I have had problems with compact when there are errors in underlying code. You might want to compile all modules to check for errors. It’s also a good idea to run the database through jetcomp.exe one time, Microsoft claims it does a better repair job than compact/repair.

Reply | Quote

You can’t compact the database to itself. You must compact to another filename and then kill the old file and rename the new one. Even when you do a compact from the menu with the database open, that is what goes on behind the scenes.

Reply | Quote

Thanks Brian… I’ll try that and see how it goes…

Thanks a million Charlotte!! …That makes complete sense now… …I am just one of “those” people who NEEDS to UNDERSTAND things that make no obvious sense… LOL …I appreciate the clarification…

Reply | Quote

Well I’ve tried everything I can think of… I even sent the code to another person I know who works on Access and it works for her… ????… If I run my code… or the Compactor Add-in that Bart told me to try… I get “Invalid Argument ‘3001’” errors… I did run a repair on all the files… No change when I run the code though… Anyone have any more ideas for me?

Reply | Quote

Hi Alexya,

I use a seperate database to compact some other databases.
It has the path and name of the databases to compact in a table and loops through the records in the table.
Sorry comments are in dutch, but the code is pretty clear:

Sub CompactDatabase()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim tmpDbNaam As String
Dim Fout As Boolean

‘***************************************************
‘Initialisatie van de loop om door de verschillende
‘databases heen te wandelen.
‘***************************************************
Set db = CurrentDb
strSQL = “SELECT * FROM tblDatabases”
Set rs = db.OpenRecordset(strSQL)
Fout = False

‘***************************************************
‘Loop om door de verschillende databases heen te wandelen.
‘***************************************************
While Not rs.EOF
‘***************************************************
‘Controle of de database nog wel bestaat, anders overslaan.
‘***************************************************
If Dir(rs![Database]) “” Then
‘***************************************************
‘Tijdelijke database op de plaats van de te comprimeren
‘database plaatsen. Naam opmaken.
‘Als de tijdelijke database nog bestaat, dan verwijderen.
‘***************************************************
tmpDbNaam = Mid$(Trim(rs![Database]), 1, Len(Trim(rs![Database])) – 3) & “tmp”
If Dir(tmpDbNaam) “” Then Kill tmpDbNaam

‘***************************************************
‘Comprimeren van de database.
‘Een database die in gebruik is kan niet gecomprimeerd worden!
‘***************************************************
On Error GoTo ErrorHandler ‘Voor het geval de database in gebruik is!
DBEngine.CompactDatabase rs![Database], tmpDbNaam
On Error GoTo 0 ‘Uitzetten foutafhandeling

If Not Fout Then
‘***************************************************
‘Vervangen van de originele versie door de gecomprimeerde versie.
‘Alleen als de gecomprimeerde bestaat.
‘***************************************************
If Dir(tmpDbNaam) “” Then
Kill rs![Database]
Name tmpDbNaam As Trim(rs![Database])
End If
Debug.Print rs![Database] & ” is gecomprimeerd.”
Else
Fout = False
End If
End If
rs.MoveNext
Wend
‘***************************************************
‘Alle open eindjes netjes afsluiten.
‘***************************************************
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub

‘***************************************************
‘Foutafhandeling als database niet gecomprimeerd kan worden.
‘***************************************************
ErrorHandler:
Debug.Print rs![Database] & ” Kon niet gecomprimeerd worden.”
Fout = True
Resume Next
End Sub

Reply | Quote

Just a shot in the dark but do you have the Microsoft DAO 3.51 Object Library selected in references. That’s where DBEngine is found. Mind you, you’d probably have an awful lot of other problems if it wasn’t.
The only other thing I can think of is re-installing Access.

Reply | Quote

Hi Brian… Yep… It’s checked… I thought of checking that a while back… Thanks for the idea though… ….Re-installing Access huh?… I’m hoping not to do that… (I wouldn’t mind if I could do it myself, but I’d have to get the desktop support people up here (they keep the CD’s darn it!)… Try explaining to them that we need to uninstall and reinstall…. Not fun! ) …I’ll do it as a last resort…

Hello Bart… I tried your code (Thank you very much by the way! ) …I was actually doing the same thing!… **from a table that contains the paths to the mdb files**… Your code compiled fine, ran, but there was no change in the size of the files… so I stepped through it and it went to the error handler right after the DBEngine.CompactDatabase line, for each record… What does ” Kon niet gecomprimeerd worden.” mean? … Just curious…

I’m going with this code not working!…Just that one line… (the most important!)… No matter what I do, simple or complex to the other parts of the process…it’s fine EXCEPT that one line!… DBEngine.CompactDatabase blah,blah,blah….

(Sorry…had to vent for a second there!)
I’ll figure it out…Eventually…

Any more suggestions or ideas would be greatly appreciated! Have a great night everyone!

Reply | Quote

Have you tried decompiling and recompiling the database you’re running this from? To decompile, select Run from the Windows Start menu, enter the full path and filename of the MsAccess.exe file (you can drag the file from the explorer window onto the run dialog, and you need quotes aroung the path if there are spaces in it), add a space and then the full path and filename of the database you want to decompile (again, you can drag it onto the dialog), then another space and “/decompile” (without the quotes). Once you’ve decompiled the database, compile and save all modules and then compact and repair.

Reply | Quote

Good Morning Charlotte…

Thanks for the thought… I’ll try that… Will I need to do that for each database file I am going to try and put in the table for automated compacting? …I am assuming so… but thought I should check.. just in case you only meant that I should do it to the Compact.mdb file itself… Thanks again…

Reply | Quote

Do it to whatever database is throwing odd errors, which in this case sounds like your Compact database.

Reply | Quote

Hi Alexya,

Couldn’t be compacted, that is the meaning of “Kon niet gecomprimeerd worden.”.
I am dutch, I copied the code from an application I wrote for one of customers in Holland, that why alle comments and error messages are in dutch.

One more thing about compacting databases.
DBEngine.CompactDatabase olddb, newdb says it already a little. You can not compact the current database! If you use this line, the compacted database is newdb! olddb is still your old db and is not compacted!
That is one of the main reasons I wrote the code I posted. It can run unattended (through a scheduler) at night.
The code ensures the compacted database gets the name of the original database after compacting, so it looks like the original database is compacted, actually that is not done, it is compacted to another name.
The error handler just gives a notitification one of the databases to compact couldn’t be compacted. If someone is still using a database it cannot be compacted!

Reply | Quote

Good Morning Bart…
I understand everything you just replied… I’m going to ask some simple questions that have come to mind, in case the problem is something simple I’m missing…

I just created a new database file and wrote the following code in a module…

Option Compare Database
Option Explicit

Sub Compact()

Dim DBfile As String
Dim NewDBfile As String

DBfile = “H:MyfilesAccessDvdAccruals.mdb”
NewDBfile = “H:MyfilesAccessDvdAccrualsBU.mdb”
DBEngine.CompactDatabase DBfile, NewDBfile

End Sub

I compiled it, ran it and got the same “Invalid Argument” error…

1) Does the NewDBfile need to be already existing when I run this?… or does it create a new file with that name?

2) If I got the code to run successfully, would I see the NewDBfile name in Explore?

3) Do you think the problem could be anything to do with the network drive I’m refering to in the path?… I do have access to it, but it’s just a thought…

4) Could it be that I should be defining a workspace or something?

I’m sorry if these are silly questions but it’s “grasping at straws” time! LOL

Thanks again… Have a great day!

Reply | Quote

If the NewDBFile already exists, you’ll get an error. You need to kill any existing file with that name, but I’d check with the user first to be sure it isn’t needed. That syntax worked in the compactor I wrote in Access 97, so it should work for you.

Have you tried your code on a local drive to see if the problem is network related? Have you tried substituting short file names for the long ones to see if your operating system is balking at the long names? Have you tried using a UNC path instead of a mapped drive? Do you have access to the H drive when you try to run this, and do you have create permissions on that drive? Oh, one other question–you aren’t trying to run this from the database you want to compact, are you?

Reply | Quote

Good Morning Charlotte!

Geezzzz…you are a wealth of knowledge! Thanks so much…

I am not running the database that I am trying to compact… I read that the db must be closed while being compacted… (I’m learning! )

I have access to the network drive I’m refering to…
but as for setting up permissions on that drive… I don’t know?… I thought permission were set up for users/groups… not different drives?

I tried refering to my C: drive (copied the files over to there, just for that purpose) and still got the error…

I haven’t tried to run it with shorter names… I’ll do that today…

Ummmm… I probably should know…but what’s a UNC path? …I read in the book I have for reference (Access 97 Programming Unleashed *which I might add, sucks!!!*) that it’s “Universal Naming Convention”… but it shows examples of UNC addresses as being “D:MSOffice97OfficeSamplesNorthwind.MDB” and “D:MSOffice97Test.XLS” … What is the difference between the mapped drive address I used… and the UNC path?

Reply | Quote

Access security is different from network security. You need network permissions that allow you to create and delete files to make this work, and delete permissions are not automatically granted on networks. That probably isn’t the issue here since you had the same problem on your C drive.

UNCs for network drives can be very different from mapped paths. your H:… path is a mapped network path. The UNC probably is something like servername…. Depending on your network operating system, the UNC might work on a network drive where the mapped path wouldn’t. Again, since you experience the same problem on C:, that probably isn’t the reason for the failure.

Reply | Quote