Returning values with SQL

Hi there. Does anyone know how to execute an SQL select statement on a MDB database, and return a value to a VB variable?

For example, If I was to execute 'select UserName from users where UserId = 123' on the database, how could I get the result into a VB variable?

Try this:

Code:

---

Dim db As Database
Dim rs As Recordset
Dim strUserName As String

Set db = Workspaces(0).OpenDatbase("C:\MyDB.mdb")
Set rs = db.OpenRecordset("Select UserName From Users Where UserID = 123", dbOpenSnapshot)

If rs.EOF And rs.BOF Then
  MsgBox "User doesn't exist"
Else
  strUser = rs(0)
End If

---

strUser now holds the name of the user.

Regards,

------------------

Serge

Software Developer
[email protected]
[email protected]

Hmm, how interesting. That indicates that the default for recordsets is "fields".

I always use:

strUser = rs.fields(0)

If you don't care about the returns from the recordset select, you can do the following conditional in place of Serge's

Code:

---

if isnull(rs.fields(0)) then
    msgbox "user doesn't exist"
else
    strUser = rs.fields(0)
end if

---

Thanks a lot!