List SQL Servers on Network in VBA

John Thompson 0 Reputation points
2023-02-08T03:11:12.9666667+00:00

Is there any possible way to list all servers on a network using VBA? I used to use SQLDMO but it's gone. I tried Powershell (System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()), but it won't list my local server on my laptop. Any suggestion would be appreciated. Thanks.

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,265 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
898 questions
{count} votes

2 answers

Sort by: Most helpful
  1. AlphonseG 201 Reputation points
    2023-02-09T01:32:43.4+00:00

    I found this many years ago, but still works. Credit included.

    Private Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal henv As Long, phdbc As Long) As Integer
    Private Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv As Long) As Integer
    Private Declare Function SQLBrowseConnect Lib "odbc32.dll" (ByVal hdbc As Long, ByVal szConnStrIn As String, ByVal cbConnStrIn As Integer, ByVal szConnStrOut As String, ByVal cbConnStrOutMax As Integer, pcbconnstrout As Integer) As Integer
    Private Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc As Long) As Integer
    Private Declare Function SQLError Lib "odbc32.dll" (ByVal henv As Long, ByVal hdbc As Long, ByVal hstmt As Long, ByVal szSqlState As String, pfNativeError As Long, ByVal szErrorMsg As String, ByVal cbErrorMsgMax As Integer, pcbErrorMsg As Integer) As Integer
    Private Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hdbc As Long) As Integer
    Private Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal henv As Long) As Integer
    
    '-----------------------------------------------------------------------------
    '   basServerList
    '   Takes an ODBC hack and uses it to get a list of
    '   valid servers on the NT domain
    '   Owner: MichKa (Michael Kaplan)
    '   You may freely use this code in your applications.
    '   Copyright © 1999 Trigeminal Software, Inc. All Rights Reserved.
    'http://www.trigeminal.com/code/ServerList.bas
    '-----------------------------------------------------------------------------
    Public Function StServerList() As String
    On Error Resume Next
        Dim rc As Integer
        Dim henv As Long
        Dim hdbc As Long
        Dim stCon As String
        Dim stConOut As String
        Dim pcbConOut As Integer
        Dim ichBegin As Integer
        Dim ichEnd As Integer
        Dim stOut As String
        
        Const COMMA As String = ","
        
        rc = SQLAllocEnv(henv)
        rc = SQLAllocConnect(ByVal henv, hdbc)
        'stCon = "DRIVER=SQL Server"
        stCon = "DRIVER=" & OdbcDriver   '1/28/2016   ODBC_DRIVER    '9/23/2010
        
        ' Get the size of the buffer to create and create it
        rc = SQLBrowseConnect(ByVal hdbc, stCon, Len(stCon), stConOut, Len(stConOut) + 2, pcbConOut)
        stConOut = String$(pcbConOut + 2, vbNullChar)
        
        ' Get the actual server list
        rc = SQLBrowseConnect(ByVal hdbc, stCon, Len(stCon), stConOut, Len(stConOut) + 2, pcbConOut)
        
        If (rc <> SQL_SUCCESS) And (rc <> SQL_NEED_DATA) Then
            ' ignore the errors if any occur
        Else
            ' Parse out the server list
            ichBegin = InStr(InStr(1, stConOut, "server="), stConOut, "{", vbBinaryCompare)
            stOut = Mid$(stConOut, ichBegin + 1)
            ichEnd = InStr(1, stOut, "}", vbBinaryCompare)
            StServerList = Left$(stOut, ichEnd - 1)
        End If
    
        ' Disconnect, free the connection handle, then
        ' free the environment handle.
        rc = SQLDisconnect(hdbc)
        rc = SQLFreeConnect(hdbc)
        rc = SQLFreeEnv(henv)
    End Function
    
    
    0 comments No comments

  2. John Thompson 0 Reputation points
    2023-02-09T15:07:17.0166667+00:00

    I appreciate your response, however the code listed crashes on the line "rc = SQLAllocConnect(ByVal henv, hdbc)." By crash I mean that the error processor fails to handle it. Access just opens up a dialog requesting I make a backup of the accdb file. Thanks again for replying.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.