Force Access to purge its Excel memory?

[Guest]

When I link an Excel sheet as an Access table, Access memorizes the values of
all the calculated fields. When new values are plugged into other cells the
calculated fields never change in Access. I can delete the link, close
Access, open Excel to verify that the values in the calculated fields have
indeed changed as expected; then close Excel, open Access, relink the table,
and Access always plugs in the old originally-memorized values into the
calculated fields.

The only way I've found to force Access to purge its memory and take a fresh
look is to make a structural change anywhere in the Excel file. That seems
to work every time.

Hopefully somebody knows some way to force Access to purge its Excel memory
with Basic.

I'm using Access 2003 and Excel 2000

 

[John Nurick]

Hi Wayne,

I'm not certain I understand your description correctly. I've never
found that Access "memorizes" any data from a linked table. But if you
have a linked table connected to an Excel worksheet with formulas in it,
those formulas are not recalculated when you update the table from
Access (at least not unless the workbook is open in Excel and set to
recalculate automatically).

I suppose the general lesson is that if you want calculated fields in a
database you should put them in a query<g>.

 

[David C. Holley]

Are you certain the you're LINKING to Excel as opposed to Importing?

 

[Guest]

Even if I delete the linked tables, shut down Access, open Excel to confirm
the new values and close it, then open Access anew, and relink the tables
from scratch, Access will still not show me the current values in calculated
fields, but plugs in the memorized values from the first time Access linked
to that table.

I couldn't believe what I was seeing, so I made a simple new Excel table
with nothing but a3=a1*a2. Then I made a new Access database that does
nothing but link that simple spreadsheet as a table. The first time I linked
the table it showed what Excel currently showed 2*3=6 (2, 3, 6, in the three
fields respectively). I replaced the 2 with 4, and Access didn't update. So
I deleted the table and relinked, but got the same result. Then I deleted the
table again, closed Access and opened Excel and saw 4*3=12, just as expected.
I closed Excel, reopened Access, relinked the table and it showed 4*3=6 (4,
3, and 6 in the three fields). Access has no problem with cells/fields a1
and a2, but regardless of what values are in those cells, and regardless of
what value Excel shows in a3, Access always reports a3 to be 6, the value it
memorized the first time it linked to that table.

I've tried this lots of different ways and always get the same results.
Only if I add something new to some cell in the spreadsheet will Access take
a new look, and report Excel's current values in all cells. But then we just
have new memorized values and the problem begins again.

Thanks for your assistance.

 

[david epsom dot com dot au]

Excel stores both a value and a formula for each
cell. It does this so that it does not have to
recalculate the value for each cell. It only
re-calculates cells if it is told that the
source of a calculation has changed.

Access only links to the values in the cells. Access
does NOT link to the formulas.

Access does NOT recalculate cells at all. Only Excel
can recalculate cells.

After Excel has recalculated a cell, you have to SAVE
the file, or the old stored value will still be there.

You should not link to calculated cells, because they
won't be updated unless you open the spreadsheet, re-calculate,
and save the spread sheet.

If you want to get a calculated value from a spreadsheet,
you should use com automation to open a copy of excel,
load the spreadsheet, recalculate, and read the required
cell value.

(david)

 

[Guest]

Yes, I understand that, although it's not correct that you have to open the
spreadsheet and save it for Excel to do its recalculations. You can plug in
values to a linked table from Access, and the next time Excel is simply
opened it will automatically show those new values, and automatically show
the recalculations.

But as I said, I can delete the links to Excel after plugging in new values
to non-calculated cells, close Access, open Excel to confirm that it has
recalculated - then close Excel, reopen Access, relink the table - but Access
will not show the current calculated value in Excel (the one I confirmed when
I opened and closed Excel), instead Access plugs in the memorized value that
it originally found in the calculated cell(s). I can delete the link, open
and close Excel a dozen times, changing the values in all cells each time,
and when I open Access again and relink the table it will show me the correct
values in the non-calculated cells, but it still plugs the original memorized
values into the calculated cell(s). Access will only take a new look at the
calculated cells if I make a structural change in the spreadsheet (as simple
as assigning a value to some previously vacant cell). Then Access requeries
the whole spreadsheet and reports all the proper current values of all cells.

But anyway your com automation suggestion sounds like it holds
possibilities. I know nothing about what that is or how to do it. Any
pointers?

I appreciate your assistance.

W

 

[Guest]

Well, yes, although it doesn't really matter; Access still screws up
reporting Excel's values with its memorizing.

If I import a table from Excel, Access memorizes the values in the
calculated cells, but not the non-calculated cells. Then I can delete the
imported table and close Access; go open the sheet in Excel and plug in new
values, which causes Excel to recaluclate and put new values in the
calculated fields.

Then I close Excel, go back to Access and re-import (or re-link) the table.
Access will correctly show Excel's current values in all non-calculated cells
- but it plugs in its original memorized values into all calculated cells -
values that are now incorrect, and not the values Excel currently has in
those cells.

Thanks for taking the time to help.

W

 

[John Nurick]

Yes, I understand that, although it's not correct that you have to open the
spreadsheet and save it for Excel to do its recalculations. You can plug in
values to a linked table from Access, and the next time Excel is simply
opened it will automatically show those new values, and automatically show
the recalculations.

Yes - but unless you cause Excel to save the new calculated values to
disk the .wbk file will still contain the former values, and these are
what Access will find.

But as I said, I can delete the links to Excel after plugging in new values
to non-calculated cells, close Access, open Excel to confirm that it has
recalculated -

You have to save the workbook at this point.

 

[George Nicholson]

Access is not memorizing anything. It is reading the calculated values from
the last time the file was saved (calculated values won't change in a closed
file.)

When you open the Excel file, Escel automatically recalculates and you "see"
the new results. If you save the file at that point (which you don't
indicate you are doing), Access will then read those values. If you don't
save after recalculation, then Access will keep reading the old values
because that's what's in the file.

 

[Guest]

Actually, I don't have to do anything to save the Excel values. I don't have
to open Excel or save anything. I just plug the new values into the linked
table from inside Access, either delete the table or not, and close Access.
Then if I go open Excel the plugged-in values are automatically there, and
any calculated cells that are dependent on them have been recalculated. It
seems to me that if things didn't work this way, there wouldn't really be
much difference between importing and linking an Excel sheet.

I notice that you mentioned .wbk files. I've never used those and don't
know what they are; I've always used .xls. Do you think that could have
anything to do with why linked Excel files seem to behave differently for
each of us - or suggest any solution to my memory purge problem?

Thanks again

 

[Guest]

Weird, can this be true? You're saying that non-calculated values can be
plugged into an Excel sheet from a linked Access table; and that those will
be automatically saved in the Excel file, so when you open it up in Excel you
will see them. (this is true for me)

And when you open the Excel file and see the new values, Excel will
recalculate the calculated fields and show you the correct calculations, but
then doesn't save those calculated results, but keeps the old, erroneous,
results in its saved file?

So you might open and close an Excel file ten times and verify that each
time it shows in cells a1, a2, and a3 that 2*4=8; but all the while the Excel
file on the disk actually shows 2*4=6, because 6 was the calculated result
when the file was last saved yesterday and a2 was 3 instead of the present 4?

 

[George Nicholson]

but then doesn't save those calculated results, but keeps the old,

erroneous,
results in its saved file?

Well, saving those results is up to the user. As a rule, Excel doesn't save
squat unless the user tells it to (AutoSave/Autorecover aside).

But otherwise, I think you've got the idea now. Access is reading the
contents of the last saved file. Any changes you make via links to the
static data in that file will also be read but *not* any dependent
calculations because calculations only happen while the file is open in
Excel. They then have to be saved if you want those calculations available
to Access.

So you might open and close an Excel file ten times and verify that each
time it shows in cells a1, a2, and a3 that 2*4=8; but all the while the
Excel
file on the disk actually shows 2*4=6, because 6 was the calculated result
when the file was last saved yesterday and a2 was 3 instead of the present
4?

Yep. And do you get a "Do you want to save changes?" prompt when you close
that workbook? You may think that you didn't make any changes but Excel did
when it recalculated on opening (or before Save, if that option is turned
on). Answer Yes to the prompt or you'll continue to get yesterday's
calculation results in Access.

HTH,