Write to MS Access 2007 Database from VB.NET

Write to MSAccess 2007

This is a very short example about how to write (insert a new record) to MSAccess database from VB.NET.
I believe that the code is well commented so no additional explanation is needed. Enjoy!

    Private Sub Form1_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles MyBase.Load
        ' populate the members list at startup
        PopulateMembersList()
    End Sub

    Private Sub WriteButton_Click(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs) Handles WriteButton.Click
        ' create a new oledb connection
        Dim connection As OleDb.OleDbConnection = NewConnection()
        ' create a new command objet
        Dim command As OleDb.OleDbCommand = connection.CreateCommand
        command.CommandText = "INSERT INTO Members(FirstName, LastName, NickName) " & _
                              "VALUES('" & Me.FirstName.Text & "', " & _
                              "'" & Me.LastName.Text & "', " & _
                              "'" & Me.NickName.Text & "')"
        connection.Open()
        command.ExecuteNonQuery()
        connection.Close()

        ' refresh the members list
        PopulateMembersList()
    End Sub

    Private Sub PopulateMembersList()
        ' create a new oledb connection
        Dim connection As OleDb.OleDbConnection = NewConnection()
        ' create a new command objet
        Dim command As OleDb.OleDbCommand = connection.CreateCommand
        command.CommandText = "SELECT FirstName, LastName, NickName FROM Members"

        connection.Open()
        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader
        Dim listitem As ListViewItem
        While reader.Read
            listitem = MembersList.Items.Add(reader("FirstName").ToString)
            listitem.SubItems.Add(reader("LastName").ToString)
            listitem.SubItems.Add(reader("NickName").ToString)
        End While
        reader.Close()
        connection.Close()
    End Sub

    Private Function NewConnection() As OleDb.OleDbConnection
        Dim connectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        Application.StartupPath & "\MembersDB.accdb;Persist Security Info=False;"
        Dim connection As New OleDb.OleDbConnection(connectionstring)
        Return connection
    End Function

Source Code (Visual Studio 2008 Project) >>>

In order to write to MS Access 2003 database from VB.NET 2005 just modify the connection string:

Private Function NewConnection() As OleDb.OleDbConnection
    Dim connectionstring As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & _
    Application.StartupPath & "\MembersDB.mdb;Persist Security Info=False;"
    Dim connection As New OleDb.OleDbConnection(connectionstring)
    Return connection
End Function

Source Code (Visual Studio 2005 Project) >>>