How to Save the Result of a SQL Query into a Variable in Vba

How to save the result of a SQL query into a variable in VBA?

Simply have your Function return the value from the Recordset:

Public Function rubrieknaamSQL(Child As Integer)
   Dim rst As DAO.Recordset
   Dim strSQL As String
   strSQL = "SELECT rubrieknaam FROM dbo_tbl_rubriek where rubrieknummer = " & Child & ""
   Set rst = CurrentDb.OpenRecordset(strSQL)
   ' new code:
   rubrieknaamSQL = rst!rubrieknaam
   rst.Close
   Set rst = Nothing
End Function

How to pass a SQL query result to variable with VBA Access

You can use the DLookup function native to access for this. Since you have an INNER JOIN in your SQL, you will have to use it twice, but it'll still work.

Use a temporary string variable to store the temporary output, like this:

Dim tmp as String
tmp = Cstr(DLookup("tbl_MoPo_BM.MandateType_ID","tbl_MoPo_BM","tbl_MoPo_BM.MoPo_BM_ID = " & iMandate)) 'This is to fetch the ID you performed the inner join on
HasBM = DLookup("tbl_Mandate_Type.BM_Included","tbl_Mandate_Type","Mandate_Type_ID =" & tmp) ' this is to get your value

Using your DoCmd.RunSQL will not suffice since its for action queries only (INSERT, DELETE, UPDATE) and incapable of returning the result.

Alternatively, you can use a Recordset with your SQL query to fetch what you want, but thats more work and the DLookup is designed to exactly avoid doing that for single column return selects.

Store results of a sql query into variables access vba

oh god I found it, it's so simple. There is a space required before where that's why it run in access but when I copied it into VBA and restructured the sql statement.

it should be

& " WHERE (((Salesdata.Loc) = 'Alipore'))" _

instead of

& "WHERE (((Salesdata.Loc) = 'Alipore'))" _

Store sql output in a vba variable

Some suggestions:

• The PortInfo variable can by of type String.
• The strServer variable is obsolete when using the server name in the connection string.
• The database can also be part of the connection string.
• When using vbTab as column separator, use vbCrLf as line separator.
• To count the rows, just increment the LastRow variable for each record.
• Do not use New when declaring a variable, because these can't be cleared.
• Clear the object variables in the end.
• Use Option Explicit in each Module to force yourself to declare all variables (like i).

For example, the code could look like this:

Dim PortInfo As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ConnectionString As String
Dim lStr As String
Dim LastRow As Long
Dim i As Integer

ConnectionString = "Provider=SQLOLEDB;Data Source=DB-01;" & _
    "Initial Catalog=a;Integrated Security=SSPI;"
lStr = "SELECT [PORTINFOID],[PORTNAME] FROM [dbo].[portinfo]"

Set cnn = New ADODB.Connection
cnn.Open ConnectionString

Set rst = New ADODB.Recordset
rst.Open lStr, cnn

With rst
    If Not .EOF And Not .BOF Then
        For i = 0 To .Fields.Count - 1
            PortInfo = PortInfo & .Fields(i).Name
            If i < .Fields.Count - 1 Then
                PortInfo = PortInfo & vbTab
            Else
                PortInfo = PortInfo & vbCrLf
            End If
        Next
    End If

    Do Until .EOF
        LastRow = LastRow + 1
        For i = 0 To .Fields.Count - 1
            PortInfo = PortInfo & .Fields(i)
            If i < .Fields.Count - 1 Then
                PortInfo = PortInfo & vbTab
            Else
                PortInfo = PortInfo & vbCrLf
            End If
        Next
        rst.MoveNext
    Loop

End With

rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

MsgBox PortInfo
MsgBox "This have been " & LastRow & " records.", vbInformation, "I'm done."

How to store the Query result into an Integer In Excel VBA

You can read like so:

rst.Open ....

dim value as long

if not rst.eof then 
     value = rst.collect(0)
else
    ''no rows
end if

Put VBA variable in SQL query in excel

Nothing strange about it, you've embedded a variable into your SQL string that your SQL engine knows nothing about.

Replace Format((month1), "mm/dd/yyyy") in your original string with month1formatted from your new string and the magic will happen.

sqlQuery = "SELECT monthdata.VAL, monthdata.MONTHVAL, monthdata.GREEN, " & _
           "       monthdata.RED, monthdata.RAG, monthdata.CREATOR " & _
           "  FROM data " & _
           "       LEFT JOIN monthdata ON data.UID = monthdata.DATAUID " & _
           " WHERE [UID] = '" & IDcell & "' " & _
           "  AND [MONTHVAL] = #" & month1formatted & "#"

Also, no need for the enclosing parenthesis () around your string literal, and I formatted it with line breaks to ease readability.

Oh, and do as Comintern instructed and parameterize the query! This accepted answer is a good example of how to do that.

---

Related Topics