How to connect to an SQL Server from VBA in Excel? – Excel

The Problem:

A user wants to connect to a Microsoft SQL Server database from Excel VBA to download data. They have installed the necessary drivers but are encountering an "Data source name not found and no default driver specified" error when attempting to connect using the ADODB library. They are seeking help to resolve this error and determine if VBA is the appropriate tool for their task of re-running a query with fresh parameters and refreshing the result cells without exposing the end user to SQL.

The Solutions:

Solution 1: Recording Connection Creation

• Attempt recording a connection to the database using Excel. This generates a sample connection string.
• Ensure you properly instantiate the connection:
  • Use Dim connection As ADODB.connection
  • Set it to a new connection using Set connection = New ADODB.connection

Solution 2: Dynamic Query Refresh

• Use a sample code like below to dynamically modify and refresh a connection based on user input or cell values:

Sub McrPricePerSupplRefresh()
    Dim Suppl As String

    Suppl = InputBox("Which supplier (code)?")
    With ActiveWorkbook.Connections("SupplierPrices").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "SELECT * FROM WORSTDATABASE.PUB." & Chr(34) & "peoplemadethiswithdashes-veryannoying" & Chr(34) & " "WHERE " & Chr(34) & "peoplemadethiswithdashes-veryannoying" & Chr(34) & "." & Chr(34) & "supplier-code" & Chr(34) & "='" & Suppl & "'")
        .CommandType = xlCmdSql
        .Connection = "ODBC;DSN=ourERP;UID=MYNAME;HOST=OURHOST;PORT=2007;DB=WORSTDATABASE;PWD=NONYA"
        .RefreshOnFileOpen = True
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    ActiveWorkbook.Connections("SupplierPrices").Refresh
End Sub

• Customize the command text based on your query and modify it dynamically based on variables or cell values.

Video Explanation:

The following video, titled "Connect to Microsoft SQL Server using VBA - YouTube", provides additional insights and in-depth exploration related to the topics discussed in this post.

In this video I'm going to show You how to connect to Microsoft SQL Server database using Excel VBA.