T
tchid2
Hello,
I have an Excel 2003 workbook VBA project that creates an Access 2003 .mdb
when the workbook opens (via DAO), and later deletes the .mdb when the
workbook closes. Subsequent code creates and appends a new DAO.TableDef
object to the database. The TableDef is created as a linked table, linking
to data that is in the Excel worbook.
(Admittedly, this is somewhat of a circular arrangement, but required by the
project's needs. The Excel data needs to be reshaped and reported upon in
Excel. Using DAO and multiple run-time generated SQL statements, I can
facilitate this in mere seconds, while an Excel-based formulaic approach to
the report(s) take several (read 7-10) minutes to calculate. There will be
multiple end-users so my solution needs to be quick and robust.)
My problem is when the workbook closes, and I set all my DAO objects to
Nothing and delete the temp database file, the VBAProject hangs around in the
Excel VBIDE. Normally, any VBA library associated with an .xls file goes
away when the .xls closes. Not in this case. Also, EXCEL.EXE is still
running in Task Manager.
I've been able to assertain (via commenting- and uncommenting-out code) that
problem lies with the code that creates the linked table, which as I
mentioned, references the Excel file data.
How do I make the VBA library go away when it should, or better put, what am
I doing that is causing it to stick around? Again, all object references are
set to Nothing in the Workbook_Close Event.
Any help or comment is greatly appreciated.
Thanks in advance,
tchid2
I have an Excel 2003 workbook VBA project that creates an Access 2003 .mdb
when the workbook opens (via DAO), and later deletes the .mdb when the
workbook closes. Subsequent code creates and appends a new DAO.TableDef
object to the database. The TableDef is created as a linked table, linking
to data that is in the Excel worbook.
(Admittedly, this is somewhat of a circular arrangement, but required by the
project's needs. The Excel data needs to be reshaped and reported upon in
Excel. Using DAO and multiple run-time generated SQL statements, I can
facilitate this in mere seconds, while an Excel-based formulaic approach to
the report(s) take several (read 7-10) minutes to calculate. There will be
multiple end-users so my solution needs to be quick and robust.)
My problem is when the workbook closes, and I set all my DAO objects to
Nothing and delete the temp database file, the VBAProject hangs around in the
Excel VBIDE. Normally, any VBA library associated with an .xls file goes
away when the .xls closes. Not in this case. Also, EXCEL.EXE is still
running in Task Manager.
I've been able to assertain (via commenting- and uncommenting-out code) that
problem lies with the code that creates the linked table, which as I
mentioned, references the Excel file data.
How do I make the VBA library go away when it should, or better put, what am
I doing that is causing it to stick around? Again, all object references are
set to Nothing in the Workbook_Close Event.
Any help or comment is greatly appreciated.
Thanks in advance,
tchid2