I have never been much of an evangelist for Microsoft Access, but not because I do not like the application. It just so happens that most of the enterprise solutions we develop at 3Sharp use SQL Server, so our exposure to Access has been somewhat limited.

Recently, however, we were tasked with building a budget solution prototype that uses Access with InfoPath. Within the InfoPath form, users could update multiple tables with a single action, and it became necessary for us to create custom calls to the database with managed code. As it turns out, using managed code to submit data from InfoPath to Access is not as arduous as it sounds.

The first step is to create data connections for your table(s). Also, note that the form will need to be fully trusted in order to write to the database. Next, add a reference to the System.Data.dll .NET component and include the using System.Data.OleDb directive in your form code file. After the reference and directive have been added, you can declare member variables for the database connection and each of the update commands that your code requires. In this particular example, I will just show the variables for the command to update the Budget table:

private OleDbConnection dbConnection;
private OleDbCommand budgetInsertCommand;

After you include the member variables in your form code file, you can add functions that set up your database connection. In our example, the Budget table has an auto-numbered BudgetID, a BudgetName, and a BudgetAmount:

public BudgetSolution()
{
    SetUpDBConnection();
}

private void SetUpDBConnection()
{
    // Connection
    dbConnection = new OleDbConnection();
    dbConnection.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops =2;
      Jet OLEDB:Registry Path =;Jet OLEDB:Database Locking Mode =1;
      Jet OLEDB:Database Password =;Data Source =""C:testBudgetSolution.mdb"";
      Password =;Jet OLEDB:Engine Type =5;Jet OLEDB:Global Bulk Transactions =1;
      Provider =""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database =;
      Jet OLEDB:SFP =False;Extended Properties =;Mode =Share Deny None;
      Jet OLEDB:New Database Password =;Jet OLEDB:Create System Database =False;
      Jet OLEDB:Don't Copy Locale on Compact =False;
      Jet OLEDB:Compact Without Replica Repair =False;User ID =Admin;
      Jet OLEDB:Encrypt Database =False";

    // Budget insert command
    budgetInsertCommand = new OleDbCommand();
    budgetInsertCommand.Connection = dbConnection;
    budgetInsertCommand.CommandText = "INSERT INTO Budget(BudgetName, BudgetAmount) VALUES (?, ?)";
    budgetInsertCommand.Connection = this.dbConnection;
    budgetInsertCommand.Parameters.Add(new OleDbParameter("BudgetName", OleDbType.VarWChar, 50, "BudgetName"));
    budgetInsertCommand.Parameters.Add(new OleDbParameter("BudgetAmount", OleDbType.Double, 0, "BudgetAmount"));
}

Then, to update the database, a connection will need to be opened before the appropriate fields can be added. In this example, I have added this code to the OnClick event for an Add Budget button control:

[InfoPathEventHandler(MatchPath="ButtonAddBudget", EventType=InfoPathEventType.OnClick)]
public void ButtonAddBudget_OnClick(DocActionEvent e)
{
    dbConnection.Open();
    budgetInsertCommand.Parameters["BudgetName"].Value = thisXDocument.DOM.selectSingleNode("//my:BudgetName").text;
    budgetInsertCommand.Parameters["BudgetAmount"].Value = thisXDocument.DOM.selectSingleNode("//my:BudgetAmount").text;
    budgetInsertCommand.ExecuteNonQuery();
    dbConnection.Close();
}

For enterprise solutions, I still prefer submitting to a Web service that sends form data to a SQL Server database. But for solutions that have only a small number of users, I would not rule out Access. It is nice to know that I can easily write custom calls from InfoPath and leverage the portability of Access without a reliance on IIS.