none
What is the best solution for compact and repair Access 2013 database RRS feed

  • Question

  • I've windows application by using Access 2013 database and VS 2015.But i'm confusing now when i searched in the web 

    i found that (Someone says that "DAO" is the best for compact access database) and (Another one says that "JRO" is more efficient). I want a method performs this task automatic and scheduled. Is compact database important to any application ?

    and Is there another methods to compact and repair Access database other than the above ?

    Any suggestions will be appreciated

    Thanks in advance


    Regards From Amr_Aly

    Monday, October 23, 2017 7:43 AM

Answers

  • If you are using newer versions of Microsoft Access (e.g. .accdb) then you should be using the DAO interop library (Microsoft.Office.Interop.Access.Dao) to compact the database periodically.

        Sub CompactAccessDatabase()
    
            Dim DatabasePath As String = "C:\Users\...\Documents\My Database\Access\Northwind.accdb"
            Dim DatabasePathCompacted As String = "C:\Users\...\Documents\My Database\Access\NorthwindCompacted.accdb"
    
            Dim AccessEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
    
            AccessEngine.CompactDatabase(DatabasePath, DatabasePathCompacted)
    
            AccessEngine = Nothing
    
        End Sub

    JRO only works for Jet database files (.mdb).

    When you run the CompactDatabase method the database must be closed. It wasn't clear to me whether the database was located on a network share, but compacting would be a bit more complicated under this scenario since users could have the database open. This is why you should develop your application so that connections to the database are only opened (and closed) on an as needed basic and not persisted through the lifetime of the application.

    Regarding SQL Server, since it was mentioned, I don't usually recommend it unless you are working in an environment where there is concurrent user access of more than ten users. For apps where the database is located on the users machine and not shared, Access is fine. Access is subject to corruption, which is why you should compact the database on a regular basis.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Amr_Aly Thursday, October 26, 2017 2:09 PM
    Wednesday, October 25, 2017 1:22 PM

All replies

  • Compact/repair is important only when dealing with a large oversize database. For example, we have a legacy .accdb database that needs compact/repair maybe once a year. This legacy database has been in service for over 15 years. When we compact/repair it's done by a support person simply because if done via code you have no clue if when compacted/repaired there may be issues that are missed and no way to report the issues while done by a person issues are known and can be examined and taken care of.

    I have to say this, in general ms-access is not a good choice for applications while SQL-Server is yet I know there are developers that don't have a choice in the selection of a database.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, October 23, 2017 10:09 AM
    Moderator
  • Compact/repair is important only when dealing with a large oversize database. For example, we have a legacy .accdb database that needs compact/repair maybe once a year. This legacy database has been in service for over 15 years. When we compact/repair it's done by a support person simply because if done via code you have no clue if when compacted/repaired there may be issues that are missed and no way to report the issues while done by a person issues are known and can be examined and taken care of.

    I have to say this, in general ms-access is not a good choice for applications while SQL-Server is yet I know there are developers that don't have a choice in the selection of a database.

    Thanks Karyne, For your fast reply...................

    Access database choice is for small application and really it's succeeded to perform the heavy tasks .For example i know an urologist doctor has an application by VBA(has created 10 years ago) and the database is MS Access 2003 .The number of visits reached to 4600 visit till now . And the doctor is very happy by this primative app. But i think it is not primative because it performs a lot of tasks.

    Indeed you are right SQL server is the right choice but in some cases the Access is the best.

    Can we make a button for a doctor to perform this task(Compact database Or here in my small Application we can perform it automatically ? .........I want knowing the best choice please. 

    Thanks in advance..........


    Regards From Amr_Aly

    Monday, October 23, 2017 11:19 AM
  • I can't give you a solution that is both proven and tested to say it's the right fit for you.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, October 23, 2017 12:40 PM
    Moderator
  • I can't give you a solution that is both proven and tested to say it's the right fit for you.


    OK ,Let us choose one and discuss it.

    Can this snippet code is suitable and doesn't make any problem 

     
    Imports System.IO
    Module Module1
        Sub Main()
            Try
                Dim File_Path, compact_file As String
                'Original file path that u want to compact
                File_Path = AppDomain.CurrentDomain.BaseDirectory & "db.accdb"
                'compact file path, a temp file
                compact_file = AppDomain.CurrentDomain.BaseDirectory & "db1.accdb"
                'First check the file u want to compact exists or not
                If File.Exists(File_Path) Then
                    Dim db As New DAO.DBEngine()
                    'CompactDatabase has two parameters, creates a copy of 
                    'compact DB at the Destination path
                    db.CompactDatabase(File_Path, compact_file)
                End If
                'restore the original file from the compacted file
                If File.Exists(compact_file) Then
                    File.Delete(File_Path)
                    File.Move(compact_file, File_Path)
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
    End Module

    I've to try and test it in my real project .But how can i know that is the best ....

    But the question is; Do you prefer the " DAO " method or " JRO " ..?


    Regards From Amr_Aly

    Monday, October 23, 2017 1:06 PM
  • Indeed you are right SQL server is the right choice but in some cases the Access is the best.

    And the cow jumped over the moon too that Access should even be considered to be the right choice for a small business  solution over MS SQL Server Express.  

    The fact that you are even here posting about how to repair Access should give you a clue that it's not suitable  for even a small enterprise  level business solution. 

    Monday, October 23, 2017 1:12 PM
  • In regards to DAO or JRO, given those choices DAO but be forewarn on some Windows operating systems they may not be available or may require special security permissions.

    In regards to the try/catch, I would go farther than a MessageBox. You could log the exception to a log file and/or email the error message to you using SMTP classes in .NET to send an email (here you will find good examples).

    Getting back to how to use the code e.g. trigger it, could be logic in your app to do the trigger or via a scheduled windows task.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, October 23, 2017 3:14 PM
    Moderator

  • Getting back to how to use the code e.g. trigger it, could be logic in your app to do the trigger or via a scheduled windows task.

    Thanks Karyne,

    Now How can i use the above code snippet  ? .I tried it but didn't work .I call this module in a button click event and it didn't create the new compact file . What will we do ?

    i came back as you asked in order to understand the method

    Thanks in advance.....


    Regards From Amr_Aly


    • Edited by Amr_Aly Monday, October 23, 2017 9:43 PM
    Monday, October 23, 2017 9:29 PM
  • Maybe it does something but DAO was not intended for Access 2013. It was for the Jet database which was with the first Office releases. 

    Be aware Office 2013 is for MS Access and not as the older versions where a kind of freeware database for programming.

    https://support.office.com/en-us/article/Compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2

    In fact everything is written on that page even how to do it from your program. 

    The best way, as is written. Don't use it as it is a stand alone database. It is not intended for that. 


    Success
    Cor

    Tuesday, October 24, 2017 10:04 AM
  • Maybe it does something but DAO was not intended for Access 2013. It was for the Jet database which was with the first Office releases. 

    Be aware Office 2013 is for MS Access and not as the older versions where a kind of freeware database for programming.

    https://support.office.com/en-us/article/Compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2

    In fact everything is written on that page even how to do it from your program. 

    The best way, as is written. Don't use it as it is a stand alone database. It is not intended for that. 


    Thanks Mr Cor,

    In fact this link enriched my knowledge more than i can imagine.

    This link makes me think in SQL database more than before and i'll make another copy of my App. with SQL in parallel to my present copy of MS Access .............Thank you so much Mr Cor i think it's a best answer .Finally any suggestions about using SQL in a very small business 

    Thanks in advance

     


    Regards From Amr_Aly


    • Edited by Amr_Aly Wednesday, October 25, 2017 2:05 PM
    Wednesday, October 25, 2017 12:22 PM
  • In regards to using SQL-Server for a small business, once you know the requirements see the following for pricing.

    https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing

    There are tools to migrate from MS-Access to SQL-Server.

    https://docs.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, October 25, 2017 12:29 PM
    Moderator
  • If you are using newer versions of Microsoft Access (e.g. .accdb) then you should be using the DAO interop library (Microsoft.Office.Interop.Access.Dao) to compact the database periodically.

        Sub CompactAccessDatabase()
    
            Dim DatabasePath As String = "C:\Users\...\Documents\My Database\Access\Northwind.accdb"
            Dim DatabasePathCompacted As String = "C:\Users\...\Documents\My Database\Access\NorthwindCompacted.accdb"
    
            Dim AccessEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
    
            AccessEngine.CompactDatabase(DatabasePath, DatabasePathCompacted)
    
            AccessEngine = Nothing
    
        End Sub

    JRO only works for Jet database files (.mdb).

    When you run the CompactDatabase method the database must be closed. It wasn't clear to me whether the database was located on a network share, but compacting would be a bit more complicated under this scenario since users could have the database open. This is why you should develop your application so that connections to the database are only opened (and closed) on an as needed basic and not persisted through the lifetime of the application.

    Regarding SQL Server, since it was mentioned, I don't usually recommend it unless you are working in an environment where there is concurrent user access of more than ten users. For apps where the database is located on the users machine and not shared, Access is fine. Access is subject to corruption, which is why you should compact the database on a regular basis.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Amr_Aly Thursday, October 26, 2017 2:09 PM
    Wednesday, October 25, 2017 1:22 PM
  • If you are using newer versions of Microsoft Access (e.g. .accdb) then you should be using the DAO interop library (Microsoft.Office.Interop.Access.Dao) to compact the database periodically.

        Sub CompactAccessDatabase()
    
            Dim DatabasePath As String = "C:\Users\...\Documents\My Database\Access\Northwind.accdb"
            Dim DatabasePathCompacted As String = "C:\Users\...\Documents\My Database\Access\NorthwindCompacted.accdb"
    
            Dim AccessEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
    
            AccessEngine.CompactDatabase(DatabasePath, DatabasePathCompacted)
    
            AccessEngine = Nothing
    
        End Sub

    When you run the CompactDatabase method the database must be closed. It wasn't clear to me whether the database was located on a network share, but compacting would be a bit more complicated under this scenario since users could have the database open. This is why you should develop your application so that connections to the database are only opened (and closed) on an as needed basic and not persisted through the lifetime of the application.

    Regarding SQL Server, since it was mentioned, I don't usually recommend it unless you are working in an environment where there is concurrent user access of more than ten users. For apps where the database is located on the users machine and not shared, Access is fine. Access is subject to corruption, which is why you should compact the database on a regular basis.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thank you Paul,

    It worked well without password(My .accdb is protected by password),But when i put the password it didn't work at all .I tried with it and search on the web but all my tries failed. Any suggestion

    Thanks in advance

     


    Regards From Amr_Aly

    Thursday, October 26, 2017 8:03 AM
  • If you are using newer versions of Microsoft Access (e.g. .accdb) then you should be using the DAO interop library (Microsoft.Office.Interop.Access.Dao) to compact the database periodically.

    
    

    Thank you Paul,

    It worked well without password(My .accdb is protected by password),But when i put the password it didn't work at all .I tried with it and search on the web but all my tries failed. Any suggestion

    Thanks in advance

     


    Regards From Amr_Aly

    Did you specify the database password in the CompactDatabase statement?

    AccessEngine.CompactDatabase(DatabasePath, DatabasePathCompacted, , , ";pwd=password")


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, October 26, 2017 12:05 PM

  • Did you specify the database password in the CompactDatabase statement?

    AccessEngine.CompactDatabase(DatabasePath, DatabasePathCompacted, , , ";pwd=password")
        Sub CompactAccessDatabase()
    
            Dim DatabasePath As String = "C:\Users\...\Documents\My Database\Access\Northwind.accdb"
            Dim DatabasePathCompacted As String = "C:\Users\...\Documents\My Database\Access\NorthwindCompacted.accdb"
    
            Dim AccessEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
    
            AccessEngine.CompactDatabase(DatabasePath, DatabasePathCompacted)
    
            AccessEngine = Nothing
    
        End Sub

    Thanks Paul,

    I've tried this method before, But now it is working well , We can consider this snippet of code as a good backup code isn't it..................? 

    many thanks Paul 


    Regards From Amr_Aly

    Thursday, October 26, 2017 2:15 PM
  • Thanks Paul,

    I've tried this method before, But now it is working well , We can consider this snippet of code as a good backup code isn't it..................? 

    many thanks Paul 


    Regards From Amr_Aly

    Yes, if you just want to back up the database this code would work as well.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, October 26, 2017 4:02 PM
  • Thank you very much for helping me and guide me to a right way,It's so helpful snippet.And i put the time and date to the compacted file ,Now i can backup with a compacted file any number of times

    I have an inquiry Paul ...

    Can you suggest a simple book or an article explaining these topics( What's dataset,data adapter,binding source,dataview,datatable and binding context and how can i use it in an ideal way ?

    Thanks Paul ,Sorry for prolongation    

      

    Regards From Amr_Aly

    Thursday, October 26, 2017 5:27 PM
  • I have an inquiry Paul ...

    Can you suggest a simple book or an article explaining these topics( What's dataset,data adapter,binding source,dataview,datatable and binding context and how can i use it in an ideal way ?

    Thanks Paul ,Sorry for prolongation    

      

    Regards From Amr_Aly

    Below are links to information that should help get you started with ADO.NET Classes. It demonstrates the different ways you can connect to an Access or SQL Server database and view/update data:

    http://www.homeandlearn.co.uk/NET/nets12p1.html

    http://vb.net-informations.com/dataview/ado.net-dataview.htm


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, October 27, 2017 1:15 PM
  • Is there another methods to compact and repair Access database other than the above?

    Some useful information has been shared in this article - Repair Access Database via Compact and Repair Database Tool

    Other than using the Compact and Repair utility, you can try to import the corrupt Access database into a new one. Also, Microsoft provides a freeware tool, JetComp.exe utility, to repair Access database.

    Thanks.

    Tuesday, October 23, 2018 11:15 AM