How to create a PT of version14 and above with Macros?

[feipezi]

Hi,

The following is part of the code that I used to create PTs of the old version (like in the Column field or Row field, there is no "Column Label" or "Row Label"). But some of my clients got used to the new version so I need to create new version of PTs with Macros. I recorded Macros (2nd part of the code) but hard to incorporate the old code.

Any ideas of how?

Thanks in advance.


Sub PW_UNQ()
Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim sql As String
Dim PTCache As PivotCache
Dim pt As PivotTable

Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DATA\unique_patient.mdb;"

sql = "SELECT * FROM UNQ"

Set RS = New ADODB.Recordset
Set RS.ActiveConnection = Con

RS.Open sql
On Error Resume Next
Sheets("Sheet1").UsedRange.Clear
On Error GoTo 0

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set PTCache.Recordset = RS

Set pt = PTCache.CreatePivotTable _
(TableDestination:=Sheets("Sheet1").Range("a2"), _
TableName:="one")
....
....


The recorded Macro (part of it).

....
Connections.Add "unique_patient", "", Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\DATA\unique_patient.mdb;Mode=Share Deny None;Extended " _
, _
"Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet" _
, _
" OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database P" _
, _
"assword="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;" _
, "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), Array( _
"UNQ"), 3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("unique_patient"), Version:=xlPivotTableVersion14) _
.CreatePivotTable TableDestination:="Sheet8!R1C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion14
....
....

 

[kjv1611]

I'm not sure you've formulated this as a question that folks can actually help answer. You say you built the code before.. so what did it do? What does it NOT do in the new version of Access? When you say new Version, which versions are we comparing? "Old Version" is what.. "New Version" is what? You'll need to provide more info if you want anyone to even have a chance at helping.

If it's not working, is it giving errors, just not giving desired results, what? What is / is not happening?



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57