VBA Connection Pooling

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.

Ramblings

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).

ConectPool1

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

Advertisements
  1. Leave a comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: