VBA for picking only one value from an SQL base

[Damian86]

Hi,

I want to pick only one value from an SQL base. I know how to bring a whole tabel to Excel. I use:

  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER=SQL Server;SERVER=192.168.0.10\SQL;UID=sa1;PWD=password;APP=System operacyjny Microsoft® Windows®;WSID=User1;DATABASE=Wyposaz" _
        ), Array("enie")), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT S_operatorzy.ID, S_operatorzy.Login, S_operatorzy.Phraser, S_operatorzy.Upr_sprzet_inf, S_operatorzy.Upr_sprzet_mod, S_operatorzy.Upr_odziez_inf, S_operatorzy.Upr_odziez_mod, S_operatorzy.Upr_p" _
        , _
        "ersonel_inf, S_operatorzy.Upr_personel_mod, S_operatorzy.Upr_alerty" & Chr(13) & "" & Chr(10) & "FROM Wyposazenie.dbo.S_operatorzy S_operatorzy" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabela_Kwerenda_z_sql22"
        .Refresh BackgroundQuery:=False
    End With
End Sub

As you can see, I can get all those columns into a sheet with headers. What I want to do is bring only one value without a header that I can assign to A1 cell or a variable.

I tried something like:

Set cnn = CreateObject("ADODB.Connection")

    cnn.Open "Driver={SQL Server};Server=SARA;UID=sa1;Password=password;Database=Wyposazenie"
    nSQL = "SELECT S_operatorzy (S_operatorzy.Login" & vbNewLine & "WHERE S_operatorzy.Login 'mylogin')"
    cnn.Execute nSQL
Range("A1").Value = cnn

However, it does not work. Can you please help me?

 

[anvg]

Hi
You have some mistakes in your code. Try with

Set cnn = CreateObject("ADODB.Connection")

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

Regards,[/FONT][/COLOR]

 

[Damian86]

Thank you very much. I will check this code on Monday and I will write you if it works.

What does this part of code mean?

Options:=2048)(0)

You have some mistakes in your code

Oh really? I did not expect that :P

Regards!

 

[Damian86]

Ok, so unfortunately I get the run-tume errro 3001. The VBE highlights the line:

Range("A1").Value = cnn.Execute(nSQL, Options:=2048)(0).Value

Any ideas?

 

[Damian86]

Ok, so doesn't the error occur when I try to insert

cnn = CreateObject("ADODB.Connection")

into

Range("A1").Value = cnn.Execute(nSQL, Options:=2048)(0).Value

?

I mean that a cell cannot hold an object as a value. Am I right? Maybe cnn.Execute.Value does not give a value?

 

[Damian86]

Anyone please?

 

[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.