Access MS Access database from Revit plugin

[Thomas_LECUPPRE_Let_it_BIM]

Hi, I'm trying to access my MS Access database with OleDbConnection in a little plugin (just for test) and it always return me the same issue.

There is my little snippets :

public UserDBManager(CPFMainModelView cmmw)
        {
            mainModelView = cmmw;
            mainModelView.Texte = "Debut de UserDBManager";
            try
            {
                RetrieveprojectList();
            }
            catch (Exception ex)
            {
                mainModelView.Texte += $"\n{ex.Message}\n\n{ex.StackTrace}\n\n{ex.InnerException}\n\n{ex.Data}";
            }

        }

        public void RetrieveprojectList()
        {
            mainModelView.Texte += "\n-1";
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                mainModelView.Texte += "\n0";
                con.Open();
                mainModelView.Texte += "\n1";
                OleDbCommand command = new OleDbCommand("SELECT Ref FROM FolderCategory", con);
                mainModelView.Texte += "\n2";
                OleDbDataReader reader = command.ExecuteReader();
                mainModelView.Texte += "\n3";
                while (reader.Read())
                {
                    mainModelView.Texte += $"\n{reader["Ref"]}";
                }
            }
        }

There is the different connectionString I use to connect to it :

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ThomasLECUPPRE(Letit\source\LIB_MainDB.accdb"
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\ThomasLECUPPRE(Letit\source\LIB_MainDB.accdb"
@"Driver = {Microsoft Access Driver (* .mdb, * .accdb)};Provider=Microsoft.ACE.OLEDB.12.0; DBQ = C:\Users\ThomasLECUPPRE(Letit\source\LIB_MainDB.mdb"
@"Driver = {Microsoft Access Driver (* .mdb, * .accdb)};Provider=Microsoft.Jet.OLEDB.4.0; DBQ = C:\Users\ThomasLECUPPRE(Letit\source\LIB_MainDB.mdb"

All of them return me the same issue

Debut de UserDBManager
-1
0
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at CPF.UserDBManager.RetrieveprojectList()
at CPF.UserDBManager..ctor(CPFMainModelView cmmw)

System.Collections.ListDictionaryInternal

All this code is working for wpf app but not for revit. Why ?
I already download all Revit DB link version, and many other think I found on forums.

Thank you !

[jeremy_tammik]

I do not use MS Access or any OLE connections myself, hence cannot say anything about your problem except point out what the error message is telling you. The error message says, The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

This means that you have a basic installation problem with your OLE connection provider configuration.

This in turn means you can try as many different connection strings as you like; it will make no difference to the underlying fundamental problem.

You also say that the connection is working from a WPF app and not from the Revit add-in. Maybe there is some difference in the DLLs being loaded. 

In the worst case, your working WPF app may be loading certain specific DLLs and Revit itself may be loading other incompatible ones. That might prevent your add-in from loading the ones it requires. Here is a note on exploring  and resolving such an issue:

https://thebuildingcoder.typepad.com/blog/2021/05/revitlookup-update-fuslogvw-and-override-joins.htm...

Just my 2c worth... I hope others can provide better and more useful advice.

[Thomas_LECUPPRE_Let_it_BIM]

As I just read from these different topic, should I copy some dll file in Revit.exe folder ?

What type of database do you use in your programs (if you use it) ?

Thank you for your reply.

[mhannonQ65N2]

 A quick search shows that Microsoft.Jet.OLEDB.4.0 is 32-bit only. As such, it looks like it won't work with a plugin to Revit (a 64-bit application). If possible, try just using the Microsoft.ACE.OLEDB.12.0 provider.

[jeremy_tammik]

https://github.com/jeremytammik/FireRatingCloud

[Thomas_LECUPPRE_Let_it_BIM]

@mhannonQ65N2 

Already done, check all the connectionString I have tested

Check this here

@jeremy_tammik 

Thank you, I think I already see that somewhere but it use only db and I work with a local file, does it also work with local db ?

[jeremy_tammik]

Yes. I implemented all my mongoDB apps to include a switch between local and remote db, and that worked fine for both cases. Testing is often easier locally. However, nowadays, it may even be easier remotely... no need to install and set up...