PDA

View Full Version : [SOLVED] VBA for picking only one value from an SQL base



Damian86
09-25-2017, 10:35 PM
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
09-26-2017, 10:03 PM
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
09-27-2017, 04:02 AM
Thanks for posting the solution.