Returning values with SQL

[Rik]

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?

[Serge]

Try this:

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]

[JHausmann]

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

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

[Rik]

Thanks a lot!