topic Re: Cant make connection to acces database in Revit API Forum

Cant make connection to acces database

Gilles.Lagrilliere — Fri, 14 Jan 2022 12:12:07 GMT

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.

Re: Cant make connection to acces database

Gilles.Lagrilliere — Wed, 19 Jan 2022 12:02:13 GMT

Has nobody a solution for this?

I know Revit can handle acces databases.

Re: Cant make connection to acces database

stever66 — Wed, 19 Jan 2022 12:51:04 GMT

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.

Re: Cant make connection to acces database

Gilles.Lagrilliere — Wed, 19 Jan 2022 12:58:46 GMT

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(); ; } } }

Re: Cant make connection to acces database

minet.axel — Thu, 02 Jun 2022 14:04:10 GMT

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).

Re: Cant make connection to acces database

Gilles.Lagrilliere — Thu, 02 Jun 2022 14:06:49 GMT

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.

Re: Cant make connection to acces database

jeremy_tammik — Thu, 02 Jun 2022 15:28:22 GMT

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.

Re: Cant make connection to acces database

minet.axel — Thu, 02 Jun 2022 16:26:32 GMT

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

Re: Cant make connection to acces database

jeremy_tammik — Thu, 02 Jun 2022 16:37:32 GMT

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.

Re: Cant make connection to acces database

minet.axel — Thu, 02 Jun 2022 20:48:53 GMT

Ok I understand better now thank you

Re: Cant make connection to acces database

moj — Mon, 15 Aug 2022 14:03:38 GMT

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.