VBA ADO Connection to SQL server

Question

I am upgrading an MS Access application to work with SQL server as the back-end.

Wrote and test ran a class for establishing/managing a connection to the server and retrieving a recordset with the requested data from the server (table, view, procedure or query), besides the connection management the retrieved recordset gets available as a property of the class.

However, some of my form's procedures open more than one recordset/table while executing.

I am wondering what's best:

• opening a separate instance of my connectclass for each needed recordset i.e. a separate connection per opened recordset
• or modifying my ConnectClass to handle multiple recordsets/commands on the same connection i.e. within the same class.

Seems to me one class instance per recordset is the cleanest (with less risk of interference and bugs) but at the expense of one open connection per open recordset and some more memory usage, are there other consequences I need to take into account? What's your advice? Andre

Answer 1

modifying my ConnectClass to handle multiple recordsets/commands on the same connection i.e. within the same class.

This. Generally you want to open a connection, perform a single "unit of work", which may be any number of separate queries and then close the connection.

In a web application a "unit of work" is typically scoped to a single web request, but in a desktop client application (like Access) the connection can be scoped to either a single method, or the lifetime of a UI element, like a form.