I have an application with an Access database as the source of the data for my application. I know that within Access you can compact a database. But is there a way programmatically within VB Express to compact the database If possible, I would like to avoid calling an Access command as some of my users might not have Access installed on their computers.
Also, what about a routine that when a user clicks on a button it will allow the user to save a backup copy of the database
Thanks,
John
Is there a way to compact a database through VB Express?
JZ1000
I haven't tried this yet, but in your example, you rename the file to something else. Is it possible to compact it to the same name If not, then I will need some way to replace the old file so the program recognizes the compacted version.
Thanks,
John
Peter D.252325
dao.DBEngineClass engine = new dao.DBEngineClass();
engine.CompactDatabase(@"c:\temp\test.mdb", @"c:\temp\test2.mdb", null, 0, null);
I hope you can keep this going. DAO and the Jet drivers haven't been part of the MDAC for quite a while now...
Daniel Hilgarth
I rarely ever disagree with no bugz but he has not chosen the best resposne in this case because Jet V4.0 does provide compression and yes you do have to do that detach and and rename dance... but here's the code:
Public Sub CompactDatabase()
' IOSUB.CompactDatabase - Called by MyApplication.MyApplication_Shutdown and Form1.cbExit_Click
' Delete any temporary versions of the database.
' Compact the database with a temporary name.
' If successful delete the original database
' Rename the old original database to the new name.
' Things to consider... this is fine when databases are small but consider when they are larger.
If con Is Nothing Then Exit Sub
Dim Settings As New My.MySettings
Using tempcon As New OleDbConnection(Common.GetConnectionString)
If con.ConnectionString = "" Then Exit Sub
Dim dbname As String = tempcon.DataSource
Dim TempFileName As String = Path.GetTempFileName()
File.Delete(TempFileName)
Dim wasOpen As Boolean = (con.State = ConnectionState.Open)
If wasOpen Then con.Close()
Dim jro As New JRO.JetEngine
Dim base As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source={0};Jet OLEDB:Engine Type=5;"
Try
jro.CompactDatabase(String.Format(base, Settings.DatabasePath & Settings.DatabaseName), _
String.Format(base, TempFileName))
File.Delete(tempcon.DataSource)
File.Move(TempFileName, tempcon.DataSource)
Catch
End Try
If wasOpen Then con.Open()
End Using
End Sub
Add a project reference to Microsoft Jet Replication Objects Version 2.6
For the following line: Using tempcon As New OleDbConnection( Insert Your Own Connection String here)
And as an extra bonus... there are no variable names that start with "The" either !!!!!!!!
vic07
Gunnar Adler
You don't have 'Common.GetConnectionString so you can't call it. I think you need a whole connection string there.
Erm the reference to "The" variables is an injoke. It's just a silly way to name variables.
take care,
PQSIK
I tried using this code and VB Express does not like a lot of it. By any chance is the code you are using C+ If so, I need the VB version.
Thanks.
John
Xenon86
Thanks. I'll try that. But I'm a bit confused by the end of your response when you say:
Add a project reference to Microsoft Jet Replication Objects Version 2.6
For the following line: Using tempcon As New OleDbConnection( Insert Your Own Connection String here)
And as an extra bonus... there are no variable names that start with "The" either !!!!!!!!
Do you mean that where you have "Insert Your Own Connection String here" that I need to replace 'Common.GetConnectionString' and add the specific path to the file I want to compact And what was the reference to a variable name starting with "The"
Thanks,
John
davidtcf
So you are referring to something like 'C:\My Documents\test.mdb'
John