Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

August 25, 2009

Update a Microsoft Access Database

Filed under: C#,Data,VB.NET @ 7:29 pm

Despite the fact that there is a free version of SQL Server called SQL Server Express, there are still applications that require using a Microsoft Access database. But since these are dwindling in number, there are few articles or posts devoted to accessing Access.

This post (and my prior post that demonstrates how to retrieve Access data) attempt to rectify that issue.

The code is first shown in both VB and C#. It is then described in detail below.

NOTE: Be sure to set a reference to System.Data.OleDb.

In C#:

string update = "Update Customer Set Title = ? Where PersonId = ?";
string cnnString =
     
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;";

using (var cnn = new OleDbConnection(cnnString))
{
    cnn.Open();

    using (var cmd = new OleDbCommand(update, cnn))
     {
        // Add the parameters
        cmd.Parameters.AddWithValue("Title", "President");
        cmd.Parameters.AddWithValue("PersonId", 1);

        // Execute the command
        cmd.ExecuteNonQuery();
    }
}

In VB:

Dim update As String = _
    "Update Customer Set Title = ? Where PersonId = ?"
Dim cnnString As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;"

Using cnn As New OleDbConnection(cnnString)
    cnn.Open()

    Using cmd As New OleDbCommand(update, cnn)
        ‘ Add the parameters
        cmd.Parameters.AddWithValue("Title", "President")
        cmd.Parameters.AddWithValue("PersonId", 1)

        ‘ Execute the command
        cmd.ExecuteNonQuery()
    End Using
End Using

The code begins by declaring several variables. The first variable is the Update statement. Change it to update the desired fields from the desired table. Use "?" as placeholders for command parameters.

The connection string uses the Jet OleDb data provider. Change the connection string Data Source property to the directory and name of your Access database file. If you provide no directory (like in this example), it will assume that the file is located in the same directory as the executing application.

The first using statement defines the connection. The connection is then opened. The connection is automatically closed at the end of the using block.

The second using statement creates the command using the defined query and opened connection. Use the AddWithValue method of the command parameter’s collection to add the parameter names and values. There should be an AddWithValue method call for each "?" placeholder in the Update statement.

Finally, execute the command using the ExecuteNonQuery method.

Enjoy!

3 Comments

  1.   Access Database Developer — December 2, 2009 @ 5:39 am    Reply

    Useful article, long live Access!

    Dennis

  2.   Phill — April 12, 2010 @ 12:35 pm    Reply

    Access is still a FREE database for web sites where most charge for MS SQL DBs.

    Also an Access DB works on ALL desktops (when building a stand alone application) and is easy to maintain/Update over MS SQL.

    Keep up the good work.

  3.   James Barker — April 25, 2013 @ 10:01 am    Reply

    Thanks very much for that!

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs