Programming Excel with VBA and .NET

Name

command.ActiveConnection[= setting]

Synopsis

Sets or returns the connection used by the specified command. The following code returns a record by executing a SQL command using the active connection:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strDbPath As String
 
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
strDbPath = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & strDbPath
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandText = "SELECT * FROM Employees Where EmployeeID = 9;"
Set rs = cmd.Execute
 
Set qt = ActiveSheet.QueryTables.Add(Connection:=rs, _
    Destination:=ActiveSheet.Range("A3"))
qt.Refresh
 
ActiveSheet.Range("A1") = qt.Recordset.Source
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing