ADODB connection - SQL "NOT LIKE" query not working

Question

I have a very weird problem. I have a Access DB (2013 64-bit .accdb) and couple of queries in there based on other query "Q_ManifestedByStatus"

All queries work as expected in Access so no issue here.

Problem is when I am pulling the data to Excel (2013 64-bit) using ADODB connection. One of the queries has a "WHERE" clause [PO No] LIKE '8*'. When the sql code is embedded into VBA it works fine, when the sql is replaced by query name from access it doesn't.

When I remove the WHERE clause from access it work fine both ways... When I change the LIKE clause to NOT LIKE it works fine both ways...

Any suggestions are welcome!

See codes below.

Access SQL:

SELECT 
Int([Collection date]) AS [Date], 
Sum(IIf([Status]="Early",1,0)) AS Early, 
Sum(IIf([Status]="On Time",1,0)) AS [On Time], 
Sum(IIf([Status]="Late",1,0)) AS Late, 
Sum(IIf([Status]="Not Manifested",1,0)) AS [Not Manifested], 
[Early]+[on time]+[late]+[not manifested] AS [Sum], 
Round([Early]/[Sum],2)*100 & "%" AS [Early%], 
Round([On Time]/[Sum],2)*100 & "%" AS [On Time%], 
Round([Late]/[Sum],2)*100 & "%" AS [Late%], 
Round([Not Manifested]/[Sum],2)*100 & "%" AS [Not Manifested%]
FROM Q_ManifestedByStatus
WHERE [PO No] Like '8*' Or [PO No] Like '9*'
GROUP BY Int([Collection date]);

VBA - SQL Embedded - WORKS

Sub tt()

Dim objAdoCon       As Object
Dim rst             As Object
Dim provider        As String
Dim sql             As String

sql = "SELECT Int([Collection date]) AS [Date], Sum(IIf([Status]=""Early"",1,0)) AS Early," & _
"Sum(IIf([Status]=""On Time"",1,0)) AS [On Time], Sum(IIf([Status]=""Late"",1,0)) AS Late," & _
"Sum(IIf([Status]=""Not Manifested"",1,0)) AS [Not Manifested], [Early]+[on time]+[late]+[not manifested] AS [Sum]," & _
"Round([Early]/[Sum],2)*100 & ""%"" AS [Early%], Round([On Time]/[Sum],2)*100 & ""%"" AS [On Time%], Round([Late]/[Sum],2)*100 & ""%"" AS [Late%]," & _
"Round([Not Manifested]/[Sum],2)*100 & ""%"" AS [Not Manifested%] FROM Q_ManifestedByStatus WHERE [PO No] NOT Like '8*' GROUP BY Int([Collection date]);"

Set objAdoCon = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

If Application.Version = 15# Then provider = "Provider = Microsoft.ACE.oledb.12.0;" Else provider = "Provider = Microsoft.ACE.oledb.4.0;"
objAdoCon.Open provider & "Data Source = C:\Users\pplusa\Documents\SupplierCompliace.accdb"

'Get 81 POs data
'Daily
Worksheets("81 POs Daily").Select
Cells.Delete

rst.Open sql, objAdoCon
Range("A2").CopyFromRecordset rst

For i = 0 To rst.Fields.Count - 1
    Range(Cells(1, i + 1).Address).Value = rst.Fields.Item(i).Name
Next i

Cells.EntireColumn.AutoFit
rst.Close

End Sub

VBA - By Query Name - DOES NOT WORK

Sub tt()

Dim objAdoCon       As Object
Dim rst             As Object
Dim provider        As String

Set objAdoCon = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

If Application.Version = 15# Then provider = "Provider = Microsoft.ACE.oledb.12.0;" Else provider = "Provider = Microsoft.ACE.oledb.4.0;"
objAdoCon.Open provider & "Data Source = C:\Users\pplusa\Documents\SupplierCompliace.accdb"

'Get 81 POs data
'Daily
Worksheets("81 POs Daily").Select
Cells.Delete

rst.Open "Q_ManifestedByDate81", objAdoCon
Range("A2").CopyFromRecordset rst

For i = 0 To rst.Fields.Count - 1
    Range(Cells(1, i + 1).Address).Value = rst.Fields.Item(i).Name
Next i

Cells.EntireColumn.AutoFit
rst.Close

End Sub

Answer 1

This is a known issue between running LIKE inside MS Access and outside via OLEDB/ODBC. Recall MSAccess is both a GUI program and a backend database.

MS Access via its GUI .exe interface by default uses ANSI-89 syntax with * as wildcard operator. MS Access via OLEDB as you are doing with Excel uses ANSI-92 syntax with % as the wildcard operator. See MS Office Support docs regarding Access wildcard character reference.

Consider one of two options to make both consistent:

1. Use the ALIKE operator over LIKE in the Access query with % operator.
2. Alternatively, set your .accdb database to use SQL Server Compatible Syntax (ANSI 92) under File \ Options \ Object Designer. And then LIKE will adhere to % and not *.

Answer 2

SQL doesn't use * for multi-character matching. It uses %.