SQL Excel

Hi

You should be able to use the SQLDMO
object to return all the sql servers that are visible from a clients machine. You will need to manually add a reference to the Reference to Microsoft SQLDMO object library (do this from TOOLS and then REFERENCES in the visual basic editor)

The you can try to play around with the following code which I found on the internet. I did not try this out but I guess it should be fine. When you get that working then, you can populate the combobox from the array that the function will return.
hope that works out fine for you..
Please let us know how it goes. Thanks, Al


Public Function AvailableSQLServers() As String()
'***********************************************
'PURPOSE: Returns array list name of all SQL Servers
' on the network that are visible to the
' machine
'
'RETURNS: String array containing names of all
' available SQL Servers (or an array with one
' element containing empty string if no
' SQL Servers are available/visible)

'REQUIRES: Reference to Microsoft SQLDMO object library
' VB6 Because array is returned
' Assumes Option Base is not set to 1
' If you don't have VB6, and/or Option Base 1
' is set, it should not be very hard to modify
' this code for you own purposes

'EXAMPLE:
'Dim sServers() As String
'Dim iCtr As Integer

'sServers = AvailableSQLServers
'If sServers(0) = "" Then
' MsgBox "No SQL Servers Available"
'Else
' For iCtr = 0 To UBound(sServers)
' Debug.Print sServers(iCtr)
' Next
'End If
'***********************************************
Dim oServer As New SQLDMO.Application
Dim oNameList As SQLDMO.NameList
Dim iElement As Integer
Dim sAns() As String
Dim lCtr As Long, lCount As Long

On Error GoTo ErrorHandler

ReDim sAns(0) As String
Set oNameList = oServer.ListAvailableSQLServers

With oNameList
lCount = .Count
If lCount > 0 Then
For lCtr = 1 To .Count
iElement = IIf(sAns(0) = "", 0, UBound(sAns) + 1)
ReDim Preserve sAns(iElement) As String
sAns(iElement) = oNameList.Item(lCtr)
Next
End If
End With

AvailableSQLServers = sAns
Exit Function

ErrorHandler:
'Return array with one empty element on error
ReDim sAns(0) As String
AvailableSQLServers = sAns
End Function

Hello Again,

I added referance of SQLDMO, but I couldn’t satisfy for listing the SQL Servers by using the Combobox. I will continue to my operations with TEXTBOXes.
I have done the ADO Connection that I wanted to do by the simple way with the Code Block which is shown at the following lines.

Codes

Private Sub CommandButton4_Click()
'TextBox3.Text = "Baglanti Sorgulaniyor ..."
Dim Baglanti As New ADODB.Connection
'Dim KayitSeti As New ADODB.Recordset
Dim Firma As String, Server As String, Database As String, Kullanici; As String, Parola As String
Firma = Format(Sheets("SETUP".Range("B5", "000"
Server = Sheets("SETUP".Range("B1".Value
Database = Sheets("SETUP".Range("B4".Value
Kullanici; = Sheets("SETUP".Range("B2".Value
Parola = Sheets("SETUP".Range("B3".Value
'Sorgu = "SELECT CODE,DEFINITION_ FROM LG_" & Format(Sheets("SETUP".Range("B5", "000" & "_CLCARD " & "ORDER BY DEFINITION_" & ""
Baglanti.Open "Provider=SQLOLEDB; Data Source=" & Server & "; Initial Catalog=" & Database & "; User ID=" & Kullanici; & "; Password=" & Parola & ";"
'KayitSeti.Open Sorgu, Baglanti
'Cells(8, 1).CopyFromRecordset KayitSeti
'KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
End Sub


thanks and have a nice day.

Hi again,

I found the solution of the problem. At the following there is solution . Best wishes

Private Sub UserForm_Initialize()
TextBox4.PasswordChar = "*"
Dim SqlServer() As String
Dim SrvSay As Integer
SqlServer = SrvList
If SqlServer(0) = "" Then
MsgBox "SQL Server Bulunamadi;"
Else
For SrvSay = 0 To UBound(SqlServer)
ComboBox1.AddItem SqlServer(SrvSay)
Next
End If

End Sub

Public Function SrvList() As String()
Dim Srv As New SQLDMO.Application
Dim SrvAd As SQLDMO.NameList
Dim i As Integer
Dim Liste() As String
Dim j As Long, Say As Long
On Error GoTo Hata
ReDim Liste(0) As String
Set SrvAd = Srv.ListAvailableSQLServers
With SrvAd
Say = .Count
If Say > 0 Then
For j = 1 To .Count
i = IIf(Liste(0) = "", 0, UBound(Liste) + 1)
ReDim Preserve Liste(i) As String
Liste(i) = SrvAd.Item(j)
Next
End If
End With
SrvList = Liste
Exit Function
Hata:
ReDim Liste(0) As String
SrvList = Liste
End Function

----------------------------------------------------- www.excelvba.net 'in adminlerinden olan Co Admin Sayin Erkan Akayay ve tüm excelvba ailesi ve www.sqlexcel.net e tesekkür ederim.
-----------------------------------------------------
I want to thanks to mr. Erkan Akayay who is one of the admin of www.excelvba.net and all www.excelvba.net family and thanks to www.sqlexcel.net
thanks and have a nice day.

Great you got it working

Thats a very useful bit of code and thanks very much for posting it up.

Bye for now and take care,
Al