Hello, i’m new to Dynamo and this is my first attempt to make a connection to an access database trough an OLEDB connection. But nothing works. Can anyone tell me what i’m doing wrong here. Tanx
I am not sure if the same issues of 32 vs 64 bit drivers for MSAccess applies in the same way to OLEDB connections in the same way as ODBC connections.
My experience (mainly Navisworks) is that connections to MSAccess can be problematic & have ended up using SQLExpress server instead.
This post might help- database connections using Python
Tanks for your answere i will give it a try.
I needed to do this myself…
It is pretty easy to convert the MSAccess table into a SQLExpress database using the MSAccess migration tools.
You can link the table back into MSAccess if you need to.
The below works:
I see this post is a couple of years old, but I still have the same problem, and curious to know how other solve this issue.
The task I’m working with is to sync Access database with revit models. In my company 32b version of office is standard, and then you cant install Access database engine for 64b, and then the oledb connection to Access databases doesn’t work.
We would like to keep using Access as it is a software people are familiar with, and I don’t think it would be appreciated to change to e.g. SQL express.
I see some people manage to install both engine by modify the registry files, but thats not really a good solution in order to make the dynamo scripts available for many users.
Any input is appreciated!
In my experience, ODBC connections to MSAccess are still a problem- there are many variables across PCs such as operating system, version of Office, drivers etc that mean it can work, but is not reliable.
It does not answer your question, but I have been using SQLite a lot recently- it has the benefit of being super-simple (a single file) and reliable.
Is it possible to convert an .mdb database with its structure, reports and queries into an SQLite db?
We have a quite well established access DB to which we would like to export electrical data from our revit model.
As I recall it- SQLite can contain tables and views (queries) but not other elements from Access, such as reports or stored procedures.
To convert from MSAccess to SQLite- I would need to check- but possibly Pentaho (refer my other post from today) could do this for the tables.
For the queries- it is probably pretty simple to copy the SQL across i.e no data
Thanks for the answer
Thanks for your input Andrew!
In our case, we use the access reports quite extensively, as well as other features in the access software.
The workaround for parallell installation of 64b and 32b seems not to be working for office 2016.
Right now, my main option seem to be data transfer back and forth via excel or something, but this makes a one-step procedure into a 4-step procedure…
I have played around with this.
It seems pretty easy to use SQLite with PowerBI, or MSAccess (as well as Dynamo)
The SQLite database can easily replace the tables in MSAccess and then this data used as a linked table in Access. Similar approach for PowerBI or any other application (Excel etc etc)
Correction to the above- SQLite can contain views (aka queries)
In the below example, I write (room data) out to an external database using Dynamo- then use this data in PowerBI or Access. I have used a simple insert query- which is why I have to delete the data before Dynamo runs so it is not doubled up, but it would be easy to change this to an update/replace query
Using SQLite instead of Excel