Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code for Compacting Database...

    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!

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

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

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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?

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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 <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q279/6/44.ASP?LN=EN-US&SD=tech&FR=0&qry=jetcomp.exe&rnk=1&src=DHCS_MSP SS_tech_SRCH&SPR=ACC97>jetcomp.exe</A> one time, Microsoft claims it does a better repair job than compact/repair.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code for Compacting Database...

    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.
    Charlotte

  6. #6
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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...

  7. #7
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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?

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    Just a shot in the dark but do you have the <font color=008080>Microsoft DAO 3.51 Object Library</font color=008080> 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.

  10. #10
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    Hi Brian... [img]/w3timages/icons/yep.gif[/img] 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! [img]/w3timages/icons/laugh.gif[/img]) ...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... [img]/w3timages/icons/laugh.gif[/img]

    I'm going [img]/w3timages/icons/nuts.gif[/img] 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.... [img]/w3timages/icons/hairy.gif[/img]

    (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!

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code for Compacting Database...

    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.
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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!

  13. #13
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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!

  14. #14
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Compacting Database...

    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...

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code for Compacting Database...

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •