Using SQL Express inside Excel via VBA

Software Engineering 1990 views

Using SQL Express inside Excel via VBA:

Private Sub buttonLoad_Click()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.RecordSet

    Set conn = New ADODB.Connection
    With conn
        .connectionString = "Driver={SQL Server native Client 11.0};Server=(localdb)\v11.0;AttachDBFileName=C:\SQL Server Sample Databases\AdventureWorks2012_Data.mdf;Trusted_Connection=Yes"
        .CursorLocation = adUseClient
        .CommandTimeout = 0
        .Open
    End With

    Set rs = New ADODB.RecordSet
    With rs
        .ActiveConnection = conn
        .Source = "SELECT DISTINCT FirstName + ' ' + LastName FROM Person.Person ORDER BY 1"
        .Open
    End With

    Me.Cells(4, 1).CopyFromRecordset rs

    rs.Close
    conn.Close

    Set rs = Nothing
    Set conn = Nothing

End Sub