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 |