This project has moved and is read-only. For the latest updates, please go here.
' compact a database with Access
' taken from Example03.vb

Private Sub Example03Main()

   ' start access 
   Dim accessApplication As New Access.Application()

   'create database name 
   Dim fileExtension As String = GetDefaultExtension(accessApplication)
   Dim documentFile As String = String.Format("{0}\\Example03{1}",
fileExtension) 'delete old database if exists If (System.IO.File.Exists(documentFile)) Then System.IO.File.Delete(documentFile) End If ' create database Dim newDatabase As DAO.Database = _
accessApplication.DBEngine.Workspaces(0).CreateDatabase( _
documentFile, LanguageConstants.dbLangGeneral) accessApplication.DBEngine.Workspaces(0).Close() ' setup database connection Dim oleConnection As New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + documentFile) oleConnection.Open() ' create table Dim oleCreateCommand As New OleDbCommand( _
"CREATE TABLE NetOfficeTable(Column1 Text, Column2 Text)", oleConnection) oleCreateCommand.ExecuteReader().Close() ' write some data with plain sql & close For i As Integer = 0 To 2000 Dim insertCommand As String = String.Format( _
"INSERT INTO NetOfficeTable(Column1, Column2) VALUES(""{0}"", ""{1}"")", _
i, DateTime.Now.ToShortTimeString()) Dim oleInsertCommand As New OleDbCommand(insertCommand, oleConnection) oleInsertCommand.ExecuteReader().Close() Next oleConnection.Close() ' now we do CompactDatabase Dim newDocumentFile As String = String.Format("{0}\\CompactDatabase{1}", _
Environment.CurrentDirectory, _
fileExtension) If (File.Exists(newDocumentFile)) Then File.Delete(newDocumentFile) End If accessApplication.DBEngine.CompactDatabase(documentFile, newDocumentFile) 'close access and dispose reference accessApplication.Quit(AcQuitOption.acQuitSaveAll) accessApplication.Dispose() Dim fDialog As New FinishDialog("Database saved.", documentFile) fDialog.ShowDialog(Me) End Sub ''' <summary> ''' returns the valid file extension for the instance. for example ".mdb" or ".accdb" ''' </summary> ''' <param name="application">the instance</param> ''' <returns>the extension</returns> ''' <remarks></remarks> Private Function GetDefaultExtension(ByVal application As Access.Application) As String ' Access 2000 doesnt have the Version property(unfortunately) ' we check for support with the SupportEntity method, implemented by NetOffice If (Not application.EntityIsAvailable("Version")) Then Return ".mdb" End If Dim version As Double = _
Convert.ToDouble(application.Version, CultureInfo.InvariantCulture)
If (version >= 12.0) Then Return ".accdb" Else Return ".xls" End If End Function

Last edited Jun 13, 2012 at 2:56 PM by SebastianDotNet, version 4


No comments yet.