Automation Excel & Access

  • Thread starter Thread starter GarethG
  • Start date Start date
G

GarethG

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
 
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
 
Ado should be able to handle that
plse adapt from this quick example
if you set up the maindatabase tables as LINKED tables in the mdb
following should work



Sub ADOstuff()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim sC As String
Dim sQ As String
Dim i As Integer

'Connect to MainDataBase
Set cn = New ADODB.Connection
sC = "driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=reporting2003.mdb;uid=admin;pwd="

cn.Open sC
sQ = " DROP Table DailySpecial"
cn.Execute sQ

sQ = " SELECT l.*,a.*, b.*" & _
" INTO DailySpecial " & _
" FROM (accounts a INNER JOIN balances b ON a.[acct#]=b.[acct#])
INNER JOIN lines l ON a.[line#]=l.[line#];"

cn.Execute sQ
cn.Close

sC = "driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=reporting2003.mdb;uid=admin;pwd="
sQ = " SELECT * " & _
" FROM DailySpecial " & _
" WHERE Period=2"

cn.Open sC
Set rs = New ADODB.Recordset
rs.Open sQ, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
' optional ways of retrieving a recordset
' Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
' Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet


Application.ScreenUpdating = False
With Worksheets.Add
For i = 1 To rs.Fields.Count
.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
.Cells(2, 1).CopyFromRecordset rs
End With
cn.Close

Application.ScreenUpdating = True

End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
GarethG said:
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
;)
Click to expand...

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.
Click to expand...

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.
Click to expand...

"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]));
 
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
 
Phobos said:
One of those Bosses eh? :o)


What query?

Is the query stored in Access?

Is it a simple Select query?

Post the SQL.


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

<rest snipped>

P *
Click to expand...
 
Back
Top