Ado Error

Anybody got any idea why this causes VB to perform an illegal operation and crash?

VB Code:

rsRes.Open "SELECT DISTINCT (ASampleName, BSampleName )" & _
            "FROM tblTests " & _
            "WHERE TestName = " & Chr(34) & _
            frmMain.cboTest.Text & Chr(34) & _
            " AND Archived = false", _
            cnnCon, _
            adOpenKeyset, _
            adLockReadOnly, _
            adCmdText

Quote:

---

SELECT DISTINCT (ASampleName, BSampleName )

---

I think you cannot use distinct that way.......did u try it directly in the database?

Quote:

---

I think you cannot use distinct that way.......did u try it directly in the database?

---

You're correct even Access hangs.

You need to pass that parameter in a different way. rs.open and parameters don't work too great.

First try something else tho: you forgot a space in the first line of your SQL, I believe.

Instead of passing the whole line like this put it in a stringvariable first:

VB Code:

strQry = "SELECT DISTINCT (ASampleName, BSampleName ) " & _ 'space missing in front of last parantheses
            "FROM tblTests " & _
            "WHERE TestName = " & Chr(34) & _
            frmMain.cboTest.Text & Chr(34) & _
            " AND Archived = false;)
....
 
rs.open strQry,...

Helger

Did you try without the brackets?

Code:

---


SELECT DISTINCT ASampleName, BSampleName

---

Oh, and how many records does the table hold?

J.