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"
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?
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?