Pass value from SQL into a variable

[TheWennerWoman]

Hello,

I have searched this site (and Google) for an answer and those I have found either don't quite fit with what I need or are using an entirely different approach to connecting to the SQL database - I am not confident enough to start tearing my routine apart to amend and plus I know what I have works!

My query will only ever return one value - I want to be able to store this value in a variable and then compare it to a constant in my code. Here is my code:

Sub ConnectSqlServer()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
Const version As String = "v20.4.0"
Const filename As String = "Test"

On Error GoTo OrderlyExit
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=localhost;INITIAL CATALOG=CAPEX;"
'strConn = strConn & " INTEGRATED SECURITY=sspi;"
strConn = strConn & "User Id=rs_rw;Password=rs_rw;"
    

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open strConn
Set rs = conn.Execute("SELECT Version FROM dbo.VersionHistory WHERE Filename = '" & filename & "'")

'
'this bit is what I'd like to change, rather than paste into a cell I'd like the value assigned to a variable
'
If Not rs.EOF Then
    Sheets(1).Range("A1").CopyFromRecordset rs
    rs.Close
        Else
            MsgBox "Error: No records returned.", vbCritical
End If

GoTo CleanUp

OrderlyExit:
strMsg = "Problem with obtaining data" + vbCrLf
strMsg = strMsg + "Please contact your administrator" + vbCrLf
strMsg = strMsg + "Giving the following error message" + vbCrLf + vbCrLf
strMsg = strMsg + Err.Description
MsgBox strMsg, vbOKOnly, "Error Obtaining Data"

CleanUp:
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
    
End Sub

Many thanks for reading and for any assistance.

 

[TheWennerWoman]

Ok, I think I've found a workaround although I'd still be interested in a non-workaround solution!

It seems that if I do

Dim MyArray() As Variant
Dim r As String
MyArray = rs.GetRows(1)

I can then pass the value into my variable from the array using

r = MyArray(0, 0)