Excel VBA with SQL : No row returned when Where clause specified

Question

Here below an excerpt of my codes.

This is the SQL command :

   vSQL = "SELECT OLIN.fk_cCUST as CustomerKey, Sum(nOLINselTota) AS ResultSumV "
   vSQL = vSQL & " FROM (OLIN Inner Join TYOR on OLIN.fk_cTYOR = TYOR.cTYOR) INNER Join MTYP On TYOR.fk_cMTYP = MTYP.cMTYP "
   vSQL = vSQL & " Where OLIN.fk_cOHEAkey Like 'T180*' "
   vSQL = vSQL & " GROUP BY OLIN.fk_cCUST "
   vSQL = vSQL & ";"

Where the database located :

   vDataSRC = "C:\_projCuTOPs\bdd\GSF_dataWHouse.accdb"

The variable containing the string connection to the DB :

   vArrSRC = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;"
   vArrSRC = vArrSRC & "Data Source= " & vDataSRC & ";"
   vArrSRC = vArrSRC & "Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";"
   vArrSRC = vArrSRC & "Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;"
   vArrSRC = vArrSRC & "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;"
   vArrSRC = vArrSRC & "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;"
   vArrSRC = vArrSRC & "Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"

The wrkSheet for the result :

  ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
  ActiveWorkbook.Sheets(Worksheets.Count).Name = "RawDatas"

This command Adding QueryTable as a ListObject returns ZERO occurrence because of Where Clause whereas the SQL is OK.

If the Where Clause is removed, it works but I need to add restrictions on Rows.

With ActiveSheet.ListObjects.Add(SourceType:=xlSrcExternal, Source:=vArrSRC, Destination:=Range("$A$1")).QueryTable
  '>>
  .CommandType = xlCmdSql
  .CommandText = vSQL
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
  '**
  .SourceDataFile = vDataSRC
  '**
  .ListObject.DisplayName = "tbl_SQL_SumTYOR"
  .Refresh BackgroundQuery:=False
  '>>
End With

What's wrong ? I absolutely need to use Where clause.

Are you sure about the * in the like-clause? Maybe you meant % — Robert Kock
– Robert Kock, Commented Dec 19, 2018 at 16:36
Possible duplicate of Why does a LIKE query in Access not return any records? — Robert Kock
– Robert Kock, Commented Dec 19, 2018 at 16:39
Yes. I even submit it in SQL Access query section and it's OK. But, I'll try now. — botakelymg
– botakelymg, Commented Dec 19, 2018 at 16:40

Parfait · Accepted Answer · 2018-12-19 16:44:44Z

2

The LIKE wildcard behaves differently when running queries between the MS Access GUI (frontend) and any ODBC/OLDEB connection to MS Access (backend). See differences between ANSI-89 and ANSI-92 in MSDN docs.

For ODBC/OLEDB connections as you are doing in Excel, LIKE requires the ANSI-92 wildcard with %:

vSQL = vSQL & " Where OLIN.fk_cOHEAkey Like 'T180%' "

Alternatively, to be compatiable in both use ALIKE (ANSI-Like) in GUI and ODBC/OLEDB:

vSQL = vSQL & " Where OLIN.fk_cOHEAkey ALike 'T180%' "

Even better, save the query in MS Access (which is more efficient as the engine caches stats and the best execution plan):

SELECT OLIN.fk_cCUST as CustomerKey, 
       SUM(nOLINselTota) AS ResultSumV
FROM (OLIN INNER JOIN TYOR ON OLIN.fk_cTYOR = TYOR.cTYOR) 
INNER Join MTYP On TYOR.fk_cMTYP = MTYP.cMTYP
WHERE OLIN.fk_cOHEAkey LIKE 'T180*'
GROUP BY OLIN.fk_cCUST

Then, reference its name in Excel (avoiding VBA concatenation):

vSQL = "SELECT * FROM mySavedQuery"

edited Dec 19, 2018 at 16:44

answered Dec 19, 2018 at 16:39

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

botakelymg Over a year ago

It's OK for the solution.

Parfait Over a year ago

Great to hear. Curious, did you try the saved query approach?

botakelymg Over a year ago

You're right @Parfait and I know that approach. However, I need to specify different criteria as date limits and other items from tables dynamically. So, saved queries seem inappropriate for me. Unless you have any tips.

Parfait Over a year ago

Consider parameterized queries then where you keep the underlying structure but change values only: SELECT * FROM mySavedQuery WHERE myDateCol BETWEEN ? AND ?

Collectives™ on Stack Overflow

Excel VBA with SQL : No row returned when Where clause specified

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related