ACCESS 2003 SQL LIKE Operator not working in VB6 runtime

[Lennie]

Hi All,

I am using VB6 script via ADO objects to read ACCESS 2003 backend database using SQL String LIKE Operator. It doesn't seem to be working. I tested the SQL string under ACCESS 2003 Query mode and it's OK but not in VB6 runtime. What the Sales Manager wanted is instead of keying in the complete 8 digits SalesOrder number he wants to key in only the first 3 digits and the SQL string will extract all transactions that matches the first 3 digits and display it in LISTVIEW CONTROL where he can select the specific line item. Please help me.

Here is the SQL string :
SalesOrder datatype is numeric


"Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"


Thank you,
Have a good day.

[Ron Weller]

I only have Access 2002; but it works fine there. There is nothing wrong with the SQL string. Try checking your Connection and Recordset Objects. Make sure they are ok. Also you can do a Debug.Print of your SQL String, then copy and paste it directly into a new query in SQL View. Run it, and if there are no errors and it does what you want then your SQL string is fine.
Once you piece together your SQL string and send it to Access, it is Access that is executing it not VB. So if the Like operator is not working it is not VB, it must be your SQL syntax that Access does not like.

[Lennie]

Hi Ron,
The SQL string that I quoted ran well under ACCESS 2003 Query View but not under VB6 runtime.

Here is the VB6 script I used to run it and the SQL did not return any data.

Dim adoRst as new AdoDb.RecordSet
Dim adoConn as new AdoDb.connection
Dim strSql as string
dim strConn as string

strConn = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"Data Source = G:\SalesDept\SalesData.mdb;" & _
"Persist Security Info = False"

With AdoConn
.connectionstring = connstr
.open
End with

"Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"

adoRst.Open strSsql, adoConn, adOpenKeyset, adLockOptimistic
With adoRst
if not .EOF and not .BOF then
'display data in LISTVIEW control.
endif
End with

adorst.close
adoconn.close
set adorst = nothing
set adoconn = nothing


Hope the above script will help to explain where the problem is. This is the standard script that I used in all my VB6 programs and they all worked fine except SQL LIKE Operator.


Thanks.

[Ron Weller]

"Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"

adoRst.Open strSsql, adoConn, adOpenKeyset, adLockOptimistic

Two things I noticed here:
First is you never set the string variable to the sql statement string:
strSsql = "Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "*'"

Second is that ADO Recordset open options maybe defaulting to Table which only works with table names and saved query names. Try adding the adCmdText option to your open recordset options parameter:

adoRst.Open strSsql, adoConn, adOpenKeyset, adLockOptimistic, adCmdText

[Lennie]

Hi there,

There was type error regarding my VB6 script when I typed in here instead of COPY and PASTE from my VB6 program.

Nevertheless, I have sorted out the problem. I have use % instead of * in the LIKE operator.

Here is the SQL string that works:-

Eg.
Select SalesOrder from TblSalesTrans where (SalesOrder) like '" & txtSalesOrder & "%'"


Thanks RON for your time and help.