Thanks Viv.

>An OLEDB connection in itself shouldn't be the cause of the exception you are seeing - although it may be related to something in Excel.

Nothing to do with Excel!!! :). It's an Access accdb that is connected.

On the subject of connections - I'm surprised that you find that maintaining an open connection to be noticeably faster than simply keeping a connection for the minimum required time.


Well, as we say in French, facts are stronger than the lord Mayor of London. When I pass the connection rather than let the ta open it each time processing is cut dramatically(something that takes 20 sec only takes 3sec). (Now this might be a ta issue of course, maybe is should design away from the generator, heard this before in these pages).

>Bear in mind that .NET automatically maintains a connection pool. When you close a connection you are not destroying it just returning it to the pool - a subsequent New() with the same connection string should just grab the already existiing connection. Also the OleDBConnection class implements IDisposable so the recommended approach is simply something like this:

Using connection As New OleDbConnection(connectionString)
>        connection.Open()
>        'Do whatever
> End Using

Wrapping the operation in a Using, which creates an implicit Try/Catch/Finally block, will ensure that the OleDBConnection is *always* closed (ie. in practice, returned to the pool) even if an unhandled exception occurs within the block.
>

the ta's open the connection. In fact from what I read in generated code, the ta tests if the connection is open and it opens only if it is not. At closing time, it does not close the connection if it did not open it.