Connect to SQL Server in VBA if the server requires specific username and password?

Question

I realized today that when you connect to SQL Server data source in Excel's native connection, it doesn't allow you to enter in a specific username and password. It just asks for server name and database.

In VBA , under the assumption that I wanted to import data from a SQL Server data query into Sheet1, can you please help me understand how to write that code?

For purposes of this exercise:

SQL Server Connection INFO
    Server Name:  TestingS,1633
    Database Name: CarSales
    username:  car
    password: sales
    
    The query I want to run for simplicity sake can be:  "select * from table"

I have been doing some research , but am getting a bit lost. I have no problem setting up standard queries with custom SQL via ODBC, but because I need VBA, it's very tricky for me. Please help.

Answer 1

This is an example of MSSQL.

Sub testMSSQL()

 'Reference  Microsoft ActiveX data object Library 2.8 ~~

   Dim cnn As ADODB.Connection
   Dim strSQL As String
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   strSQL = "select * from table"
   
   Set cnn = New ADODB.Connection

   'Set the provider property to the OLE DB Provider for ODBC.
   'cnn.Provider = "MSDASQL"
   'cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
   'cnn.Provider = "MSOLAP"
   cnn.Provider = "SQLOLEDB.1" '<~~ mssql
   
   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=TestingS;uid=car;pwd=sales;database=CarSales"

    
    Set rs = New ADODB.Recordset

    rs.Open strSQL, cnn.ConnectionString, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    cnn.Open
    If cnn.State = adStateOpen Then
    Else
       MsgBox "Not connected server"
       Exit Sub
    End If
    
    If Not rs.EOF Then
         With Ws
            .Range("a1").CurrentRegion.ClearContents
            For i = 0 To rs.Fields.Count - 1
               .Cells(1, i + 1).Value = rs.Fields(i).Name
            Next
            .Range("a2").CopyFromRecordset rs
            .Columns.AutoFit
        End With
    Else
        MsgBox "No Data!!", vbCritical
    End If
    rs.Close
    Set rs = Nothing
   
    cnn.Close
    Set cnn = Nothing
End Sub