Connecting to MS Access (.accdb) in Delphi 12

[Squall_FF8 1]

Hi guys,

I'm trying to connect my application to MS Access DB. If the file is with ".mdb" format - everything works fine.

However I need to use the "accdb" and so far - nothing worked (ADO/FD connection).
Can you advise how to do it?

 

P.S. if it matters, I will need read/write to DB, but no exclusive. 

[dummzeuch 1635]

You need the Access Database Engine 2012 (or later, if it exists).

I don't remember where I got it from, but a quick Google search just gave me this link to Microsoft.

[Squall_FF8 1]

21 hours ago, dummzeuch said:

You need the Access Database Engine 2012 (or later, if it exists).

I don't remember where I got it from, but a quick Google search just gave me this link to Microsoft.

Thank you for your response!

It seems the problem is much bigger then I thought. I did quite some digging and tests and here is short summary:
1. [Windows] ODBC keep two separate sets of providers for 32/64 bit.
2. [Windows] Depending on the version of your app, you can use only one set.

3. [Windows] Natively, in 32 version you can handle only the old (.mdb) Access format.
4. [MS Office] If you have Office installed, you are stuck with whatever version you had installed. Not 100% sure, but latest versions are x64 only.

5. [MS Office] That means, I cant install the drivers in the link. Not without removing the Office first!

6. [Delphi] Comes as 32 bit application! That mean in Design time, you can set connections only for 32bit set.

7. [Delphi] For 64 bit, runtime ONLY setup!

Good luck juggling all above to deliver a seamless application to your clients ...

Edited March 24 by Squall_FF8

[Anders Melander 1986]

3 hours ago, Squall_FF8 said:

Good luck juggling all above to deliver a seamless application to your clients ...

That's why they pay us the big bucks 🙂 

 

Here's some links that might be relevant:

https://learn.microsoft.com/en-us/office/troubleshoot/access/cannot-use-odbc-or-oledb

https://how-to.aimms.com/Articles/129/129-MSACCESS-32bit-64bit.html

[Squall_FF8 1]

8 hours ago, Anders Melander said:

That's why they pay us the big bucks 🙂 

 

Here's some links that might be relevant:

https://learn.microsoft.com/en-us/office/troubleshoot/access/cannot-use-odbc-or-oledb

https://how-to.aimms.com/Articles/129/129-MSACCESS-32bit-64bit.html

Hehehe, nice one 🙂
Thank you for the links! I'm sure they will come in handy (the download in the second one doesnt work). Especially I would like to try the "/passive" trick with 2013 or 2016 32 bit drivers. 

[TimLewis 0]

I had the same issue with connecting to .accdb files. After trying a few things, I ended up using unidac and it worked good. It supports both .mdb and .accdb formats, and I was able to read/write to the database without any problems. If you're stuck with this, you can check it.

[DelphiUdIT 229]

I'm pretty sure that this is the correct driver (runtime) that should be used to connect with "mdb or accdb" using OLEDB or ODBC:  https://www.microsoft.com/en-us/download/details.aspx?id=54920

 

Connection strings are noted in the page linked.

 

With OLEDB you can use: " Microsoft.ACE.OLEDB.12.0" or " DAO.DBEngine.120"

 

This was reported by an Italian guy who used it with Lazarus/FPC.

 

P.S: I haven't used access in over 20 years, so I can't provide any further help.

Edited April 17 by DelphiUdIT

[Brian Evans 122]

The annoyance is Microsoft Office gets installed as EITHER 32-bit or 64-bit and this includes the database access infrastructure for it like the Access ODBC driver. You never get both 32-bit and 64-bit at the same time and installing Office will uninstall the one it doesn't need while installing the one if does need. 

 

The Unidac drivers from Devart include a direct mode that doesn't use the ODBC drivers but has some limitations like not supporting DDL statements. This gets around needing a matching 32-bit or 64-bit Access ODBC driver.