Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2007
    Posts
    48

    Unanswered: Repair and compact with VBA

    After running a VBA procedure, I need to compare and compact the Access DB programatically with VBA before closing the DB.



    Does anyone know if this can be done with VBA.



    All ideas, suggestions and help appreciated

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First hing that poed into my head was "WHY?!"
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Posts
    48
    thought someone would raise that point!!

    It is all part of an Excel VBA program, georgev, that connects to an Access database, runs an Access VBA procedure that modifies the database, without any manual intervention.

    Before the database closes and returns control to the Excel VBA code, I want to repair and compact the DB.

    After this is done, the DB is compressed into a .zip file and sent to a network server.

    So, any suggestions on how to repair and compact the DB with VBA?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Pro typing there George ^^

    Yes it can be done with VBA. I can't remember the code offhand though, but it's there.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Docmd.RunCommand acCmdCompactDatabase might do the trick for you
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2007
    Posts
    48
    Thanks StarTrekker, much appreciated. Any suggestions where would I fit this command so that it is run just before the DB closes?

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Other than the code StarTrekker provided, you can also set the mdb to automatically compact and repair via Tools -> Options (General Tab) and check the Compact on Close checkbox.

    Otherwise, I might put the compact code in the OnClose event of the form (providing the form doesn't close and do something else other than exit.) This would take care of the situation where a user might exit MSAccess via the upper right X button. But your setup of the form could be different so where to exactly put the code may vary.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2007
    Posts
    48

    With thanks

    hi again, Paul. Thanks for your comment - I was not aware of the Tools/ Options for repair on closing - sounds good.

    With regards to the line of DoCmd code, unfortunatley I don't have a form running, just transferring data via SQL, so I can't put the line of code in a form.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Gotta go with Paul's suggestion then
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    Pro typing there George ^^
    Damn those vile batteries!
    Wireless keyboards have their failings
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The other alternative is to periodically manually do a compact and repair

    Unlike the /repair command line switch there probably isn't a limit to the amount of times comact and repair can be used, but I doubt it will bloat that much. Before doing a compact and repair, being the bitter twisted cynic I am I prefer to do a backup, just in case.

    the problem with compact and repair in my mind is that it locks other users out, so its not a smart call to attempt one in a multi user environment. if its a single user environment then fine.
    perhaps another option call then is to kick of a VBScript / DOS process at the tail end of your excel script, after you have closed the Access DB which does a backup then a compact and repair. Argaubly you could do it as part of an overnight script within an Access app. Say stopre the access MDB's which have been modified and then compress them. or compress all MDB's in directory X....

    just a thought

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup... I tend to like to do a backup before compacting and repairing too. I've seen at least one database file DIE to hitting the compact and repair command.

    Once bitten...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Aug 2016
    Posts
    2

    This should do it

    Quote Originally Posted by JillB View Post
    After running a VBA procedure, I need to compare and compact the Access DB programatically with VBA before closing the DB.



    Does anyone know if this can be done with VBA.



    All ideas, suggestions and help appreciated
    Quite old thred but since it is still "Unanswered" and came up to search of mine...

    Calling this from VBA would help:

    Code:
    Function compact_inp_db()
    
    Dim strDB
    Dim strDBbk
    
    On Error Resume Next
    
    ' Set the path to the DB to be compacted and the backup file
    strDB = "D:\3MAX_HRLyDB\inpDB.accdb"
    strDBbk = "D:\3MAX_HRLyDB\inpDBbk.accdb"
    
    ' Set the object to perform file operations
    Set ObjScript = CreateObject("Scripting.FileSystemObject")
    
    ' Delete old backup DB if exists
    ObjScript.DeleteFile strDBbk
    
    ' Set the current DB as the backup file
    ObjScript.MoveFile strDB, strDBbk
    
    ' Compact and Repair the backup file into a new Current DB
    Application.CompactRepair strDBbk, strDB
    
    End Function

  14. #14
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As was suggested...running a C & R is frequently blamed for corrupting an entire database, and making/storing a backup, before running it, really is vital!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  15. #15
    Join Date
    Aug 2016
    Posts
    2
    Quote Originally Posted by Missinglinq View Post
    As was suggested...running a C & R is frequently blamed for corrupting an entire database, and making/storing a backup, before running it, really is vital!

    Linq ;0)>
    And the code presented does that, as suggested...

Posting Permissions

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