0

I have an app that's been working fine in 32 bit using the connection string: 

Driver={Microsoft Access Driver (*.mdb)}; Dbq=MyDatabase.mdb;

using ADO in C++ on a PC with no Office installation.

I have converted it to x64 and I believe I need to install some form of Access drivers no matter if the host PC has x64 Office installed or not. So I grab Microsoft Access Database Engine 2016 Redistributable and install it.

In general everything works OK but certain SQL queries are failing. Most noticeably ones that use LIKE '%somevalue%' - now I understand that % is ANSI-92 but it's been working fine as I say so assume that's ADO related (I could change this to ALIKE I guess).

What I don't understand is if I install the Access Database Engine 2010 then all works as expected. It seems that something is different in the Access Database Engine 2016. I did look for some release notes/breaking changes but couldn't find anything.

So my question is are there changes in the way SQL is parsed in Microsoft Access Database Engine 2016 and should I simply get customers to install the 2010 version?

Note: the other query that seems to be failing is a table with a Yes/No field. I have a query that checks: field <> 0 and this throws an exception.

Update: If I install the Microsoft Access 2016 Runtime then all seems to work. So the issue seems specific the Microsoft Access Database Engine 2016

So in summary:

No Office Installed: Installed Access 2016 x64 Database Engine: Query FAILS
No Office Installed: Installed Access 2010 x64 Database Engine: Query PASSES
32bit Office Installed: Installed Access 2016 x64 Database Engine: Query FAILS
32bit Office Installed: Installed Access 2010 x64 Database Engine: Query PASSES
x64 Office Installed: Installed Access 2016 x64 Database Engine: Query PASSES

Where the Query is:

SELECT * from sometable WHERE somefield LIKE '%ABC%';

The setup is:

Window 10 Version 1909
32 bit Office: Microsoft Office MSO 16.0.12325.20280 32bit 
64 bit Office: Microsoft Office MSO 16.0.12325.20280 64bit

Access database engine (x64) from here: microsoft.com/en-us/download/details.aspx?id=54920

My app is x64

Alastair Taylor
  • 345
  • 1
  • 3
  • 10
  • W10 Version 1909, Microsoft Office MSO 16.0.12325.20280 32bit. Then installed Access database engine (x64) from here: https://www.microsoft.com/en-us/download/details.aspx?id=54920 Query that fails is basically SELECT * from sometable WHERE somefield LIKE '%ABC%'; If I uninstall 2016 Database Engine and install 2010 Database Engine this query works fine – Alastair Taylor Jan 30 '20 at 16:37
  • Office is Click-to-run? [Can't use the Access ODBC driver or OLEDB provider outside Office Click-to-Run applications](https://learn.microsoft.com/en-us/office/troubleshoot/access/cannot-use-odbc-or-oledb) is an issue if bitness matches (doesn't apply to Database Engine Redistributable, as that is an Msi-version) – ComputerVersteher Jan 30 '20 at 17:29
  • Code to run sql still missing, especially connection string. For ADO use OleDB provider. For C++.Net you can use`.NET Framework Data Provider for OLE DB` – ComputerVersteher Jan 30 '20 at 19:31
  • Maybe this help https://stackoverflow.com/questions/6649363/microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine?rq=1. If access is used as database only, consider replace with e.g SQLite, as even you solve that provider issue, your customers will run into bitness issue, because you shouldn't install x86/x64 office components on same machine (updates will lead to strange behaviour), And I guess you don't want to tell customers, to switch office bitness. Use Access as frontend, but not as backend. – ComputerVersteher Jan 30 '20 at 20:25

1 Answers1

0

Are you using SQL Server as the backend database, or Access?
In SQL Server % is the wildcard symbol.

With a pure Access environment, the wildcard standard is usually *
There may be a setting to change this, but I can't recall.

reference: https://support.office.com/en-us/article/like-operator-b2f7ef03-9085-4ffb-9829-eef18358e931

trevor
  • 257
  • 3
  • 9