' write data to database with Access
' taken from Example02.vb

Private Sub Example02Main()

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

   'create database name 
   Dim fileExtension As String = GetDefaultExtension(accessApplication)
   Dim documentFile As String = String.Format("{0}\\Example02{1}", _
Application.StartupPath, _
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 1000 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() '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 1:56 PM by SebastianDotNet, version 7