[RESOLVED] Memo Field/Ado/SQL/Access db/and a from clause error

Hi all.

Here is the problem. I have a form with an ado code control, and I am searching a access 2000 db in the memo field for a substring using a sql statement to return only the rows that contain that substring. Maybe its just 1 word or a couple words to look for.

Anyhow, here is the code I have tried so far:

Code:

---

SQLStr = "Select * From tblQUOTE Where (tblQUOTE.Quote Like '%" & sString & "%')"
MsgBox SQLStr
Dim db1 As Connection
Set db1 = New Connection
db1.CursorLocation = adUseClient
sPath = App.Path & "\kjvbible.mdb"
db1.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
Set adoPrimaryRS1 = New Recordset
Set adoPrimaryRS1 = db1.Execute(SQLStr, , adCmdTable)

---

I have also tried this:

Code:

---

SQLStr = "Select * From tblQUOTE Where (tblQUOTE.Quote Like '*" & sString & "*')"
MsgBox SQLStr
Dim db1 As Connection
Set db1 = New Connection
db1.CursorLocation = adUseClient
sPath = App.Path & "\kjvbible.mdb"
db1.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
Set adoPrimaryRS1 = New Recordset
Set adoPrimaryRS1 = db1.Execute(SQLStr, , adCmdTable)

---

I have tried ( and ) around the where clause and not having them. I just dont understand what the problem is here. It should be just a streight forward search, no?

The error I get is: Run-time error '-2147217900 (80040e14)'; Syntax error in FROM clause.

When using google to search, I got a lot of info about weather to use the * as wildcard for access 97 and % for access 2000 and above. But I tried both with same exact error message.

Can anyone shed some light on this subject?

intercepter

i am not sure if you can access a memo field directly. but take a look at your Execute method, the last parameter (option) should be adCmdText.

And with that change, it now works as expected. Thank you! I just didnt see that. Sheesh.

intercepter