1 2 Previous Next 

Parameters Oledb with DateTimePicker

Often is done the selection of data from a Jet (MS Access) file very difficult. While it is so easy
Public Class Form1
    'Drag and drop for this tip a datetimepicker and a DataGridView on a form
    'Be aware this is a sample better to use a generic dataset using the DataSource way
    Private DT As New DataTable
    Private Sub Form1_Load(ByVal sender As ObjectByVal e As EventArgsHandles MyBase.Load
        DateTimePicker1.Value = New Date(1995, 7, 3) 'For northwind which is so old.
    End Sub
    Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgsHandles DateTimePicker1.ValueChanged
        Using da As New OleDb.OleDbDataAdapter("SELECT * FROM Orders WHERE (OrderDate = ?)",
                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test\nwind.mdb")
            da.SelectCommand.Parameters.AddWithValue("?", DateTimePicker1.Value.Date) 'Watch the Date which is important
            DataGridView1.DataSource = DT
        End Using
    End Sub
End Class

Access: How to create an Accdb databasefile with columns

With thanks to Paul Clement we were able to create this tip how to create from scratch a Accdb database file in code. Be aware there has to be set a reference (extensions) to the Access Interop
'Set a reference to Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Access.Dao
Module Exercise
    Sub Main()
            Dim AccessDatabaseEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine
            Dim AccessDatabase As Microsoft.Office.Interop.Access.Dao.Database
            AccessDatabase = AccessDatabaseEngine.CreateDatabase("C:\Test\NewDatabase.accdb"LanguageConstants.dbLangGeneral, DatabaseTypeEnum.dbVersion120)
        Catch ex As Exception
        End Try
        Using conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "  Data Source=C:\Test\NewDatabase.accdb;Persist Security Info=False;")
            Using cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
              "AutoId int identity ," & _
              "Id int NOT NULL," & _
              "Name NVarchar(50)," & _
                "BirthDate datetime," & _
               "IdCountry int," & _
                  "CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
                Catch ex As Exception
                End Try
            End Using
        End Using
    End Sub
End Module

Convert MS Access DataBase to another version format (also to 2007) in Visual Basic Code

Often is asked in the forums how to handle an MS Access 2003 file and an MS Access 2007 file.

There is a very simple way to convert those. Be aware it is possible that methods from one are not available in the other and are therefore then not always converted.

Option Strict On
'Set a reference to Microsoft.Office.Interop.Access
Imports Microsoft.Office.Interop.Access
Module Module1
    Sub Main()
        Dim theAccessObject As New Application
    End Sub
End Module

VS.Net Open database in 64 bit OS

If you try and open an access database on a 64bit os you will get the following error. System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. To prevent this open My Project -> compile -> advanced compiler options and set the target cpu to x86.

Otherwise get the new redistributable


    Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As OleDbConnection
        Dim strConn As String
        Dim da As OleDbDataAdapter

        strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = c:\Northwind.mdb;"

        conn = New OleDbConnection(strConn)
        ds = New DataSet
        da = New OleDbDataAdapter("Select * from Products", conn)
            da.Fill(ds, "Products")
        Catch ex As Exception
        End Try
        DataGridView1.DataSource = ds.Tables("Products")
    End Sub

Access (OleDB): List Database Tables

Use the oledb connections GetOleDbSchemaTable method to list the tables in an access database.
Module Module1
    Sub Main()
        Dim strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
        strConn &= "Data Source = C:\Test1\Nwind.mdb;"
        Dim dtTableNames As DataTable
        Using conn As New OleDb.OleDbConnection(strConn)
            dtTableNames = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, _
                New Object() {NothingNothingNothing"TABLE"})
        End Using
        Dim dr As DataRow
        For Each dr In dtTableNames.Rows
    End Sub
End Module

1 2 Previous Next