Hi All -

I want to execute a SQL (select statement) at the click of a command button and store the result in a variable. How can I do that ?

DoCmd.RunSql doesn't work because it is only for action queries and I am trying to execute a select query. I cannot find a way to do it.

Thanks for helping.

You want to store the result of the select query in a variable? How many columns and rows in the select query results?

Assuming it is one column, one row, and I will assume it is an integer and the select query looks like:

"SELECT Count(ID) as IDCount FROM tblMyTable"

Dim sSQL As String
Dim rs As ADODB.Recordset
Dim iValue As Integer

sSQL = "SELECT Count(ID) as IDCount FROM tblMyTable"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

iValue = rs("IDCount")

rs.Close

etc...

Did that help?



mmcdonal

Look it up at: http://wrox.books24x7.com

Try this - it uses an ADO connection and recordset object:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim CustId As Integer
Dim CustName As String
Dim SQL As String

' Open the ADO connection
If cnn.State = adStateOpen Then
   cnn.Close
End If
cnn.Open CurrentProject.Connection

CustID = 5

' Define the SQL using a variable
SQL = ""
SQL = SQL & "SELECT "
SQL = SQL & " CUST_NAME "
SQL = SQL & "FROM CUSTOMERS "
SQL = SQL & "WHERE CUST_ID = " & CustId

' Open the ADO recordset
If rst.State = adStateOpen Then
    rst.Close
End If
rst.Open SQL, cnn, adOpenKeyset, adLockOptimistic

' If no rows returned, notify the user and exit
If rst.BOF And rst.EOF Then
   MsgBox "Cust Name not found for Cust ID " & CustId & "!"
   rst.Close
   cnn.Close
   Exit Sub
End If

' If cust name exists, load it into the cust name variable
If Not IsNull(rst("CUST_NAME")) Then
   CustName = rst("CUST_NAME")
Else
   CustName = ""
End If

' Close the recordset and connection objects
rst.Close
cnn.Close


I tried to be a little thorough - it includes checks for being sure to close the
connection and/or recordset objects first if either are already open for some
reason. Though it's not actually necessary in this example, it may be something
to think about if you insert a chunk of code like this elsewhere.

The other thing is that I checked to be sure that if there IS a null value in
the customer name in this example, is to not try to load a null into a string
variable.

Hope that helps.

Warren

That was very helpful and narrative.

Thank you wscheiman and mmcdonal.

I never understood why this:

' Define the SQL using a variable
SQL = ""
SQL = SQL & "SELECT "
SQL = SQL & " CUST_NAME "
SQL = SQL & "FROM CUSTOMERS "
SQL = SQL & "WHERE CUST_ID = " & CustId

Is not written as:

' Define the SQL using a variable
SQL = "SELECT CUST_NAME FROM CUSTOMERS WHERE CUST_ID = " & CustId

Is there a rationale? I have seen it a few times on this forum.


mmcdonal

Look it up at: http://wrox.books24x7.com