Using Access with InfoPath

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.

8 thoughts on “Using Access with InfoPath

  1. In the Solution Explorer of your Visual Studio solution, you will need to right-click "References" and select "Add Reference". Then, select the System.Data.dll .NET component. The "using" directive can be placed at the top of your form code file (e.g., on the line that follows "using System;").

  2. The answer to your question about the connection is somewhat more involved. To get this information, I right-clicked the form code in my Solution Explorer and selected Add | Add Windows Form. Once the new Windows Form was added to my project, I dragged an OleDbDataAdapter control from my toolbox into the form designer. I then walked through the Data Adapter Configuration Wizard. After the wizard was completed, the connection information was found in the "Windows Form Designer generated code" region.

  3. This seems very helpful, but I have a few questions on this step…

    "Next, add a reference to the System.Data.dll .NET component and include the "using System.Data.OleDb" directive in your form code file."

    How do you do this?? Where does it go in the code??? I have no idea.

    Thanks,
    Stacy

  4. Where do you get this information??

    // 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";

  5. K. Patrick,

    You can design an InfoPath form that queries and submits to an Access database. When you start InfoPath:

    * In the Getting Started dialog box, click Design a Form Template.
    * In the Design a Form Template dialog box, double-click Database.
    * In the Data Connection Wizard, click Select Database, navigate to the location of your DB file, and then complete the wizard.

    If you are looking for something a little more advanced, you can try the technique identified in this blog post or submit to a Web service, which would then update the Access DB.

    NOTE: You cannot create Access database data connections in form templates that were designed based on a Web service, XML document, etc.

    Hope this helps…

    Regards,
    David

  6. Hi,This code is helping me a lot………but I have a problem……..I need to create a custom code to retrive the max value of the ID column…..how can I do it with your OleDbConnection???

Comments are closed.