Datasource quirks with OWC in Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kiln - 16 Feb 2004 23:13 GMT

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.

Reply to this Message

Mike Wachal - 17 Feb 2004 19:46 GMT

> 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
[quoted text clipped - 19 lines]
> in a broken manner. This has been asked before, at least in part, but I
> didn't see any responses.

Hi,

I'm not sure what your question is here.

Access 2002/2003 use the PivotTable and PivotChart from OWC10 natively,
there is no need to insert the controls into Forms or Reports as ActiveX
controls. The object model for the pivot views is available to you through
the Form/Report programming interface should you need to control the view
programmatically.

Connections to external data in Access is accomplished through linking and
importing of data into tables. Access already knows how to bind the data
from local and linked tables to objects in pivot view, so there isn't a
need for any other binding mechanisim. We do use the data source control to
handle databinding in Data Access Pages, and you can also use the DSC when
developing web pages.

What you descirbe is the designed behavior of pivot views in Access. If you
have a specific question about how to accomplish something, let me know and
I'll try to answer it.

Signature

Regards,
Mike Wachal
Microsoft Corporation

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

Reply to this Message

kiln - 21 Feb 2004 17:31 GMT

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.

> > 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
[quoted text clipped - 40 lines]
> have a specific question about how to accomplish something, let me know and
> I'll try to answer it.

Reply to this Message

Mike Wachal - 23 Feb 2004 18:04 GMT

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.

Signature

Regards,
Mike Wachal
Microsoft Corporation

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

> 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
[quoted text clipped - 73 lines]
>> have a specific question about how to accomplish something, let me know and
>> I'll try to answer it.

Reply to this Message

kiln - 24 Feb 2004 01:47 GMT

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.

> 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
[quoted text clipped - 20 lines]
> view subform does not handle, possible someone on this forum will have
> ideas how to accomplish that.

Reply to this Message

Mike Wachal - 26 Feb 2004 17:58 GMT

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.

Signature

Regards,
Mike Wachal
Microsoft Corporation

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

> 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
[quoted text clipped - 47 lines]
>> view subform does not handle, possible someone on this forum will have
>> ideas how to accomplish that.

Reply to this Message

kiln - 26 Feb 2004 19:48 GMT

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.

> 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
[quoted text clipped - 11 lines]
>
> I'm happy to have provide a few answer and some clarification.

Reply to this Message

kiln - 27 Feb 2004 01:40 GMT

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.

Reply to this Message