Excel-VBA – Accessing Microsoft SQL Database

Excel-VBA – Accessing Microsoft SQL Database

 
   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      DbConnection
   End Sub
 
   Sub DbConnection()
 
      Dim strConn As String
      strConn = "Driver={SQL Server};Server=VMSER001;Database=BITest;UID=sa;PWD=P@ssw0rd"
 
      Dim cn As ADODB.Connection
      Set cn = New ADODB.Connection
 
      cn.Open strConn
 
      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
 
      With rs
 
         .ActiveConnection = cn
         .Open "SELECT * FROM [Product]"
 
         Dim i As Integer
 
         For i = 0 To .Fields.Count – 1
            Cells(1, i + 1).Value = .Fields(i).Name
            Cells(1, i + 1).Font.Bold = True
         Next i
 
         Range("A2").CopyFromRecordset rs
 
      End With
 
      Cells.EntireColumn.AutoFit
      Cells.EntireColumn.HorizontalAlignment = xlLeft
 
      rs.Close
      Set rs = Nothing
 
      cn.Close
      Set cn = Nothing
 
   End Sub