-
Jul 25th, 2013, 09:19 AM
#1
Thread Starter
Addicted Member
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
-
Jul 25th, 2013, 10:56 AM
#2
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
-
Jul 25th, 2013, 11:20 AM
#3
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!
-
Jul 25th, 2013, 11:44 AM
#4
Thread Starter
Addicted Member
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?
-
Jul 25th, 2013, 05:42 PM
#5
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!
-
Jul 25th, 2013, 06:28 PM
#6
Thread Starter
Addicted Member
Re: compacting 2010 accdb database using VB 2012
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|