Simple SQL not working !

[cbsm]

Hi !
I am probably missing something very obvious here - but for now, I am just becoming crazy !
I am working on a VB6 application - talking to an Access2000 database.
I am used to this kind of application.
I worked with this specific one for about 1 year - wiht no trouble in this kind.
I've created a query in access (did this to simplify my code). This query (QUERY1) is made of 9 tables.
I am selecting a few field of those tables.
When I run this query in access - I get 1 record as a result - which is OK.
Now - when I run the query from VB - I get 0 record !
(the query is "select * from QUERY1" - no WHERE clause or other)
I checked - I typed in the correct queryname, I am talking the correct DB ...
I added a MSFlexgrid linked to a Data Object - the record is there !
This is how I run the query :
dim rs as adodb.recordset
set rs=new adodb.recordset
set rs=RunQuery("select * from query1")

function Runquery (strSQL as string) as adodb.recordset
Dim RS As ADODB.Recordset
Dim cmd As ADODB.Command
On Error GoTo ErrorHandler
Set RS = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn1 'cnn1 is set at start of application
cmd.CommandText = strSQl
Set RS = New ADODB.Recordset
RS.CursorType = adOpenStatic
RS.LockType = adLockReadOnly
RS.Open cmd
Set RunSQLReturnRORS1 = RS
Set cmd = Nothing
Set RS = Nothing
Exit Function

I run queries this way since the beginning and NEVER had a problem ...
I am sure it is some stupid thing ...

 

[bjd4jc]

My guess is that the query that exists in Access contains a LIKE clause using an "*". To execute the query from VB you will need to have a version that uses an "%" in the like clause.

 

[cbsm]

At last ! I got it right !!!
Should have tried this long ago ...
In the query in Access i am using a LIKE "ab*".
Problem is, for VB, the wildchar is a % !!!!
What a waist of time ! Can't they all just use the same ?!

 

[bjd4jc]

>>for VB, the wildchar is a %

that is actually a specification of ADO.

 

[Scoty]

% is the standard. Uncle Bill (Gates) likes to do his own thing

 

[chiph]

% is the ANSI SQL standard wildcard character. MS-Access is the one that is non-standard.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first