Cant make connection to acces database

[Gilles.Lagrilliere]

Hello

I can't make a connection to an Acces database in Revit.

Every time I use "connection.Open();" Revit crashes without a warning or error message.

Here is the code I used to test if I can make a connection to Acces database:

namespace TestDatabaseConnection
{
    [Transaction(TransactionMode.Manual)]
    public class TestDatabaseConnection : IExternalCommand
    {
        public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
        {
            UIApplication uiapp = commandData.Application;
            UIDocument uidoc = uiapp.ActiveUIDocument;
            Document doc = uidoc.Document;

            // Connection string and SQL query       
            string connectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" +
                 @"F:\Revit\Families_Codraft\Pipe Fittings\COD_Y-STUK\Test\BMPfittings_PE_riol.mdb";

            string strSQL = "SELECT *" +
                " FROM BMP_TeeTbl" +
                " WHERE (((BMP_TeeTbl.PIPE_OD_M)=110)" +
                " AND ((BMP_TeeTbl.PIPE_OD_R)=110)" +
                " AND ((BMP_TeeTbl.PIPE_OD_B)=110)" +
                " AND ((BMP_TeeTbl.Angle)=45)" +
                " AND ((BMP_TeeTbl.EndType)=\"-;-;-;\")" +
                " AND ((BMP_TeeTbl.THD_ENG1)=0)" +
                " AND ((BMP_TeeTbl.THD_ENG2)=0)" +
                " AND ((BMP_TeeTbl.THD_ENG3)=0))";

            // Create a connection
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                // Create a command and set its connection    
                OleDbCommand command = new OleDbCommand(strSQL, connection);
                // Open the connection and execute the select command.    
                try
                {
                    // Open connecton    
                    connection.Open();
                    // Execute command    
                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            TaskDialog.Show("test", reader["Object"].ToString() + " " + reader["COMP_LEN"].ToString());
                        }
                    }

                    return Result.Succeeded;
                }

                catch (Exception ex)
                {
                    TaskDialog.Show("test",ex.Message);
                    return Result.Failed;
                }
                // The connection is automatically closed becasuse of using block.    
            }
        }
    }
}

 If I test it with a Console App it works perfectly fine.

[Gilles.Lagrilliere]

Has nobody a solution for this?

I know Revit can handle acces databases.

[stever66]

I tried your code ( with a simpler select statement, and with a simpler file path) and got the same result - an error message that basically said  the connection was refused.

And a google search seemed to pull up a thread with the same code on another forum, and they seemed to be having the same problem.

Can you try running this same code from within another program ( like excel) or as a standalone app and see if you get the same error?  That would tell us if it’s a Revit issue or not.

[Gilles.Lagrilliere]

If I use a console app it works fine

The console gives me the data I want.

using System;
using System.Data.OleDb;

namespace ReadingData
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection string and SQL query       
            string connectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" +
                @"F:\Revit\Families_Codraft\Pipe Fittings\COD_Y-STUK\Test\BMPfittings_PE_riol.mdb";

            string strSQL = "SELECT *" +
                " FROM BMP_TeeTbl" +
                " WHERE (((BMP_TeeTbl.PIPE_OD_M)=110)" +
                " AND ((BMP_TeeTbl.PIPE_OD_R)=110)" +
                " AND ((BMP_TeeTbl.PIPE_OD_B)=110)" +
                " AND ((BMP_TeeTbl.Angle)=45)" +
                " AND ((BMP_TeeTbl.EndType)=\"-;-;-;\")" +
                " AND ((BMP_TeeTbl.THD_ENG1)=0)" +
                " AND ((BMP_TeeTbl.THD_ENG2)=0)" +
                " AND ((BMP_TeeTbl.THD_ENG3)=0))";
            // Create a connection
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {  
                // Create a command and set its connection    
                OleDbCommand command = new OleDbCommand(strSQL, connection);  
                // Open the connection and execute the select command.    
                try 
                {  
                    // Open connecton    
                    connection.Open();  
                    // Execute command    
                    using(OleDbDataReader reader = command.ExecuteReader())
                    {  
                        Console.WriteLine("------------Original data----------------");  
                        while (reader.Read()) 
                        {  
                            Console.WriteLine("{0} {1}", reader["Object"].ToString(), reader["COMP_LEN"].ToString());
                        }  
                    }  
                } 
                
                catch (Exception ex)
                {  
                    Console.WriteLine(ex.Message);
                    Console.ReadKey();
                }  
                // The connection is automatically closed becasuse of using block.    
            }  
            Console.ReadKey();  ;
        }
    }
}

[minet.axel]

I have the same problem with this:

string connectionString = @"Driver={Microsoft Access Text Driver (*.txt, *.csv)};" +
 $@"Dbq=C:\;" +
 @"Extensions=csv,txt;";

Works great in a standalone application (WPF APP).

[Gilles.Lagrilliere]

Hey,

I did found a solution.

You need to install Access 2013 runtime. https://www.microsoft.com/en-us/download/details.aspx?id=39358.

I hope this helps.

[jeremy_tammik]

Wow, congratulations on solving and sorry we were not able to help. Thank you for sharing your solution!

For future problems: it is almost always helpful to try to fix them manually in the user interface before trying to attack them programmatically. That will often give you more precise error messages and better access to support.

[minet.axel]

But why does it work standalone app without installing Access 2013 runtime? I don't understand why this code doesn't work on Revit.

[jeremy_tammik]

Maybe:

• An Access library is required
• The stand-alone is free to load the one it wants
• Revit itself is hard-wired to use the Access 2013 version only, preventing the add-in code to load a different default one

Pure theory, needs testing.

[minet.axel]

Ok I understand better now thank you

[moj]

Wow, thank you... this tread saved my day.

I was having the same problem as you connecting to an access database, but installing the 2013 runtime solved it.