Programmatically List All SQL Servers And Their Databases

[mazza]

I am actually trying 2 things:

Using vba Programmatically List All SQL Servers And Their Databases so I can easily relink my application to various clients SQL servers. Each organisation has different server names. rather then going through Enterprise manager to find out the servernames, I would like to search using the access application when I perform the original set up.

Also maybe a bit tricker create ODBC links programatically using vba

Anyone who can help me along the way with the code

 

[tehNellie]

Not entirely sure how you'd determine whether or not a server is running SQL server through VBA, but easiest way to list databases on SQL server is to exec sp_databases. As long as you can connect to the DB you should be able to exec this stored procedure by default.

 

[LPurvis]

List actual SQL Servers? You can use SQLDMO http://support.microsoft.com/kb/q287737/ (though it's more distanced from these days I'd say).
Or have a look at http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_21801003.html for an API example. (It's Experts Exchange - so just scroll down past the crap to see the real code on the page.. it's not true that you have to sign up if you're not already a member!)

Establishing ODBC linked tables via code is pretty trivial by comparison. ;-)
Once you have the necessary settings to create the connectionstring.

 

[tehNellie]

Well there you go, you learn something new everyday, Had found .net code to do it, but not for vb(a), I'll tuck that away somewhere safe.

 

[mazza]

Thanks got the Server List working... had to add the SQLDMO reference to get it to work.

However with creating an ODBC link programatically can someone give me a clue where to start, mind you I need to use it in VBA...



for those interested in the code to list available sql servers

Private Sub Command2_Click()

Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Set oSQLApp = New SQLDMO.Application


Set oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To oNames.Count
List1.AddItem oNames.Item(i)

Next i

End Sub

 

[LPurvis]

Hi. Yep.

Sub CreateLinkedODBCTable(strTableName As String, strServer As String, strDBName As String, _
                            Optional blnIntegratedSecurity As Boolean, _
                            Optional strUserName As String, Optional strPassword As String, _
                            Optional strAlias As String)
'Creates a SQL Server ODBC linked table based on provided connection properties
'Usage example: CreateLinkedODBCTable "tblTableName", "ServerName", "Northwind", True
On Error GoTo errHere
    Dim db As Database
    Dim tdf As TableDef
    Dim prp As DAO.Property
    Dim strConnect As String
    
    strConnect = "ODBC;Driver={SQL Server};Server="
    strConnect = strConnect & strServer & ";Database=" & strDBName
    
    If blnIntegratedSecurity Then
        strConnect = strConnect & ";Trusted_Connection=Yes"
    Else
        If Len(strUserName) > 0 Then
            strConnect = strConnect & ";UID=" & strUserName
        End If
        If Len(strPassword) > 0 Then
            strConnect = strConnect & ";PWD=" & strPassword
        End If
    End If
    
    Set db = CurrentDb
    Set tdf = db.CreateTableDef()
    With tdf
        .Name = IIf(Len(strAlias) > 0, strAlias, strTableName)
        .Connect = strConnect
        .SourceTableName = strTableName
    End With
    db.TableDefs.Append tdf
    
ExitHere:
    Set tdf = Nothing
    Set db = Nothing
    
    Exit Sub
    
errHere:
    MsgBox "Error " & Err & vbCrLf & Err.Description
    Resume ExitHere
End Sub

 

[DCrake]

Simple Software Solutions

Mazza
Little addition to your code. If the oNames.Count = 0 then you must only be running on a local copy and not on a server.

Public Function ListConnectedSQLServers()
    Dim i As Integer
    Dim oNames As SQLDMO.NameList
    Dim oSQLApp As SQLDMO.Application
    Set oSQLApp = New SQLDMO.Application
    
    Set oNames = oSQLApp.ListAvailableSQLServers()
    SysServerCount = oNames.Count
    If SysServerCount = 0 Then
        ArrayServers(0) = "LOCAL"
    Else
        For i = 0 To SysServerCount - 1
            ArrayServers(i) = oNames.Item(i)
        Next i
    End If
    
End Function

 

[mazza]

Thanks
before I start to test this code, just checking that it does what it wants to do:

Problem I have is that if I install for example my application on a virgen PC or server, I need to create and ODBC. Normally I go to control panel, administatrative tools, odbc databases and set up an ODBC link.

The I link my front end to the SQL tables programatically or manually by relinking tables to the sql through the odbc just created.

So i want to avoid setting up for example a new odbc link for each user

 

[LPurvis]

Pretty much yeah - as the above example code actually assumes that you won't be using a DSN. (i.e. it creates tables with a DSNless connection string).
DSNs aren't a bad thing at all - but depending upon them is no way to live. :-D

 

[Banana]

Google for DNS-less connection.

Doug J. Steele has a great example code.

Edit: Apparently Leigh already had gave you an example. I need caffeine.

 

[sixHat]

This is an old question... And I'm sure their is a way to still apply the SQLDMO Library examples given here. In my case this wasn't an option as the SQLDMO reference is no longer available in MS Access 2010 and up (Not that I could find anyway)

And outside of .Net SMO I couldn't find any other solutions to do this programtically via VBA. Then I remembered SQL SERVER!!

Listing databases and even available servers can be done very easily from SQL Server here is an example of how I applied this to a couple combo boxes on my MS Access Form:

Private Sub ListServers()
    Dim currentConnection As String
    Dim Conn As ADODB.Connection
    Dim Cmd As ADODB.Command
    
    currentConnection = "Your Connecton String Here""
    
    Debug.Print currentConnection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "EXEC sp_linkedservers", currentConnection
    Set Me.cmboServers.Recordset = rs
End Sub

Private Sub ListDatabases()
    Dim currentConnection As String
    Dim Conn As ADODB.Connection
    Dim Cmd As ADODB.Command
    
    currentConnection = "Your Connecton String Here"
    
    Debug.Print currentConnection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM sys.databases", currentConnection
    Set Me.cmboDatabases.Recordset = rs
End Sub

 

[sixHat]

This is an old question... And I'm sure their is a way to still apply the SQLDMO Library examples given here. In my case this wasn't an option as the SQLDMO reference is no longer available in MS Access 2010 and up (Not that I could find anyway)

And outside of .Net SMO I couldn't find any other solutions to do this programtically via VBA. Then I remembered SQL SERVER!!

Listing databases and even available servers can be done very easily from SQL Server here is an example of how I applied this to a couple combo boxes on my MS Access Form:

Private Sub ListServers()
    Dim currentConnection As String
    Dim Conn As ADODB.Connection
    Dim Cmd As ADODB.Command
    
    currentConnection = "Your Connecton String Here""
    
    Debug.Print currentConnection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "EXEC sp_linkedservers", currentConnection
    Set Me.cmboServers.Recordset = rs
End Sub

Private Sub ListDatabases()
    Dim currentConnection As String
    Dim Conn As ADODB.Connection
    Dim Cmd As ADODB.Command
    
    currentConnection = "Your Connecton String Here"
    
    Debug.Print currentConnection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM sys.databases", currentConnection
    Set Me.cmboDatabases.Recordset = rs
End Sub