Access Qry in Excel Pivot

[Lungta]

This has got me stumped - hope someone can point me in the right direction.

We have a large db split with shared backend on the common drive. Windows
XP/Access 2003, 12 users. Not authorized to make enhancements directly in
Access right now, so can't use Access pivots.

We intend to distribute 8 Excel workbooks with pivots that use Access
queries from the main db as the source. They don't seem to have their hooks
into the db except for the moment of refresh, so we're hoping this means
minimal performance hit.

Question is, if testing goes ok, where in heck do we store the queries on
the real db? Each user will save their own copies of the workbooks for
customizing their pivots. The workbooks can move easily but they
self-destruct if source is moved/renamed. Can't be tied to each person's C:
drive, and can't have everyone accessing 1 shared dummy front end on the
network, or can we?

Seems almost like the source queries should be in the shared back end, but I
would really appreciate input.

 

[Andy Hull]

Hi

The queries should go in the front end. The back end should contain just the
data.

It is possible for all users to use the same front end on the network
although this is strongly advised against.

With a split database each user should have their own copy of the front end
which can be on each user's pc or exist as separate files on the network.

These front ends will be identical and when development changes are
necessary you can make the changes on your own copy and once tested this new
version can be distributed to each user (overwriting their previous copies
wherever they happen to exist).

When each user opens their pivot table file and refreshes it, the pivot will
first look to that user's front end database for the query which will then
get its data, via the linked tables, from the single back end database.

hth

Andy Hull

 

[Lungta]

Thanks, Andy, that's what I was trying to figure out. If we can manage to
locate the front ends in a folder under C:\Pivot instead of on each user's
personalized desktop, the path would become the same for all front ends.
So you're saying that the query will just find the literal path and not be
tied to any specific c: drive, is that correct?

 

[Andy Hull]

Yes, that's right.

Looking back at my previous post, I see I didn't really make it clear.

Different companies have different pc & network setups.

If you are allowed to use the user's pc then go for a folder that will be
the same on every pc.

At our company, all users have personal space on the network and it is
mapped to the same letter so we can use that as well.

Andy

 

[Lungta]

That's great - simplifies things for updating, too.

Thank you very much!