query data from SQL server using VBA, assign it to a variable

[spyla]

Hi,

I using excel 2010 in Windows 7. I would like to query a SQL database using VBA and retrieve data and assign the data to a variable. currently I have been able write the following code

Sub Macro2()
'
' Macro2 Macro
'
'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER=SQL Server;SERVER=PNWEBSERV01;UID=pnxlreader;PWD=LimsPass01;APP=Microsoft Office 2010;WSID=PNDTP056V64;DATABASE=PNLMDB" _
        , Destination:=Range("$C$6")).QueryTable
        .CommandText = Array( _
        "SELECT Samples.Date_Sampled" & Chr(13) & "" & Chr(10) & "FROM PNLMDB.dbo.Samples Samples" & Chr(13) & "" & Chr(10) & "WHERE (Samples.Sample_Num='034223-02')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_pnweb01"
        .Refresh BackgroundQuery:=False
    End With
 
End Sub

but the problem with this code is that it puts the data on the sheet with column name on C6 and date on C7 but instead of being put on a sheet, I want to assign the query result to variable in VBA so that I can manipulate it. Not sure How to change the destination to a variable

thanks

 

[Ruddles]

How about:-

    End With
 
[COLOR=red][B]    column_name = ActiveSheet.Range("C6")[/B][/COLOR]
[COLOR=red][B]    the_date = ActiveSheet.Range("C7")[/B][/COLOR]
 
End Sub

 

[spyla]

How about:-

    End With
 
[COLOR=red][B]   column_name = ActiveSheet.Range("C6")[/B][/COLOR]
[COLOR=red][B]   the_date = ActiveSheet.Range("C7")[/B][/COLOR]
 
End Sub

this is going to be part of big excel workbook and lot of VBA code. Would like to avoid putting data on to a sheet then delete after assigning it. Would also like to assign data to an array if I pull more than a single value

 

[Ruddles]

I suggested that because it would have been a trivial change to your existing code. Copying data from the worksheet to an array would not have been very much more complicated.

My only other suggestion would be to establish an ADO connection to the database and pull the data directly into a variable.

 

[spyla]

I suggested that because it would have been a trivial change to your existing code. Copying data from the worksheet to an array would not have been very much more complicated.

My only other suggestion would be to establish an ADO connection to the database and pull the data directly into a variable.

never used ADO, can you guide me some examples or books or any other resources

 

[Ruddles]

I'm not the best person to ask - I've only ever used it to get at Access databases. At its simplest, for a single record:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub SearchSingle()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim dbData As DAO.Database
  Dim rsData As DAO.Recordset
  
  Set dbData = DAO.OpenDatabase("database.mdb")
  Set rsData = dbData.OpenRecordset("SELECT Forename, Surname FROM tblMembers WHERE EmpNum=123456;")[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]  MsgBox "Employee 123456 is " & rsData!Forename & " " & rsData!Surname, vbOKOnly + vbInformation
  
  rsData.Close
  
End Sub[/SIZE][/FONT]

For multiple records:-

[FONT=Courier New][SIZE=1]Private Sub SearchMulti()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]  
  Dim dbData As DAO.Database
  Dim rsData As DAO.Recordset
  
  Set dbData = DAO.OpenDatabase("database.mdb")
  Set rsData = dbData.OpenRecordset("SELECT * FROM tblMembers WHERE Surname LIKE 'smi*';")[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]  Do Until rsData.EOF
    MsgBox rsData!EmpNum & ": " & rsData!Surname & ", " & rsData!Forename, vbOKOnly + vbInformation
    rsData.MoveNext
  Loop
  
  rsData.Close
  
End Sub[/SIZE][/FONT]

As I said, this is for connecting to an Access database. How you make the connection to your own database I'm afraid I don't know.

Time to hit Google, perhaps?