Connect to C# Program Data Source from Microsoft Access

Question

I have a small C# console program that retrieves an item list from quickbooks, and I am trying to figure out how to expose that data to Microsoft Access. It is in XML format.

I want to retrieve the data in real-time, since it only takes about a second to get the data, whenever Access calls for it. I am using Access 2003 and VS 2010.

If there is a way to do this with VBA that would work fine as well. I can get the XML data using VBA already, but I don't know how to go from there.

Here is the code I use in C#:

public string DoQBQuery(XmlDocument doc)
{
    bool sessionBegun = false;
    bool connectionOpen = false;
    RequestProcessor2 rp = null;
    string ticket = "";
    try
    {
        //Create the Request Processor object
        rp = new RequestProcessor2();

        //Connect to QuickBooks and begin a session
        rp.OpenConnection2("", "QB Transaction Item Retriever", QBXMLRPConnectionType.localQBD);
        connectionOpen = true;
        ticket = rp.BeginSession("", QBFileMode.qbFileOpenDoNotCare);
        sessionBegun = true;

        //Send the request and get the response from QuickBooks
        string responseStr = rp.ProcessRequest(ticket, doc.OuterXml);

        //End the session and close the connection to QuickBooks
        rp.EndSession(ticket);
        sessionBegun = false;
        rp.CloseConnection();
        connectionOpen = false;

        return responseStr;

    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message, "Error");
        if (sessionBegun)
            rp.EndSession(ticket);

        if (connectionOpen)
            rp.CloseConnection();

        throw;
    }
}

So are you saying that you want to use C# to copy the data to your Access database? — Brian, Jul 01 '13 at 19:05
[Here](http://msdn.microsoft.com/en-us/library/aa288452(v=vs.71).aspx) is a tutorial for sending data to an Access DB. — Brian, Jul 01 '13 at 19:08
I want to expose the data like a db server so it can be retrieved in realtime. — Arlen Beiler, Jul 01 '13 at 19:29
What mechanism does the C# program use to retrieve the QuickBooks data? — Gord Thompson, Jul 02 '13 at 09:01
A COM type call. QBXMLRPC2, using OpenConnection2 and BeginSession. I'm pretty sure I got back xml just fine using VBA, but I don't know how to use it past that. I'm already using it in C# and it definitely works. — Arlen Beiler, Jul 02 '13 at 15:11

score 0 · Accepted Answer · edited May 23 '17 at 11:58

0

"I am trying to figure out how to expose that data to Microsoft Access. It is in XML format" I've never tried this before, but I think this is possible by putting the c# classes that accomplish what you discussed in a dll and calling from Access. Here is a post from this site about that.

A Simple C# DLL - how do I call it from Excel, Access, VBA, VB6?

P.S. Given I've never tried this before, I would have put this in the comment section of your original post, but I'm new to this site and am unable to do that.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 01 '13 at 20:22

This is technically an answer anyway. Thanks, I'll look into it. – Arlen Beiler Jul 01 '13 at 20:24
Let me know what happens. I'll be interested to see! – Jul 01 '13 at 20:27
Actually, this would get me about halfway if I need it, but I still need to create something that Access can actually use and retrieve on the fly. – Arlen Beiler Jul 02 '13 at 15:15
Are you talking about a thread that constantly polls the methods in the dll? – Jul 02 '13 at 15:30
No, I'm asking how to get make an external datasource for access. I want to make a program that acts like a database or something like that. Can programs host an ODBC database? I think maybe. I may as well forget about trying to do it live, but I may be able to do something else. – Arlen Beiler Jul 03 '13 at 20:19

Connect to C# Program Data Source from Microsoft Access

1 Answers1