How to create add-ins for Office Access 2010: C#, VB.NET

I’m sure like me, a lot of IT professionals had customers who made a substantial investment in developing a custom application in Microsoft Access. As is the nature of such things, the customer outgrows their MS Access application and is soon in need of a larger, more complex system.

In a perfect world you would be able to rewrite the entire system from scratch and move all the users onto the new system in one fell swoop. Unfortunately, this is hardly ever the case and your clients would prefer re-using their existing investment. In this post I will demonstrate by using Add-in Express for Office and .net, how you could leverage your clients’ current MS Access systems’ functionality whilst simultaneously introducing some of the new systems’ features. We are going to build an Access plug-in that gives the user added functionality depending on the form they have open in MS Access.

Same drill as usual, start by creating a new ADX COM Add-in project in Visual Studio 2010.

Complete the wizard, by selecting Visual C# as the programming language, Microsoft Office 2010 as the minimum supported Office version and Microsoft Access as the supported application. Once the wizard has completed, switch to the AddinModule designer and add a new ADXRibbonTab component. Add two ADX Ribbon Groups, and one button to each of the Ribbon Groups. If you want pictures on your buttons, you would need to add an ImageList control to the AddinModule designer surface and set the button’s ImageList property. We will add logic to enable two buttons depending on which form is open in MS Access, so set its Enabled property to False. Once you’re done designing the ADXRibbonTab, it can look like this:

Add the following code to the OnClick event handler of the Employee Leave Application button:

private void btnEmployeeLeave_OnClick(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed)
{
    Access.Control firstNameControl = (Access.Control)AccessApp.Forms["Employee Details"].Controls["First Name"];
    Access.Control lastNameControl = (Access.Control)AccessApp.Forms["Employee Details"].Controls["Last Name"];
 
    //Get Values from TextBox
    string firstName = firstNameControl.GetType().InvokeMember("Value", System.Reflection.BindingFlags.GetProperty,
        null, firstNameControl, null).ToString();
    string lastName = firstNameControl.GetType().InvokeMember("Value", System.Reflection.BindingFlags.GetProperty,
        null, lastNameControl, null).ToString();
    string fullName = String.Format("{0} {1}", firstName, lastName);
 
    Marshal.ReleaseComObject(firstNameControl);
    Marshal.ReleaseComObject(lastNameControl);
 
    NorthwindNet.UI.frmEmployeeLeave employeeLeaveForm = new NorthwindNet.UI.frmEmployeeLeave(fullName);
    employeeLeaveForm.Show();
}

In the above code, we get a reference to the First Name and Last Name textbox controls on the MS Access form called Employee Details and retrieve their values using InvokeMember. However, if you need to set values on a MS Access form you can use the following code:

//Set Value in First Name Textbox to John
Access.Control firstNameControl = (Access.Control)AccessApp.Forms
["Employee Details"].Controls["First Name"];
object[] Parameters = new Object[1];
Parameters[0] = "John";
firstNameControl.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, accessControl, Parameters);
Marshal.ReleaseComObject(firstNameControl);

Below is a screenshot of what the Employee Details form looks like in MS Access:

When the user clicks on the Employee Leave Application button, the leave application form (written in C#) of our new system is displayed:

This is accomplished by adding a reference to the UI library of our new system called NorthwindNet.UI. In order for this to work, the UI library needed to be strongly signed.

You’ll also notice that the Shipper Rates/Taxes Maintenance button is disabled. It will only be enabled as soon as the Shipper Detail form is open in MS Access. To accomplish this, we need to add a Microsoft Access Events component to the AddinModule designer surface:

In the Access Events components’ CurrentObjectChanged Event handler, add the following code:

private void adxAccessEvents_CurrentObjectChanged(object sender, string name,
    AddinExpress.MSO.ADXAcObjectType objType, AddinExpress.MSO.ADXAcObjectState objState)
{
    switch (name.ToUpper())
    {
        case "EMPLOYEE DETAILS":
            btnEmployeeLeave.Enabled = true;
            break;
 
        case "SHIPPERDETAILS":
            btnShipperRatesTaxes.Enabled = true;
            break;
 
        default:
            btnEmployeeLeave.Enabled = false;
            btnShipperRatesTaxes.Enabled = false;
            break;
    }
}

There you go! An easy and effective way to add new .Net features to an existing MS Access application.

Thank you for reading. Until next time, keep coding!

You may also be interested in:

• Developing Microsoft Office COM add-ins