Communicating with 32 bit Access from 64 bit .NET

Question

I have a .NET (C#) application that has to be compiled 64 bit. This uses ADO.NET to work with various databases including Excel & Access with OLEDB (yes, I know - Excel doesn't like being treated like a database but customers really want to do it, so I allow it with lots of warnings)

All is fine until we come across a system with 32 bit Office. A 64 bit app cannot use the 32 bit OLEDB drivers.

Now, 32 bit Excel communication does work using COM. So I've run some tests, and I can write an alternative interface for Excel that uses Excel COM.

So, I also tried Access. Some descriptions of this online hint that DAO will work. However, digging deeper this does not appear to be true. DAO hits the same 32 bit /64 bit problems.

Using the Access COM interface, I can query a list of table names but that is about it. Eg.

  Access.Application accessApp = new Access.Application();
  accessApp.Visible = false;
  accessApp.OpenCurrentDatabase("mydb.accdb", false, "");

  Access._CurrentData dt = accessApp.CurrentData;
  foreach ( dynamic tbl in dt.AllTables )
  {
      string name = tbl.Name;
      if (! name.StartsWith("MSys"))
          Console.WriteLine(">" + tbl.Name+"< ");
  }
  accessApp.Quit();

Is this the limit of what I can do? I would like to be able to query the table columns and their types. Query data rows (simple "fetch everything" would be sufficient), and to update rows (simple UPDATE WHERE would be sufficient).

I don't need to support JET.

How can I read data rows and update them using Access COM interface without using DAO or OLEDB, both of which will not communicate across the 32 bit / 64 bit boundary?

Answer 1

Unfortunately, you can’t do this.

While the interop DOES have a bag of tricks, and uses a form of “marshalling” to allow the in-process x64 to launch + automate a 32 bit version of word, or in this case Access, the interop marshalling system does not support the database engine.

So, here is how you could code things “if” this would work:

    Dim accessApp As New Access.Application
    accessApp.OpenCurrentDatabase("\\ALBERTKALLAL-PC\test\vbtest\test44.accdb")
    Dim strSQL As String
    accessApp.Visible = True
    Application.DoEvents()

    strSQL = "select * from tblHotels3"
    Dim rst As Access.Dao.Recordset

    Dim strBuf As String = ""
    rst = accessApp.CurrentDb.OpenRecordset(strSQL)
    Do While rst.EOF = False
        strBuf += vbCrLf & rst("ID").Value & "," & rst("HotelName").Value
        rst.MoveNext()
    Loop
    Me.TextBox1.Text = strBuf
    accessApp.Quit()

So, the above code is how you could use interop if this was possible.

However, keep in mind that you require the full version of Access to use "createObject()" or create a instance of Access. The runtime version does not support this (so, just keep in mind that while you HAVE success in creating a instance of Access, you can't do this with the runtime. (now in your case you obviously do have full edition of access installed).

And by creating a WHOLE instance of access, then all of the startup code, and parts of that Access application will start to run. MUCH better would be to simply create a instance of the database engine, and thus you not launching Access + VBA + the WHOLE application. To use inter-op, this code would work:

    Dim db As New Access.Dao.DBEngine
    Dim MyDatabase As Access.Dao.Database

    db.OpenDatabase("\\ALBERTKALLAL-PC\test\vbtest\test44.accdb")
    Dim rst As Access.Dao.Recordset
    Dim strSQL As String = "select * from tblHotels3"
    Dim strBuf As String = ""
    rst = db.Workspaces(0)(0).OpenRecordset(strSQL)
    Do While rst.EOF = False
        strBuf += vbCrLf & rst("ID").Value & "," & rst("HotelName").Value
        rst.MoveNext()
    Loop
    rst.Close()
    db.Workspaces(0)(0).Close() 
    Me.TextBox1.Text = strBuf

Once again, however, the instant you attempt to use the data engine local to .net, then you are marshalling across the in-process (x64), and thus once again the above will not work.

However, if you wanted to modify a text file, you could have word open the text document, execute word commands to modify the doc, and save it. This would and should in theory work over the x32/x64 bridge.

In fact, the following code for Access inter-op SHOULD work, but it does not:

    Dim accessApp As New Access.Application
    accessApp.OpenCurrentDatabase("\\ALBERTKALLAL-PC\test\vbtest\test44.accdb")

    Dim strSQL As String

    strSQL = "update tblHotels3 set City = 'zoo' "

    accessApp.DoCmd.RunSQL(strSQL)
    accessApp.Quit()

Note in above, we attempt to use the Access model to do the "update". However, the instant Access attempts to run + use the database engine, it fails. The above example SHOULD work, but if the above .net (vb.net) code is run as x64, then find that:

The code does create a correct running instance of Access (.net = x64, Access = x32).

However, it seems the instant we attempt to try run + use the Access dataengine, then then a error message is tossed up by .net (so above fails on accessApp.DoCmd.RunSQL(strSQL) - that's when we try to use the data engine).

So, while inter-op can launch + get a you a running copy of Access, attempts to use the data engine fail.

I do find that you CAN tell MSAccess to run some VBA code. So this will work:

    Dim accessApp As New Access.Application
    accessApp.OpenCurrentDatabase("\\ALBERTKALLAL-PC\test\vbtest\test44.accdb")

    accessApp.Visible = True
    Application.DoEvents()
    MsgBox("access open = ok - show access ok")
    accessApp.Run("MyUpdate")
    accessApp.Quit()

And, the VBA code for above?

VBA code - not vb.net:

  Public Sub MyUpdate()

     MsgBox "about to update"
     CurrentDb.Execute "update tblHotels3 set city = 'zoozoo'"

     MsgBox "upate ok"


  End Sub

I can confirm that the above DOES work:

So, the problem here is that the Access database engine is deemed and seen as a external database object, and the inter-op marshalling does not support the data base engine from .net.

You only way around this is to install an x64 bit version of the Access data engine. You can install the engine separate and NOT have to install Access, but to my knowledge, from .net, you can't do this unless you have a x64 bit version of the data engine.