simple VB question

P

Peter Morris

How do I run a SQL query in a VB program?

The query has the form

SELECT col1 FROM my_table WHERE key_no = 1


It should return a single string, and I want to put the returned
value in a string in my code.
 
K

Ken Snell [MVP]

Dim strSQL As String, strValue As String
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb
strSQL = "SELECT col1 FROM my_table WHERE key_no = 1"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then _
strValue = rst.Fields(0).Value
rst.Close
Set rst = Nothing
dbs.Clsoe
Set dbs = Nothing
 
P

Peter Morris

Peter Morris said:
How do I run a SQL query in a VB program?
Click to expand...

That's in a MS Access form, BTW. I want to run
this query when someone selects from a listbox.
 
U

UpRider

If the value is unique, how about using Myvar =
Dlookup("col1","my_table","Key_no=" & 1)

UpRider
 
Top