Hi there

I'd be most grateful for pointers in the right direction, on how to optimise
my Excel VBA program, that goes back and forth to an Access DB. At the
moment, I don't feel I've done enough with the instantiating and/or closing
of objects - particularly Connection, Catalog and Command Objects.

All the data my Excel program requires, sits in an Access DB. I have a
connection object created, in readiness, whenever the spreadsheet is open. I
close this connection before the workbook closes (using Excel's inherent
BeforeClose Event).

However, after I create an Excel file, from this program, I tried, using
explorer, to delete the created file. I got an error prompt saying:

"Cannot delete xfile. Access is denied. Make sure the disk is not full or
write-protected and that the file is not currently in use."

This error arose despite the fact that the file was not open. It led me to
think that I might still have an object open. However, when I shut Excel down
completely, I was able to delete the file.

What must I pay attention with all the objects I have in my program? If I
use a Command and Catalog object to return a rst, can I close the Command and
Catalog objects immediately after the rst has been returned?

Is it practical to have a Connection open, in expectation of fetching
another rst that the program might require, or should I open one only when
needed?

Any pointers on Dos and Don'ts when working with objects will be greatly
appreciated.

Thanks
ade

Hi, please follow these simple rule-of-thumb.
1. Anything that was opened should be closed in the proper order.
2. Close the inner-most objects first and then progress towards outer-most
objects.
The above should save most of the trouble, if not all.

"adewole" wrote:

Hi there

I'd be most grateful for pointers in the right direction, on how to optimise
my Excel VBA program, that goes back and forth to an Access DB. At the
moment, I don't feel I've done enough with the instantiating and/or closing
of objects - particularly Connection, Catalog and Command Objects.

All the data my Excel program requires, sits in an Access DB. I have a
connection object created, in readiness, whenever the spreadsheet is open. I
close this connection before the workbook closes (using Excel's inherent
BeforeClose Event).

However, after I create an Excel file, from this program, I tried, using
explorer, to delete the created file. I got an error prompt saying:

"Cannot delete xfile. Access is denied. Make sure the disk is not full or
write-protected and that the file is not currently in use."

This error arose despite the fact that the file was not open. It led me to
think that I might still have an object open. However, when I shut Excel down
completely, I was able to delete the file.

What must I pay attention with all the objects I have in my program? If I
use a Command and Catalog object to return a rst, can I close the Command and
Catalog objects immediately after the rst has been returned?

Is it practical to have a Connection open, in expectation of fetching
another rst that the program might require, or should I open one only when
needed?

Any pointers on Dos and Don'ts when working with objects will be greatly
appreciated.

Thanks
ade