Connection to MS Access database

[helene.macher]

I would like to connect a Microsoft Access database into Revit. This database contains some historical information about elements and a column with the Element Ids in Revit. The main idea is to display information of this database when clicking on an element.

I wrote the following C# code to connect to my database and read information:

// Connection string and SQL query
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Users\MHelene\Desktop\Data\Tests\WallsDB.accdb";
string strSQL = "SELECT * FROM Table1";

// Create a connection
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
   // Create a command and set its connection  
   OleDbCommand command = new OleDbCommand(strSQL, connection);

   // Open connection
   try
       {
         if (connection.State != ConnectionState.Open)
         {
           connection.Open();
         }
         // Execute command
         using (OleDbDataReader reader = command.ExecuteReader())
         {
            while (reader.Read())
            {
              string RevitID = reader.GetValue(5).ToString();
              string name = reader.GetString(2);
             }
          }
        }
    catch (Exception ex)
      {
        message = ex.Message;
        return Result.Failed;
      }
  }

Unfortunately, I received an error for the bold line namely, when I try to open my connection. See the screenshot of the error:

I spent a few days trying to fix it and I tested all the solutions proposed on internet but I begin to lose hope. In Visual Studio, I can connect a database without any problem (Tools>Connect to Database…). However, within Revit plug-in I am still facing the same problem of database connection. So did I miss something with Revit?

[jeremytammik]

Dear Helene,

There is no reason that I am aware of why this should not run inside a Revit add-in.

However, you say nothing about the Revit add-in framework and context within which you are running the code.

Is this code executed inside an external command Execute method or some other callback function handler providing a valid Revit API context?

Cheers,

Jeremy

[helene.macher]

Dear Jeremy,

Thank you for your answer. Here is my whole code. I first prompt the user to select a wall. Then, I try to open my database. I would like to find some information about that wall in my database (with the element Id). I try to figure it out if the error I get is linked to Revit.

using System;
using System.Data;
using System.Data.OleDb;
using System.Threading;

using Autodesk.Revit.DB;
using Autodesk.Revit.UI;
using Autodesk.Revit.UI.Selection;
using Autodesk.Revit.Attributes;

[TransactionAttribute(TransactionMode.Manual)]
[RegenerationAttribute(RegenerationOption.Manual)]

public class Open_database : IExternalCommand
{
    public Result Execute(
     ExternalCommandData commandData,
     ref string message,
     ElementSet elements)
    {
        UIDocument uidoc = commandData.Application.ActiveUIDocument;

        try
        {
            MySelectionFilter selFilter = new MySelectionFilter();
            Reference selRef = uidoc.Selection.PickObject(ObjectType.Element, selFilter, "Please select a wall");

            // Connection string and SQL query
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Users\MHelene\Desktop\Data\Tests\WallsDB.accdb";
            string strSQL = "SELECT * FROM Table1";
           
            // Create a connection
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                // Create a command and set its connection  
                OleDbCommand command = new OleDbCommand(strSQL, connection);

                // Open connection
                try
                {
                    if (connection.State != ConnectionState.Open)
                    {
                      connection.Open();
                    }
                    // Execute command
                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        string RevitID;
                        string name;
                        while (reader.Read())
                        {
                            RevitID = reader.GetValue(5).ToString();
                            name = reader.GetString(2);
                        }
                    }
                }
                catch (Exception ex)
                {
                    message = ex.Message;
                    return Result.Failed;
                }
            }
        }
        //If the user right-clicks or presses Esc, handle the exception
        catch (Autodesk.Revit.Exceptions.OperationCanceledException)
        {
            return Result.Cancelled;
        }
        //Catch other errors
        catch (Exception ex)
        {
            message = ex.Message;
            return Result.Failed;
        }

        return Result.Succeeded;
    }

    public class MySelectionFilter : ISelectionFilter
    {
        public bool AllowElement(Element elem)
        {
            return (elem.Category.Id.IntegerValue.Equals(
            (int)BuiltInCategory.OST_Walls));
        }

        public bool AllowReference(Reference reference, XYZ position)
        {
            return false;
        }
    }

}

Best regards,

Hélène

[norman.yuan]

I haven't done any Revit API coding (intended to, that is why I wandered into here), but used to use MC Access DB with AutoCAD a lot.

When running into error like you did with Autodesk apps and MS Access DB, the first thing one would ask is: do you have connect MC Access DB Engine installed? It is highly likely you are using 64-bit Revit, so you need to install 64-bit MC Access DB Engine. Having MC Access installed in the computer does not necessarily mean the computer has 64-bit Access DB Engine, because most likely the MS Office products in the computer are 32-bit.

Since your post did not mention this, it would not be easy to troubleshoot further before knowing whether you have correct MS Access DB Engine installed or not.

[helene.macher]

Thank you for your feedback.

Indeed, I’m using 64-bit Revit. I already installed MS Access DB Engine 2016 64 bit.

When I try to connect my database manually in Visual Studio (Tools>Connect to database…), this doesn’t work with only this DB Engine; the provider is not found. I need to install the 2007 version to be able to connect a DB in Visual Studio. However, it seems that the 2016 DB Engine 64 bit is correctly installed.

I’m still investigating. I will try to reinstall the DB Engine. There is perhaps something wrong with my installation.

Best regards,

Hélène

[norman.yuan]

---

@helene.macher wrote:

...

When I try to connect my database manually in Visual Studio (Tools>Connect to database…), this doesn’t work with only this DB Engine; the provider is not found. I need to install the 2007 version to be able to connect a DB in Visual Studio. ...

...

---

Clearly, your issue has nothing to do with Revit itself, but with MS Access DB Engine.

The MS Access DB Engine came out first with MC Access 2007. At that time, if MS Office 2007 32-bit is installed, one cannot install 64-bit ACE (Access Database Engine) in the same computer. This was changed only with later version of MS Office. Since you have ACE 2016 64-bit installed, I suspect that the *.mdb/*.accdb file could be created by older version of MC Access (2007 or older). I'd try to open the DB file in MS Access 2013 or later and save (with minor change to enforce saving). Then see what happens.

[bim01]

I have the same problem, have you got any solution?

[Flies-Eyes]

It seems I am having issues with Revit 2021 connecting Access for the SMLX import mapping.  Was working now it doesn't want to connect.  Strange.