Interesting read about Connection Pooling in VBA and SQL Server. I knew circular references are bad juju, but I didn’t know about the “App” property of a connection, or that `conn.Close` doesn’t actually sever the connection. The connection isn’t really severed until the `ADODB.Connection` is garbage collected. Highly recommended reading here.
I was messing around with SQL Server Profiler recently and thought to myself “this would be a good way to illustrate VBA connection pooling to people”. Many books recommend using connection pooling when building apps that involve more than a trivial amount of SQL database work. Most of those books describe connection pooling and how to perform it, but none backup their assertions with actual proof. Of course, they are correct in what they are saying and connection pooling is a very useful paradigm to implement. This blog post, however, goes one step further and shows you what is happening ‘on the server side’.
The screenshot below you’ll see I’ve got 3 apps open: SQL Server Profiler (top), MS Access VBA IDE (middle) and Task Manager (bottom).
You’ll see I’ve already started a trace on Profiler and also stepped into the VBA procedure ‘TestConnection’. I have instantiated and instance of…
View original post 1,030 more words
Christopher J. McClellan 