Pass value from SQL into a variable

TheWennerWoman

Board Regular
Joined
Aug 1, 2019
Messages
60
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:
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

TheWennerWoman

Board Regular
Joined
Aug 1, 2019
Messages
60
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
Code:
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
Code:
r = MyArray(0, 0)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,615
Messages
5,625,871
Members
416,141
Latest member
Bartek9q

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