ADO - EXCEL problem

Hi all,

I am creating very simple database in excel and I would like to implement simple searching mechanism using ADO.

To achieve this aim I wrote such a piece of code:

VB Code:

Dim search_result As String
 
search_result = "'%" & frmsearch.TextBox1.Text & "%'"
 
Dim rec As ADODB.Recordset
Dim conn As ADODB.Connection
 
Set conn = New ADODB.Connection
 
conn.Provider = "Microsoft ole db provider for odbc drivers"
conn.ConnectionString = "driver={microsoft excel driver (*.xls)};dbq=" & _
ThisWorkbook.Path & "\film_catalog.xls;"
 
conn.Open
 
Set rec = New ADODB.Recordset
rec.CursorLocation = adUseClient
rec.Open "select * from [catalog$] where category like " & (search_result), conn, adOpenDynamic, adLockOptimistic

but I have such a problem: excel doesn't ignore size of chars.
for exmaple: when I want to search a film category horror so I type horror but in database I have Horror so that I get empty recordset.

I was trying to use Option Compare Text but it doesn't work,

How can I make excel ignoring char's size??? :confused:

please help

thx in advance for your help

regards,
sweet_dreams

I couldn't duplicate this behavior. What versions are you using?

Anyways, one option might be to convert everything to uppercase:

VB Code:

search_result = "'%" & UCase(frmsearch.TextBox1.Text) & "%'"
 
rec.Open "select * from [catalog$] where UCase(category) like " & (search_result), conn, adOpenDynamic, adLockOptimistic

HTH

sweet dreams:

Perhaps this Web site would help you?

http://www.excel-vba.com/e-database-functions.htm

Hi guys,

Thanks a lot for your reply but unfortunatelly your advices didn't help.
Can anybody help me with this problem???

regards,

sweet_dreams

Post #2's code should work for you. Did you get any errors and what were the results of the search?

RobDog888,

I am sorry post #2's code works perfect.

But now I am trying to do it another way. Instead of

Quote:

---

UCase(category)

---

I would like to use variable. I mean:

Quote:

---

rec.Open "select * from [catalog$] where " & UCase(variable_category) & " like " & (search_result), conn, adOpenDynamic, adLockOptimistic

---

and in this case this code doesn't work. excel doesn't ignore char's size.

what should I do now???

please help

regards,
sweet_dreams

Instead of doing it in-line try setting the variable to ucase before hand.

VB Code:

Dim variable_category as string
variable_category = "SomeThing"
variable_category = UCase$(variable_category)
rec.Open "select * from [catalog$] where " & variable_category & " like " & (search_result), conn, adOpenDynamic, adLockOptimistic

unfortunately it also doesn't work :(

Are youtring to search numeric and textual data types?