Sub PivotExternalDB()
Dim pv As PivotTable
Dim sh As Worksheet
Set sh = ActiveSheet
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
[COLOR="SeaGreen"]' Open the connection.[/COLOR]
Set cnnConn = New ADODB.Connection
With cnnConn
[COLOR="SeaGreen"] 'Access Database-----------------------------------------
'.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
'.Open "C:\A-Tools\DATA_DEMO\Examble.mdb"
'-----------------------------------------------
'SQL Server
'Create [B]ODBCSQLSERVER[/B] in Control Panel\Administrative Tools\Data [/COLOR]Sources (ODBC)\User DSN
.ConnectionString = _
"Provider=MSDASQL.1;Persist Security Info=False;Data Source=[B]ODBCSQLSERVER[/B]"
'-----------------------------------------------
.Open
End With
[COLOR="SeaGreen"]' Set the command text.[/COLOR]
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "SELECT * FROM NKC"
.CommandType = adCmdText
.Execute
End With
[COLOR="SeaGreen"]' Open the recordset.[/COLOR]
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
[COLOR="SeaGreen"]' Create a PivotTable cache and report.[/COLOR]
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="NKC"
End With
With ActiveSheet.PivotTables("NKC")
.SmallGrid = False
With .PivotFields("DVKH")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("NOTK")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("TTIEN")
.Orientation = xlDataField
.Position = 1
End With
End With
[COLOR="SeaGreen"]
' Close the connections and clean up.[/COLOR]
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing
End Sub