Help with VBA SQL Query and storing Results into Variables

tomlancaster

New Member
Joined
Apr 7, 2009
Messages
26
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"

Code:
[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?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You can make your routines a little more flexible but in terms of the specifics of your query perhaps:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,149
Messages
5,600,012
Members
414,355
Latest member
Aruna Kallu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top