Test SQL Server Connection

Question

I built an Excel-based tool that uses ODBC connections and querytables to pull data from a SQL Server (2014). This tool has to be compatible with Mac Office 2016 (hence the ODBC and querytables).

I'm struggling with testing whether the user can connect to the SQL Server. With the ODBC connection, if it fails to connect, a SQL Server error login dialog opens and then a login dialog opens before my error handling can take effect.

Every solution to this issue that I found uses either ADODB (which isn't compatible with Mac) or is a solution to Access VBA. This answer talks about TCP connections, but I was unable to find anything about TCP and VBA that looked compatible with Mac.

Does anyone know of a way to test a SQL Server connection that would be compatible with Office for Mac 2016 using VBA?

An example of how I'm using ODBC and querytables to connect to SQL Server.

Sub main()
    On Error GoTo err1
    Dim connstring As String
    Dim sqlstring As String
    Dim dArr As Variant
    Dim qt As QueryTable

    connstring = "ODBC;DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=master;Trusted_Connection=yes"
    sqlstring = "SELECT 1"

    Set qt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=ThisWorkbook.Sheets(1).Range("A1"), Sql:=sqlstring)

    With qt
      .BackgroundQuery = False
      .RowNumbers = False
      .Refresh
    End With

    dArr = qt.ResultRange.Value
    qt.Delete

    ThisWorkbook.Sheets(1).Cells.Clear
    Exit Sub
err1:
Stop
End Sub

have you considered using try/catch? – Takarii Feb 09 '16 at 08:22 — Takarii, Feb 09 '16 at 08:22
@Takarii I'm pretty sure VBA doesn't support try/catch... – ARich Feb 09 '16 at 14:23 — ARich, Feb 09 '16 at 14:23
Right you are. I'm thinking of VB – Takarii Feb 09 '16 at 15:07 — Takarii, Feb 09 '16 at 15:07

spioter · Answer 1 · 2019-12-05T14:22:13.927

Same issue exists on Windows. Here are 2 possible answers.

1) Not sure it is compatible with MAC: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_other-mso_2007/connection-error-when-network-down/35d5be85-f850-4953-a393-6fafa687961f?messageId=0c063768-69c7-43ce-a9ed-9139f8cfd830

2) in principle, this should work I have not done it. Requires sqlcmd.exe = OS level sql client where you can query at command line. At OS prompt, get a simple "select 1" to work using sqlcmd.exe that either succeeds and creates a sqlSuccess.{TestID}.tmp file or fails and does nothing. Then build a VBA function, say fnGetConnString() that runs the commands and verifies that the sqlSuccess.{TestID}.tmp file exists and deletes it. The function could halt your program if it fails or return the connstring you desire and run as (pseudo code):

connstring = fnGetConnString( {connection string inputs})
if connstring = '' then 
    msgbox( "Not a valid connection or sever unavailable: {connection string inputs}"
    exit sub ''#halt all
end if

Among other things, the function needs to take {TestID} an input to guarantee that it has the right file.

for sqlcmd help, see: https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

for OS command from VBA see: Execute a command in command prompt using excel VBA

Test SQL Server Connection

1 Answers1

Linked