LIKE Operator with ODBC & Access causing pain!

Hi,

I'm trying to query a table on an Access database using the LIKE operator
with wildcards within a VB app. The technique I usually use is to massage
a 'template' query with Access via 'cut-and-paste'. I am using ADO 2.1, ODBC,
WinNT 4(SP5), VB6 (SP3).

I get the following errors (using Debug.Print) in VB:
---SQL(1)---
SELECT ... FROM ... WHERE Languages LIKE "*COBOL*" ORDER BY ...;
-2147217904 [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 1.

NOTE: The above works perfectly from within Access itself, but not within
VB.

Changing " to ' seems to work until I try to access the recordset:
---SQL(2)---
SELECT ... FROM ... WHERE Languages LIKE '*COBOL*' ORDER BY ...;
3021 Either BOF or EOF is True, or the current record has been deleted;
the operation requested by the application requires a current record.

My VB code is as follows:

Dim rsTCCSource As ADODB.Recordset 'Source recordset
...
'Open a connection
Set cnTCC = New ADODB.Connection
cnTCC.Open "TCC"
...
strSQL = ... (the above SQL statements)
Debug.Print strSQL
'Get the Source recordset - Static & Read Only
Set rsTCCSource = New ADODB.Recordset
rsTCCSource.Open strSQL, cnTCC, adOpenStatic, adLockReadOnly, adCmdText
'ERROR here for SQL(1)
...
'Loop through the Source recordset
rsTCCSource.MoveFirst 'ERROR here for SQL(2) - no records

Any ideas, suggestions or clues on how to overcome the above problem will
be much appreciated. Thanks - AP.

hi,
replace this "*COBOL*" with this "%COBOL%".
Ado use % for wild cards. it is suppose to work. try it!!!!


"A. Phillips" <adamp@absa.co.za> wrote:
>
>Hi,
>
>I'm trying to query a table on an Access database using the LIKE operator
>with wildcards within a VB app. The technique I usually use is to massage
>a 'template' query with Access via 'cut-and-paste'. I am using ADO 2.1,
ODBC,
>WinNT 4(SP5), VB6 (SP3).
>
>I get the following errors (using Debug.Print) in VB:
>---SQL(1)---
>SELECT ... FROM ... WHERE Languages LIKE "*COBOL*" ORDER BY ...;
>-2147217904 [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
>Expected 1.
>
>NOTE: The above works perfectly from within Access itself, but not within
>VB.
>
>Changing " to ' seems to work until I try to access the recordset:
>---SQL(2)---
>SELECT ... FROM ... WHERE Languages LIKE '*COBOL*' ORDER BY ...;
> 3021 Either BOF or EOF is True, or the current record has been
deleted;
>the operation requested by the application requires a current record.
>
>My VB code is as follows:
>
> Dim rsTCCSource As ADODB.Recordset 'Source recordset
>...
> 'Open a connection
> Set cnTCC = New ADODB.Connection
> cnTCC.Open "TCC"
>...
> strSQL = ... (the above SQL statements)
>Debug.Print strSQL
> 'Get the Source recordset - Static & Read Only
> Set rsTCCSource = New ADODB.Recordset
> rsTCCSource.Open strSQL, cnTCC, adOpenStatic, adLockReadOnly, adCmdText
> 'ERROR here for SQL(1)
>...
> 'Loop through the Source recordset
> rsTCCSource.MoveFirst 'ERROR here for SQL(2) - no records
>
>Any ideas, suggestions or clues on how to overcome the above problem will
>be much appreciated. Thanks - AP.
>