Using ADODB Efficiently

[Simon]

Hi,

I have an excel worksheet where a number of cells use a UDF to retrieve data
from an access database, the UDF uses ADODB to communicate to the database.
When the spreadsheet updates the formulas it takes quite a while to calculate
the cells I think its because for each cell that contain the UDF, the UDF
creates a new connection to the database is there a more efficient way of
doing this maybe re-using the one connection?

Thanks.
Simon

 

[Bob Phillips]

Save the connection as a static object variable in the UDF and test if it is
nothing or not (set connection if so), or as a module level variable and do
likewise.