Help with VBA SQL Query and storing Results into Variables

[tomlancaster]

I am trying to store the result from the below sql query - At the moment it writes to an Excel Sheet but I would prefer the information to be stored into variables "strFirstName" and "strSurname"

[FONT=Arial]Sub SQL_Result()[/FONT]
 
[FONT=Arial]Dim qt As QueryTable[/FONT]
[FONT=Arial]Dim strFirstName As String[/FONT]
[FONT=Arial]Dim strSurname As String[/FONT]
 
[FONT=Arial]connstring = "ODBC;DRIVER=SQL Server;SERVER=it-staffrecords.hq.internal;Trusted_Connection=Yes;DATABASE=CMDB"[/FONT]
 
[FONT=Arial]SQLSTRING = "SELECT StaffTable.staffnumber, StaffTable.PreferredFirstName, StaffTable.surname" & Chr(13) & "" & Chr(10) & "FROM CMDB.dbo.StaffTable StaffTable" & Chr(13) & "" & Chr(10) & "WHERE (StaffTable.staffnumber='999')"[/FONT]
 
[FONT=Arial]With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=SQLSTRING)[/FONT]
[FONT=Arial].Refresh[/FONT]
[FONT=Arial]End With[/FONT]
 
[FONT=Arial]End Sub[/FONT]

I have been searching through google for answers and I am getting the impression I need to do an open recordset to return the info I need but my attempts using example code have so far been unsuccessful.


Can anyone help me?

 

[DonkeyOte]

You can make your routines a little more flexible but in terms of the specifics of your query perhaps:

Public Sub ADO_Recordset()
    Dim c As ADODB.Connection
    Dim r As ADODB.Recordset
    Dim strSQL As String, strNO As String, strFN As String, strSN As String
    Set c = New ADODB.Connection
    c.Open "ODBC;DRIVER=SQL Server;SERVER=it-staffrecords.hq.internal;Trusted_Connection=Yes;DATABASE=CMDB"
    strSQL = "SELECT "
        strSQL = strSQL & "staffnumber, "
        strSQL = strSQL & "PreferredFirstName, "
        strSQL = strSQL & "surname "
    strSQL = strSQL & "FROM "
        strSQL = strSQL & "CMDB.dbo.StaffTable "
    strSQL = strSQL & "WHERE "
        strSQL = strSQL & "staffnumber='999'"
    Set r = c.EXECUTE(strSQL)
    If r.EOF = False Then
        strNO = r.Fields(0)
        strFN = r.Fields(1)
        strSN = r.Fields(2)
    End If
    r.Close
    c.Close
    Set r = Nothing
    Set c = Nothing
ExitPoint:
    Exit Sub
    
Handler:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ADO_Recordset of Module modADO"
    Resume ExitPoint
    
End Sub

You will need to reference the Microsoft Active X Data Objects Library using either early / late bound methods.

 

[tomlancaster]

Thank you so much - that worked a treat and is easy to understand.

Thanks once again.