Using the value returned by a SQL Query in VBA

[sbfq]

Im new here on the forums, so please excuse me if a similar question has already been asked...

I am writing some VBA code and want to use the value returned by a SQL script in the VBA code. My idea is to run the sql script (which returns just 1 value) and then put that value into a variable. Then I can use the variable in the rest of my code.

Can someone help me please? Is this possible??

 

[Bat17]

This may give you the idea.

Dim rst As DAO.Recordset
Dim strSQL As String
Dim varResult

strSQL = "SELECT MyData FROM MyTable"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not (rst.EOF And rst.BOF) Then
    varResult = rst!MyData
End If
Set rst = Nothing

Peter

 

[DB7]

As Bat said, or,

varResult = DLookUp("MyData","MyTable")