Compressing Access-95 table thru VB.NET

Posted on 2004-11-24
Last Modified: 2010-04-23

   Using VB.NET( or C#) , how can I compress an Access-95 table ?
 (This can be considered as a windows application. But ultimately, this EXE  has to be in a schedule that is run once in a week).

  Actually there is a windows service running in the back which  , in every 10 minutes deletes almost all rows in the  Access table (same table which has to be compressed once in a week) . Do we have to do anything additionally, considering the possibility that  the  EXE in the schedule will run to compress the table at the same time when the windows service is deleting the rows in the table?  
                                        Thanking you
Question by:sgs1970
    LVL 69

    Accepted Solution

    You still have to use DAO or ADO because .Net does not gives you this feature.

    LVL 28

    Assisted Solution

    Add ref to Microsoft Jet replication Objects lib
    Jet OLEDB:Engine Type = 4 - for Access97
    Jet OLEDB:Engine Type = 5 - fro Access 2000

    Imports System.IO.File

      Private Sub CompactDatabase()
            Dim jro As New JRO.JetEngine
            Dim sConnSource As String
            Dim sConnDestination As String

            'compact db to temp.mdb

            sConnSource = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & DBPATH & ";Jet OLEDB:Engine Type = 4"

            sConnDestination = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & DBPATHONLY & "temp.mdb;Jet OLEDB:Engine Type = 4"

                jro.CompactDatabase(sConnSource, sConnDestination)
                Delete(DBPATH) 'delete db(System.IO.FIle)
                Copy(DBPATHONLY & "temp.mdb", DBPATHONLY & "CheckRec.mdb")
                Delete(DBPATHONLY & "temp.mdb")
                MessageBox.Show("Cannot compact database. It might be open by another user.", "Error Compacting", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub

    another example

    Sub Main()

            Dim jro As JRO.JetEngine

            jro = New JRO.JetEngine()

            jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb", _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewNwind.mdb;Jet OLEDB:Engine Type=4")

            MsgBox("Finished Compacting Database!")
    End Sub


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    This video discusses moving either the default database or any database to a new volume.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now