Datasource quirks with OWC in Access

[kiln]

OWC 10 seems to be a very strange object in Access XP. With a wizard you
can make a form display as a pivot table; but the pivot table ActiveX
control is not implanted anywhere on the form, and no code is carried
with the form that would set up a pivot table object, assign data, store
the previous filters etc. Yet it all happens. One can use for the
datasource a local table without problems.

If on the other hand one inserts the pivot table as an ActiveX control
onto a form, you can't (via the gui interface) assign a local table as a
data source. All you can do is to pick Jet 4 as the OLE provider, and
point to another db for the data (else a "db in use exclusively" error).
That is a major disconnect for an Access db. There is also a Data Source
ActiveX control that comes with the OWC, but it appears dead on an
Access form - there is no way to link it to a data source and can't be
set up in any manner.

You can successfully do

pt.CommandText = "SELECT tblPerson.* FROM tblPerson"

But the gui interface, and the Data Source control seem to be operating
in a broken manner. This has been asked before, at least in part, but I
didn't see any responses.

 

[kiln]

Yes, I know that the OWC functionality is available via the regular form
interface of Access 2002. I have been wanting to use the pivot table on
a form with other design elements, and a plain form in pivot view has
many limitations (as it does in datasheet view). I could use the pivot
table via a form view in a subform control but that wan't my first
choice.

Another reason to try to use PT as a control natively is reporting. PT
is not printer freindly in general. Unfortunately PT as a control is
awful in an Access report. As a form in a report sub report control it
actually works better.

Incidentally, you mentioned twice in your reponse that the pivot table
is supported by Access in both Forms and Reports. I've never seen any
support for pivot tables in Access reports - if there is, could you
explian where it is?

So I've been trying to learn what the pivot table ActiveX control is
capable of. My questions here were about the quirks that it exhibits in
Access. Access is a terrible container for ActiveX controls, so I more
or less am used to fighting for every inch, and losing many battles,
when I need ActiveX controls.

Then there is the spreadsheet component of the OWC. It is quite useful
in Access. I figured that since Microsoft has apparently done it's
homework with the spreadsheet in Access, then it might also be possible
to get the PT to work well as a native control. Pivot table does work as
a native ActiveX control, but with abberations.

You know Access developers have always had push the tool beyond what
Microsoft has handed off; otherwise we'd all be building databases like
Northwind.

 

[Mike Wachal]

When I say that you can use a PT in a Report, I mean that you can insert a
Form in PT view as a sub-report; I should have been more clear. Doing this
is functionally equivalent to the behavior we have supported in past
versions with the MS Graph object.

I don't know that I'd agree that a Form in PT view has many limitations. To
my knowledge, we support almost all the same features that the PT ActiveX
control does. We also have a complete object model for the PT so you can
manipulate it via code. If you want to build a form that has a "dynamic" PT
based on other controls, the supported way to do that is by using a
sub-form that is in PT view. It seems that you've guessed that already.

Just to be clear, the design of the PT in Access is that it is a View of a
databound object, Table, Query and Form, and as such, it stands alone just
like Tables, Queries and Forms do. You can use the PT view of a Form as a
Subform in both Forms and Reports; in my experience this works well and
meets my needs.

I would suggest trying to work with the PT view as a subform to see if it
meets your needs. I don't know of anything to suggest that using an ActiveX
control is better than using a subform if they can both accomplish the same
thing. If there is a specific problem you are trying to solve that as PT
view subform does not handle, possible someone on this forum will have
ideas how to accomplish that.

--
Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[kiln]

It may be that you are right, that a form view pivot table is nearly as
versatile as the ActiveX control. You asked why I was investigating it
as an ActiveX control, and in a way the shoe is on the other foot. Why
shouldn't I? I mean, it's available to use via Insert, and until I
experimented and found out what it could do, I couldn't know that it had
issues in Access... and why would I expect it to since it's (apparently)
more directly supported than other ActiveX controls like Treeview. There
is no mention of OWC as ActiveX control in the Access help files (I
think), but also no prohibition. And as I mentioned, the only way to use
the spreadsheet aspect of OWC is via an ActiveX control. There wasn't
any reason to suppose that the pivot was any different, or less capable
than the form view edition.

Another reason I worked with it as an ActiveX control is that the
microsoft.public.office.developer.web.components ng is the main source
of info for the OWC (albeit a very sparse source) and few there are
using it in Access as a form view, they are using it as a control in
ASPX etc. And the OWC Toolpack uses it as an ActiveX control, etc etc.

BTW, I'd already tried the pivot table as a form view in a report
subreport and am glad it works... but it's tricky with paging and so
forth. I'll have to see how well it works for my needs in production.

Anyways you have made it clear that MS didn't intend for the pivot table
to be implemented as an ActiveX control in Access. Thanks. It is
worthwhile to have it stated clearly like that.

 

[Mike Wachal]

Thanks for the feedback. I wasn't trying to suggest that you shouldn't
investigate using the OWC as an ActiveX control in Access, I was more
interested in whether your investigation was the result of a limitation you
had found in the built-in implementation.

The reason that the spreadsheet aspect of the OWC is not implemented
similarly to the PT or PC is that Access already supports a datasheet view
which is structurally similar to the spreadsheet view. Granted, the
datasheet view doesn't support "excel-like" functionality; if you need that
"excel-like" functionality, the spreadsheet control may be the way to go.
There is also the alternative of inserting an Excel document direction into
an Access object, which is actually how we supported pivoting before
incorporating the OWC in Access 2002.

I'm happy to have provide a few answer and some clarification.

--
Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[kiln]

Good enough. Yes, it's the Excel functionality of the OWC spreadsheet
component that adds a new capability to Access.

So far, I'm finding that the form view of the OWC pivot table is better
behaved (in Access) than the ActiveX control was. For instace, column
widths seem to auto-adjust properly. I was having fits trying to get
some of these basics to behave as an ActiveX control in Access. So,
could be that form view is going to work out well. So far all of the
programmatic mods that I'd tried on the ActiveX control have succeeded,
but I've not tried everything yet.

I and many others have had a long term need for Access to become a
civlized host for ActiveX controls. It would be such a boon. But the
request has been out there since the start of ActiveX technology; that's
a very long time now. Access is just a bottom tier priority for
Microsoft.

 

[kiln]

Mike

I have a question for you that I've not been able to resolve. I know how
to set a filter axis field to all for only a single selection:

.FieldSets("Conversion").AllowMultiFilter = False

However, even after I've done this, an "All" selection remains in the
list of items. There are times when I don't want to allow an "All"
selection, is there a setting for this? Probably I can trap for an
"All" selection and reverse it, but that is pretty klunky.