OWC - error in AllowEdits

[stanl]

I have an Access Table which contains numeric summary fields by City/County - I can set it as a datasource for an OWC Pivot as either an XML persisted recordset of as a SELECT Statement from the table itself. The table has a primary key, but if I allow editing

oPIV.ActiveView.AllowDetails = True
oPIV.ActiveView.AllowEdits = True

I receive a general OLE error. How do I make the datasource not be treated as 'readonly'? [partial screenshot attached]

TIA

Stan

[XL-Dennis]

Stan,

Is it possible for You to send me via e-mail the project?

TIA and all the very best from,

Dennis

[stanl]

Dennis,

It's part of a larger project (about 345meg), and may involve sensitive data. But the more general question is "Can you ever set AllowEdits=True with a Pivot Table based on an ADO Recordset?" I'll try to extract out enough of the project to send you that replicates the error?

Stan

[stanl]

Dennis;

I need an email address where I can send you a zip file. E-mail me at principaldirections at mindspring dot com and I will attach a sample xml and sub.

Thank you,

Stan

[XL-Dennis]

Hi Stan,

I made my own case to see and I must admit that I find it a little bit weird.

First I tested it with the following approach:

[vba]
Dim stCon As String, stFile As String

stFile = "C:\Report.xml"
stCon = "Provider=MSPersist;"

With Me.PivotTable1
.XMLData = stFile
.ConnectionString = stCon
.CommandText = stFile
.ActiveData.ShowDetails
With .ActiveView
.AllowAdditions = True
.AllowDeletions = True
.AllowEdits = True
End With
End With
[/vba]

Then I tested it with the following approach:
[vba]
Dim stCon As String, stFile As String

stFile = "C:\Report.xml"
stCon = "Provider=MSPersist;"
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.Open stFile, stCon, adOpenStatic, adLockBatchOptimistic, adCmdFile
Set .ActiveConnection = Nothing
End With

With Me.PivotTable1
.DataSource = rst
With .ActiveView
.AllowAdditions = True
.AllowDeletions = True
.AllowEdits = True
End With
End With
[/vba]

Both tests gave the same output - not editable.

A check to see the Edit status for the P/T like:
[vba]
MsgBox Me.PivotTable1.ActiveView.AllowEdits
[/vba]

gave me the answer: True

Hm, I wonder if it is per design when using XML-file as datasource?

Kind regards,
Dennis

[stanl]

I agree that it is strange.

'does not allow edits
'oPIV.ConnectionString = "Provider=MSpersist"
'oPIV.CommandText = "C:\owc\test.xml"
 
'does allow edits
oPIV.ConnectionString = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=C:\owc\owc_data.mdb;"
oPIV.CommandText = "SELECT * FROM test ORDER BY County,City;"

even though test.xml is persisted as keyset, batchoptimistic from the test table and can be updated as a normal ado recordset. I had previously tried to create a recordset object to open the xml file as keyset,batchoptimistic and assign it to oPIV.DataSource and it still cannot be updated.

But I still think there is a way.