EXCEL and Imported Data Question

[puforee]

I have a MS Access DB on my server. I also have 5 spreadsheets that use Imported External Data (from the Access DB) within those spreadsheets.

I am using one spreadsheet as an example here.

On this spreadsheet I have two sheets that have charts. I also have two sheets that hold imported (linked by MS Query) data, one for each chart. I do NOT have "automatic data update when opened" turned on. I manually use the External Data tool bar to update the data.

Two situations...
1. When I select the "Refresh All" button on the tool bar it errors with, ODBC Microsoft Access Driver***** as an error.

2. If I go to my first sheet where data is stored and select the "Refresh Data" it works...BUT when I go to the second sheet where data is stored and select "Refresh Data" it errors with..."The text file specification ***** does not exist.*****"

Now, if I close and save the spreadsheet and then reopen it, the second sheet holding data refreshes normally. It does not matter which order I do the updates. I can only update one data sheet at a time. Some of the spreadsheets contain 5 chart sheets with their corresponding 5 data sheets.

Does anyone have an idea what may be going on? Remember, the Access DB, MS Queries, and spreadsheets are on a server.

 

[SkipVought]

Hi,

I never like to use the refresh all function. I'd rather refresh using a little macro like.

Sub RefreshAllQT()
  dim ws as worksheet, qt as querytable
  for each ws in worksheets
    for each qt in ws.querytables
       qt.refresh false
    next
  next
end sub

Try that.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[puforee]

Great Idea. Should I name the query area with a name?

Can you show me wat it would look like in this circumstance:

First Sheet named "All Regions" contains chart only

Second Sheet named "Seattle" contains chart only

Third sheet named "Data" Contains inported data for First sheet

Fourth sheet named "Data1" Contains inported data for Second sheet.

Using the real names as I have indicated will help me learn the code.

Thanks,

 

[SkipVought]

You do not need any actual sheet or qt names. It runs thru each sheet and IF there are QT's in the sheet it refreshes, otherwise on to the next sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[puforee]

Great, I will give it a shot. Thanks.
:-D :-D

 

[puforee]

Skip,

Another question. I reduced my spreadsheet to one sheet showing a chart and one sheet for the data, for testing of your code. The data is obtained through a MS Query. When I click refresh on the External Data tool bar it works.

Then, I put your code into play and added a button to run it. When the code runs it I get an error. When I click debug this shows:

Sub RefreshAllQT()
Dim ws As Worksheet, qt As QueryTable
For Each ws In Worksheets
For Each qt In ws.QueryTables
qt.Refresh False (This line is highlighted in yellow)
Next
Next
End Sub

Can you interpret what it is trying to tell me?

 

[SkipVought]

And what does the error message indicate?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[puforee]

Sorry I forgot to include it:

Microsoft Visual Basic
Run-time error '1004'
General ODBC Error

Thanks,

 

[SkipVought]

You have a problem with you QUERY SQL in that particular QT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[puforee]

Hmmmm.

Here is the SQL

SELECT `Total Flt NK EOC qry`.Month, `Total Flt NK EOC qry`.AvgOfQ1, `Total Flt NK EOC qry`.AvgOfQ2, `Total Flt NK EOC qry`.AvgOfQ3, `Total Flt NK EOC qry`.AvgOfQ4, `Total Flt NK EOC qry`.AvgOfQ5, `Total Flt NK EOC qry`.AvgOfQ6, `Total Flt NK EOC qry`.CountOfCount, `Total Flt NK EOC qry`.SumOfQ1_1, `Total Flt NK EOC qry`.SumOfQ1_2, `Total Flt NK EOC qry`.SumOfQ1_3, `Total Flt NK EOC qry`.SumOfQ1_4, `Total Flt NK EOC qry`.SumOfQ1_5, `Total Flt NK EOC qry`.SumOfQ2_1, `Total Flt NK EOC qry`.SumOfQ2_2, `Total Flt NK EOC qry`.SumOfQ2_3, `Total Flt NK EOC qry`.SumOfQ2_4, `Total Flt NK EOC qry`.SumOfQ2_5, `Total Flt NK EOC qry`.SumOfQ3_1, `Total Flt NK EOC qry`.SumOfQ3_2, `Total Flt NK EOC qry`.SumOfQ3_3, `Total Flt NK EOC qry`.SumOfQ3_4, `Total Flt NK EOC qry`.SumOfQ3_5, `Total Flt NK EOC qry`.SumOfQ4_1, `Total Flt NK EOC qry`.SumOfQ4_2, `Total Flt NK EOC qry`.SumOfQ4_3, `Total Flt NK EOC qry`.SumOfQ4_4, `Total Flt NK EOC qry`.SumOfQ4_5, `Total Flt NK EOC qry`.SumOfQ5_1, `Total Flt NK EOC qry`.SumOfQ5_2, `Total Flt NK EOC qry`.SumOfQ5_3, `Total Flt NK EOC qry`.SumOfQ5_4, `Total Flt NK EOC qry`.SumOfQ5_5, `Total Flt NK EOC qry`.SumOfQ6_1, `Total Flt NK EOC qry`.SumOfQ6_2, `Total Flt NK EOC qry`.SumOfQ6_3, `Total Flt NK EOC qry`.SumOfQ6_4, `Total Flt NK EOC qry`.SumOfQ6_5, `Total Flt NK EOC qry`.SumOfExpr1, `Total Flt NK EOC qry`.SumOfQ7Count
FROM `Total Flt NK EOC qry` `Total Flt NK EOC qry`

What do you think?

 

[SkipVought]

YOU ought to know if it runs or not. Can your Data > Refresh? Probably not.

Here's a cleaned-up version of your query...
[tt]
SELECT
Month
, AvgOfQ1
, AvgOfQ2
, AvgOfQ3
, AvgOfQ4
, AvgOfQ5
, AvgOfQ6
, CountOfCount
, SumOfQ1_1
, SumOfQ1_2
, SumOfQ1_3
, SumOfQ1_4
, SumOfQ1_5
, SumOfQ2_1
, SumOfQ2_2
, SumOfQ2_3
, SumOfQ2_4
, SumOfQ2_5
, SumOfQ3_1
, SumOfQ3_2
, SumOfQ3_3
, SumOfQ3_4
, SumOfQ3_5
, SumOfQ4_1
, SumOfQ4_2
, SumOfQ4_3
, SumOfQ4_4
, SumOfQ4_5
, SumOfQ5_1
, SumOfQ5_2
, SumOfQ5_3
, SumOfQ5_4
, SumOfQ5_5
, SumOfQ6_1
, SumOfQ6_2
, SumOfQ6_3
, SumOfQ6_4
, SumOfQ6_5
, SumOfExpr1
, SumOfQ7Count

FROM
`Total Flt NK EOC qry`
[/tt]
I observe that table Total Flt NK EOC qry, has AGGREGATIONS galore stored in it. This is not best and accepted DB practice. Chances are that your table is NOT Normalized, leading to all sorts of processing problems.

You have Month -- probably should be a REAL DATE (year, month, day)

You have all sorts of Q1 thru Q6 aggregations -- probably need a column in a new table just for the Q NUMBER, then another for the Q VALUE. So maybe

QDATE
QNUM
QVAL

ought to be your fields.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[puforee]

Interesting....what the qry(Total Flt NK EOC qry) (not a table)does....It sums data for several Catagories over the last (most recent) 12 months. I have a year and month calculation so the final data is sorted by year then month. This way the Excel chart always shows Sum'd data by catagory over the most recent 12 months.

But I will try to simplify the data. I am returning several fields I do not need.

Stand by, more to come.....and thanks for the advice.

John

 

[SkipVought]

Ahhhhh. You're querying a QUERY. OK. I guess I'm kinda dense!!!

But the issue at hand is that the query is not working.

My first statement still stands,

"YOU ought to know if it runs or not. Can your Data > Refresh? Probably not."



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[puforee]

The query does run...and when I use a normal refresh from the External Data tool bar it works. But....it does not work when I run the code. ???? I tried your code in another area and it worked fine. So, back to square one. I am going to simplify the MS Access query first. If that does not work I will turn the MS Access query into a make table query and then use excel to access the table. It might be more stable this way. But, would require the table to be refreshed first. Ho Hum.

Thanks for your input...keep any thoughts you may have coming.

John