Good morning (it is here -and cold too!)
I am working on a project whereby an Access database is updated on a
daily basis. A Pivot Table displays the information as required and my
boss is happy, and I don't get nagged for the information every morning
;)

I have most of the project set up, except the automated side. I was
thinking of implementing the following solution and am basically
looking for an alternative method(my solution seems a little untidy).

When the Excel document opens my macro executes the Access query which
refreshes the database.
Once Access has refreshed the database it executes a macro within Excel
to refresh the pivot table.
Access then closes.
Pivot Table is refreshed, boss is happy :)

I am concerned that this "handshaking" between the two applications may
cause some problems (which I have not forseen).
My main concern is that Excel has a macro running which is interacting
with Access and Access is then in turn interacting with Excel.
Is there a way in which Excel is aware of the "progress" of the Access
query? So that Excel itself is able to refresh the pivot table rather
than relying on Access to execute this command?

Another small issue (but not my main concern) is that when Access is
called from Excel is it possible to "hide" Access from the user so that
it does not appear in the task bar?

Appreciate any help
Appologies for any spelling errors



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

have a look at www.erlandsendata.no for working with ado and dao.
which may avoid opening access altogether, as you'll be working with the
..mdb file directly.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


GarethG wrote:

Good morning (it is here -and cold too!)
I am working on a project whereby an Access database is updated on a
daily basis. A Pivot Table displays the information as required and my
boss is happy, and I don't get nagged for the information every morning
;)

I have most of the project set up, except the automated side. I was
thinking of implementing the following solution and am basically
looking for an alternative method(my solution seems a little untidy).

When the Excel document opens my macro executes the Access query which
refreshes the database.
Once Access has refreshed the database it executes a macro within Excel
to refresh the pivot table.
Access then closes.
Pivot Table is refreshed, boss is happy :)

I am concerned that this "handshaking" between the two applications may
cause some problems (which I have not forseen).
My main concern is that Excel has a macro running which is interacting
with Access and Access is then in turn interacting with Excel.
Is there a way in which Excel is aware of the "progress" of the Access
query? So that Excel itself is able to refresh the pivot table rather
than relying on Access to execute this command?

Another small issue (but not my main concern) is that when Access is
called from Excel is it possible to "hide" Access from the user so that
it does not appear in the task bar?

Appreciate any help
Appologies for any spelling errors



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

Thanks for the link, some helpful information on the site.

Looking back on my initial post I probably did not make it very clear
that Access generates the database based on a query which
"interrogates" a larger database (effectively creating a data
warehouse-if that is the correct term for my .mdb file).

Access or a query within Access needs to be executed so as far as I can
tell, Access needs to be running, if I am incorrect on this feel free
to mock me :)

-Excel file is opened
-'On Open' Macro executes query in Access file
-Access Query extracts required information from 'live' database
-Excel Pivot Table refreshes (either Access executes this or Excel
determines when Access has finished querying 'live database' then
refreshes the Pivot Table
-Access Closes

Apologies if I did not provide enough information in my original post.

Gareth Grindal
UK -Still Cold



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

"GarethG" wrote in message
...
Good morning (it is here -and cold too!)
I am working on a project whereby an Access database is updated on a
daily basis. A Pivot Table displays the information as required and my
boss is happy, and I don't get nagged for the information every morning
;)

One of those Bosses eh? :o)

I have most of the project set up, except the automated side. I was
thinking of implementing the following solution and am basically
looking for an alternative method(my solution seems a little untidy).

When the Excel document opens my macro executes the Access query which
refreshes the database.

What query?

Is the query stored in Access?

Is it a simple Select query?

Post the SQL.

Once Access has refreshed the database it executes a macro within Excel
to refresh the pivot table.

"refreshed the database"?


<rest snipped

P

Yes one of those bosses.

The query is in Access, its a pretty simple query that selects data
based on a range (the Access file was originally used to export the
information as a text file or print out which was then manually
re-entered into an Excel worksheet-but I did not want to do that so I
export the data into a seperate database).

Access extracts the information and dumps it into the new database (I
seperated the "data warehouse" and the Access query to make it a little
more manageable).

There are effectively three files involved here, the Excel file with my
Pivot Table, the Access file that queries the database, and the Access
Database which holds my extracted information.

SQL for the Access Query:
SELECT SANAL.SA_DACCNT AS [ACCOUNT CODE], SANAL.SA_TRDATE AS [DATE],
SANAL.SA_TRREF AS [DOC REF], CNAME.CN_DESC AS [PRODUCT DESCRIPTION],
CNAME.CN_REF AS [PRODUCT CODE], SNAME.SN_NAME AS [BRANCH NAME],
SANAL.SA_TRVALUE AS [SALES VALUE], [SANAL]![SA_QTY]*[CNAME]![CN_UNITW]
AS Kilos, SNAME.SN_CUSTYPE AS [CUSTOMER TYPE], SNAME.SN_REGION AS
REGION, SNAME.SN_TERRTRY AS TERRITORY, SANAL.SA_ANCODE AS [PRODUCT
GROUP], SNAME.SN_ANALSYS AS [CUSTOMER CODE], SNAME.SN_PSTCODE AS [POST
CODE], SANAL.SA_QTY AS QUANTITY
FROM (SANAL RIGHT JOIN CNAME ON SANAL.SA_PRODUCT = CNAME.CN_REF) LEFT
JOIN SNAME ON SANAL.SA_DACCNT = SNAME.SN_ACCOUNT
WHERE (((SANAL.SA_DACCNT)=[Forms]![GetReportParams]![comboFirstAcc]
And (SANAL.SA_DACCNT)<=[Forms]![GetReportParams]![comboLastAcc]) AND
((SANAL.SA_TRDATE)=[Forms]![GetReportParams]![txtFirstDateCurrent] And
(SANAL.SA_TRDATE)<=[Forms]![GetReportParams]![txtLastDateCurrent]) AND
((SANAL.SA_TRREF) Is Not Null) AND
((SNAME.SN_CUSTYPE)=[Forms]![GetReportParams]![comboFirstType] And
(SNAME.SN_CUSTYPE)<=[Forms]![GetReportParams]![comboLastType]) AND
((SNAME.SN_REGION)=[Forms]![GetReportParams]![comboFirstRegion] And
(SNAME.SN_REGION)<=[Forms]![GetReportParams]![comboLastRegion]) AND
((SNAME.SN_TERRTRY)=[Forms]![GetReportParams]![comboFirstTerr] And
(SNAME.SN_TERRTRY)<=[Forms]![GetReportParams]![comboLastTerr]) AND
((SANAL.SA_ANCODE)=[Forms]![GetReportParams]![comboFirstSalesCode] And
(SANAL.SA_ANCODE)<=[Forms]![GetReportParams]![comboLastSalesCode]));



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Gareth,

You can run that query from within Excel (or change the SQL to a Crosstab
Query to eliminate the need for a Pivot Table within Excel) by using MS
Query.

Data | Get External Data | New Database Query...

and paste your SQL into the SQL box once your connection to the Access DB is
established.

This will eliminate the need to automate Access altogether, all you need to
do is refresh the query now and then.

P


"GarethG" wrote in message
...
Yes one of those bosses.

The query is in Access, its a pretty simple query that selects data
based on a range (the Access file was originally used to export the
information as a text file or print out which was then manually
re-entered into an Excel worksheet-but I did not want to do that so I
export the data into a seperate database).

Access extracts the information and dumps it into the new database (I
seperated the "data warehouse" and the Access query to make it a little
more manageable).

There are effectively three files involved here, the Excel file with my
Pivot Table, the Access file that queries the database, and the Access
Database which holds my extracted information.

SQL for the Access Query:
SELECT SANAL.SA_DACCNT AS [ACCOUNT CODE], SANAL.SA_TRDATE AS [DATE],
SANAL.SA_TRREF AS [DOC REF], CNAME.CN_DESC AS [PRODUCT DESCRIPTION],
CNAME.CN_REF AS [PRODUCT CODE], SNAME.SN_NAME AS [BRANCH NAME],
SANAL.SA_TRVALUE AS [SALES VALUE], [SANAL]![SA_QTY]*[CNAME]![CN_UNITW]
AS Kilos, SNAME.SN_CUSTYPE AS [CUSTOMER TYPE], SNAME.SN_REGION AS
REGION, SNAME.SN_TERRTRY AS TERRITORY, SANAL.SA_ANCODE AS [PRODUCT
GROUP], SNAME.SN_ANALSYS AS [CUSTOMER CODE], SNAME.SN_PSTCODE AS [POST
CODE], SANAL.SA_QTY AS QUANTITY
FROM (SANAL RIGHT JOIN CNAME ON SANAL.SA_PRODUCT = CNAME.CN_REF) LEFT
JOIN SNAME ON SANAL.SA_DACCNT = SNAME.SN_ACCOUNT
WHERE (((SANAL.SA_DACCNT)=[Forms]![GetReportParams]![comboFirstAcc]
And (SANAL.SA_DACCNT)<=[Forms]![GetReportParams]![comboLastAcc]) AND
((SANAL.SA_TRDATE)=[Forms]![GetReportParams]![txtFirstDateCurrent] And
(SANAL.SA_TRDATE)<=[Forms]![GetReportParams]![txtLastDateCurrent]) AND
((SANAL.SA_TRREF) Is Not Null) AND
((SNAME.SN_CUSTYPE)=[Forms]![GetReportParams]![comboFirstType] And
(SNAME.SN_CUSTYPE)<=[Forms]![GetReportParams]![comboLastType]) AND
((SNAME.SN_REGION)=[Forms]![GetReportParams]![comboFirstRegion] And
(SNAME.SN_REGION)<=[Forms]![GetReportParams]![comboLastRegion]) AND
((SNAME.SN_TERRTRY)=[Forms]![GetReportParams]![comboFirstTerr] And
(SNAME.SN_TERRTRY)<=[Forms]![GetReportParams]![comboLastTerr]) AND
((SANAL.SA_ANCODE)=[Forms]![GetReportParams]![comboFirstSalesCode] And
(SANAL.SA_ANCODE)<=[Forms]![GetReportParams]![comboLastSalesCode]));



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Phobos wrote:
*"GarethG" wrote in message
...
Good morning (it is here -and cold too!)
I am working on a project whereby an Access database is updated on
a
daily basis. A Pivot Table displays the information as required
and my
boss is happy, and I don't get nagged for the information every
morning
;)

One of those Bosses eh? :o)

I have most of the project set up, except the automated side. I
was
thinking of implementing the following solution and am basically
looking for an alternative method(my solution seems a little
untidy).

When the Excel document opens my macro executes the Access query
which
refreshes the database.

What query?

Is the query stored in Access?

Is it a simple Select query?

Post the SQL.

Once Access has refreshed the database it executes a macro within
Excel
to refresh the pivot table.

"refreshed the database"?
Updated-sorry getting my terminology all mixed up.

<rest snipped

P *



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/