DAO - Accessing open Excel file issue

  • Thread starter Thread starter Phillip Fries
  • Start date Start date
P

Phillip Fries

[I can find nothing that addresses this issue on the internet] When I use
DAO code to access a closed Excel file to retrieve data from it, the code
runs fine, i.e. the data is retrieved without opening the Excel file that
contains the data. But, if the Excel file is currently in use by another
user when DAO attempts to access it, the Excel file containing the data is
automatically opened (visually) as read-only, albeit the data is then
retrieved apparently from the opened Excel file. Additionally, once the
read-only Excel data file is manually closed, the Excel VBA editor shows
that the Excel data file is still in memory, i.e. the user interface to the
Excel data file gets closed, but the file appears to remain in memory and
is shown in the Excel VBA editor. Also, running the DAO code against the
Excel data file multiple times in one session results in multiple copies of
the Excel data file being loaded into memory and shown in the VBA editor.
One must close the Excel file containing the DAO code and reopen it to
release the Excel data files from memory.

Since memory for the Excel data file appears not to be released, I'm
guessing that this is probably a problem/bug in DAO 3.6? Which would
indicate that I can't do much except [1] try to avoid running the DAO code
against an Excel data file that is currently open by another user or [2]
find some way to open the file without invoking the user interface and then
execute some code that releases the memory allocated to the file? The first
choice is probably the most realistic? I'm open to any suggestions because
this quirk pretty much makes DAO access to Excel files unusable.
 
I am more familiar with ADO, for which there is a known memory leak
bug:

Microsoft Knowledge Base Article - 319998
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998

I know of similar problems with ODBC.

Bottom line: it isn't a good idea to query an open workbook.

However, this may not be the show-stopper you think it is. You can
always make a copy (on disk) of the open workbook and query this
(closed) copy. Won't work if you are trying to change the original
workbook using SQL (UPDATE, INSERT INTO, CREATE TABLE etc) of course!
 
Back
Top