VBA for picking only one value from an SQL base

[Damian86]

Hi all,

I've already posted this on two other forums, however, to no avail so far (unfortunately I cannot post links yet):

What I want to do is to fetch a single value from an SQL base (not a table, not a row and not a record). I want to store it as a value in the A1 cell, as a variable or show it in a message box. To do this, I need to fetch it properly. I've been adviced to use the following code:

Sub FetchRecord()

Set cnn = CreateObject("ADODB.Connection")

    cnn.Open "Driver={SQL Server};Server=SARA;UID=sa1;Password=password!;Database=Wyposazenie"
    nSQL = "SELECT S_operatorzy.Login FROM Wyposazenie.dbo.S_operatorzy S_operatorzy WHERE S_operatorzy.Login='mylogin'"
    
    Range("A1").Value = cnn.Execute(nSQL, Options:=2048)(0).Value
    cnn.Close

End Sub

However, I get the run-time error 3001. I don't really know if I do it well. Should I do someting with the "recordset"? I appreciate all help.

Regards.

[Damian86]

The answer is:

Sub FetchRecord() 
     
    Dim Conn As Object 
    Dim ConnStr As String 
    Dim RecSet As Object 
    Dim SqlStr As String 
     
    Set Conn = CreateObject("ADODB.connection") 
    ConnStr = "Driver={SQL Server};Server=SARA;UID=sa1;Password=password;Database=Wyposazenie" 
    Set RecSet = CreateObject("ADODB.Recordset") 
    SqlStr = "SELECT S_operatorzy.Login FROM Wyposazenie.dbo.S_operatorzy S_operatorzy WHERE S_operatorzy.Login='mylogin'" 
     
    Conn.Open ConnStr 
     
    RecSet.Open SqlStr, Conn 
    Range("A1").Value = RecSet(0) 
     
    Conn.Close 
    Set Conn = Nothing 
End Sub

I've changed the names of the variables. Regards.

[mdmackillop]

Thanks for posting the solution.