LIKE Operator with ODBC & Access is causing pain.

[Adam Phillips]

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.

[Erwin]

Hi
If You are using ADO then see example
change the asterisk into %

SELECT ... FROM ... WHERE Languages LIKE "%COBOL%" ORDER BY ...;

Erwin

"Adam Phillips" <adamp@absa.co.za> schreef in bericht
news:39bf9c4a$1@news.devx.com...
>
> 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.

[Adam Phillips]

Thanks - It works!
Adam.

"Erwin" <erwin.christiaens@pandora.be> wrote:
>Hi
>If You are using ADO then see example
>change the asterisk into %
>
>SELECT ... FROM ... WHERE Languages LIKE "%COBOL%" ORDER BY ...;
>
>Erwin
>