Results 1 to 6 of 6

Thread: compacting 2010 accdb database using VB 2012

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    compacting 2010 accdb database using VB 2012

    I've looked all over the net and tried all sorts of different code suggestions, but nothing seems to work. I'm obviously doing something wrong, but I don't know what it could be.

    Code:
    'Add a reference to Microsoft Jet and Replication Objects 2.x Library when setting up application
            
            Dim DataDirectory = Application.StartupPath & "\Database\"
            Dim Provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataDirectory & "Rentals.accdb"
            Dim Provider1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataDirectory & "Rentals1.accdb"
            Dim je As New JRO.JetEngine
    
    
            Try
    
                ' remove compacted database if it exists
                If Dir(DataDirectory & "Rentals1.accdb") <> "" Then Kill(DataDirectory & "Rentals1.accdb")
    
                'compact database
                je.CompactDatabase(Provider, Provider1)
    
                WriteStatusMessage("Successfully compacted and repaired database.")
    
            Catch ex As Exception
    
                WriteStatusMessage("Failed to compact and repair database.")
    
            End Try
    I am aware that the directory to use is the ...bin/debug and I also have a "Database" directory in that which holds the accdb. If I place a Rentals1.accdb file in that directory with the Rentals.accdb and run the code, the Rentals1.accdb file is deleted as expected. However, the compact command throws an exception. I got the syntax for the provders basically from MS and just modified them with my locations/files. They match the connection string that VB2012 uses when creating a connection using Add New Data Source.

    Never having successfully done this, I expect it will create a Rentals1.accdb file which is the compacted version. Then I will have to delete the original file and rename the compacted one. But I have to get the code to compact first.

    What am I missing here?
    TIA
    Ken

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: compacting 2010 accdb database using VB 2012

    "However, the compact command throws an exception." -- well seeing as how you've seen fit to keep the exception message to yourself, I'm not sure how to help.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: compacting 2010 accdb database using VB 2012

    So to be clear. You have an ACE.OLEDB database and you're trying to compact it with a JetEngine. What could possibly go wrong?

    All processes of this kind with an Access 2007 database require you to run the Access Application whether directly or automated from VB.net.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: compacting 2010 accdb database using VB 2012

    TG:
    I'm sorry .. the exception is an "invalid argument".

    DF:
    I can't guarantee that the end users will have Access 2007+ installed and since I need to clean the file up from time to time after insertions and deletions, apparently I can't use an Access database. What database would you suggest?

  5. #5
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: compacting 2010 accdb database using VB 2012

    You can use an Access datatbase. You just can't use an Access 2007 database.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: compacting 2010 accdb database using VB 2012

    Then an mdb format?

Posting Permissions

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



Click Here to Expand Forum to Full Width