I've been using ODBC for external data sources to pivot tables. I would now
like to switch to using ADO. With ODBC, the code looks like:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array("ODBC;DSN=MyDSN;;;;;DATABASE=MyDatabase;")
.CommandType = xlCmdSql
.CommandText = Worksheets("SQL1").Range("A100").Value
.CreatePivotTable TableDestination:="R8C1", TableName:= _
"PivotTable1"
End With

How could I do the same with ADO?

TIA,

Wolfie