PDA

View Full Version : OWC - error in AllowEdits



stanl
11-30-2005, 01:14 PM
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
12-01-2005, 09:47 AM
Stan,

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

TIA and all the very best from,

Dennis

stanl
12-01-2005, 02:59 PM
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
12-02-2005, 06:02 AM
Dennis;

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

Thank you,

Stan

XL-Dennis
12-03-2005, 11:22 AM
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:


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


Then I tested it with the following approach:

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


Both tests gave the same output - not editable.

A check to see the Edit status for the P/T like:

MsgBox Me.PivotTable1.ActiveView.AllowEdits


gave me the answer: True

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

Kind regards,
Dennis

stanl
12-03-2005, 04:18 PM
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.