ADO %, * Wild Card confusion

[PTP86]

Hi

I've found the workaround for this issue, but don't understand why it was an issue:


I did all the hard work of writing the queries in Access and some of the queries involve sub-queries
e.g. Pretend I wanted queries that prepared data on Goals, Assists, Wages, Corners
So inside Access, the queries that contain my final results of these are called Goals_Qry3, Assists_Qry2, Wages_Qry5, Corners_Qry1


On a spreadsheet I have the queries listed and use VBA to tell it to loop through the queries and import the results to the spreadsheet
On each loop it executes the following code:

(Note: QueryName is of course the argument telling it which query to deal with in this iteration of the loop)

Dim RS as ADODB.Recordset
Dim strSQL as String

Set RS = New ADODB.Recordset
strSQL = "SELECT ["
strSQL = strSQL & QueryName & "]"
strSQL = strSQL & ".* FROM ["
strSQL = strSQL & QueryName & "];"

RS.Open strSQL, Conn, adLockReadOnly, adCmdText

i.e the query that I have written the SQL for in Excel VBA is a straightforward select all



Pretend Corners_Qry1 was written to only look at Manchester City and Manchester Utd for some reason.
In Access the query was written with the criteria of Like "Manchester *"


When Excel VBA loops through the queries, it is returning no records for Corners_Qry1 but is returning records for all of the others.



On various forums I've seen the comment that % is the wildcard for ADO and not * and that's been the solution to peoples problem
My problem is solved if I go into Access and change Corners_Qry1 criteria to say "Manchester Utd" or "Manchester City" instead

The Question
Why was the use of Like "Manchester *" a problem when I haven't actually written that query in Excel VBA. The query I've written in Excel VBA is a straightforward select all

Plus

That "straightforward select all" does feature a * wildcard and it works for every query. So why do people say that "*" is a problem?



Thanks

 

[RoryA]

The * in your Select query is not a wildcard - it's the shorthand for all fields. That's a totally different thing.

 

[PTP86]

Thanks Rory That's answered the 2nd question


Still wondering if someone can explain why I was having this issue with Corners_Qry1 when the SQL for that wasn't written in Excel VBA. The only SQL written in Excel VBA and having to be passed by ADO was a select all query.

 

[RoryA]

The SQL is still being executed by the OLEDB provider, not by the Access application, so it needs ANSI SQL syntax.