How to set the current record in a Microsoft Access form from external program

Question

The background: The charity I work for has two systems, a new C# system and an MS Access system. Taking a phone call from someone usually means looking them up on the new system, then looking them up a second time on the legacy system. Unfortunately we're stuck with the Access system, because we don't have the money to re-develop it.

Both systems use a unique PersonID for each person, and the IDs are synchronised between systems. The Access system is basically one giant form which shows the info for a particular PersonID.

The problem: What I want to do is tell Access to move the current record to a particular PersonID from an external c# program. I don't want to launch a new Access window, as this is very slow on our PCs.

I've tried to think what to Google to control Access from the outside (something like DDE??) but i'm drawing a blank. Can anyone give me any pointers what to look for? Is this even possible?

Answer 1

You can use COM automation. If your new system were e.g. Excel, you could use VBA code like this:

Sub TestCOMtoAccess()

    ' Has References to Microsoft Access Object Library & Microsoft DAO 3.6 Object Library

    Dim oAccess As Access.Application
    Dim oForm As Access.Form
    Dim RS As DAO.Recordset

    ' This assumes that exactly one instance of Access is running, with your old application
    Set oAccess = GetObject(, "Access.Application")
    Set oForm = oAccess.Forms("your_giant_form")

    ' find the record you are looking for
    Set RS = oForm.RecordsetClone
    RS.FindFirst "myPrimaryKey = 42"
    ' and navigate the form to this record
    If Not RS.NoMatch Then
        oForm.Bookmark = RS.Bookmark
    End If
    RS.Close

End Sub

and according to How to interact with another programs with OLE Automation in C#? this is easily adapted to C# and .NET.

For a C# Windows Forms Application, the equivalent code would be:

using System;
using System.Windows.Forms;

namespace ComAutoWindowsFormsApp
{
    public partial class MyCsharpForm : Form
    {
        Microsoft.Office.Interop.Access.Application accApp;
        public MyCsharpForm()
        {
            InitializeComponent();
        }

        private void MyCsharpForm_Load(object sender, EventArgs e)
        {
            accApp = 
                (Microsoft.Office.Interop.Access.Application) 
                System.Runtime.InteropServices.Marshal.GetActiveObject("Access.Application");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Access.Form accForm = accApp.Forms["your_giant_form"];
            Microsoft.Office.Interop.Access.Dao.Recordset accRs = accForm.RecordsetClone;
            accRs.FindFirst("myPrimaryKey = 42");
            if (!accRs.NoMatch)
            {
                accForm.Bookmark = accRs.get_Bookmark();
            }
            accRs.Close();
        }
    }
}

Answer 2

Read this guide if you want to make the C# program control Access. This is way easier than the reverse, because Access already exposes most of it's objects and methods to other programs. The C# program could just navigate the Access form.

If you want to get information from the C# program to Access, you would need to add COM+ functionality to your C# program, create a COM+ library, reference that in Access, and then you could make calls from Access to your new program. See this page if you want to do that, but if your budget is limited, you probably don't.

Answer 3

I would propose quite simple solution: add a table to access db for storing just one row with PersonID. C# application inserts a desired PersonID to this table, Access application by timer checks this table, let's say every 500 ms and opens a screen with related data as soon as it found the data in this exchange table. Access deletes the row with PersonID immediately after reading. Access can ask the user for scrolling confirmation in case if user, for instance, din't save current record yet.

I believe COM should work either, but not sure if this would be reliable, especially if user can interact with Access application at the same time as C# application.