Database connection to Microsoft Access 2013 DB

Ignition
1

Is there a way to setup a database connection to a 32-bit Microsoft Access 2013 database? I don’t need to write data to this database, just need to read some data from the database. I have seen some posts that recommend against this, but I can’t change the Microsoft Access DB, it is part of a large system that would take way too long to convert to SQL server.

if so, what settings would I need to use for the connection URL and any other settings I would have to change from the default?

For the connection URL I tried jdbc:odbc://server_name/dir_name_that_has_access/databasename.accdb but this faulted.

The other problem I have is there is no username or password on the database, so I just left those fields blank.

2

Check the docs Can I connect using ODBC?

1 Like
3

Thanks, I have seen that same message in a number of posts and documents. I really do only need limited access, so I was just trying to get it to work to see if it works for my application.

I certainly prefer to link using a fully functional connection. So what driver do you recommend?

I see an article about using the UCanAccess driver (MS Access database connection with Ignition 7.8) but was afraid to install on our Ignition server. Anyone else have any experience using the UCanAccess open source driver or any other driver for that matter?

4

Try installing SQL Server Express and add the MS Access database as a linked server. On the gateway, make a DB Connection to sql express.

2 Likes
5

We currently use SQL server 2012 in 80% of our applications (and 100% of all new applications). I will try to link Access to SQL Server. Thanks for input, that is a really good idea.

6

And for performance use OPENQUERY against your ACCDB https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-2017