Access Query from Excel VBA

[omsoft]

I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND ((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID = tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.

 

[Harald Staff]

If I read this right; wildcard in SQL is % , not *

Best wishes Harald

 

[omsoft]

I do not believe that is true.

If I read this right; wildcard in SQL is % , not *

Best wishes Harald

 

[Sam Wilson]

It is true... why are you looking for LIKE '*' anyway? That will return
everything?

 

[omsoft]

I am sorry, my mistake. you are both right.

% does work. Somehow (I do not know how) I have used * and ? for wild cards
and have always worked.
Sorry about that.

BTW, LIKE * is to indeed return everything.
But in this case * is the value fo a string variable, which could be either
set to a specific value or * if the user wanted to report on all divisions.

The query works correctly in Access directly with * so I assumed that it
should work in VBA calling from Excel.

 

[Sam Wilson]

* and ? work in Access but not in SQL proper. I've never worked out why
Access is different but there we go.