-1

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

EuanM28
  • 258
  • 3
  • 14
Andre Gotlieb
  • 27
  • 1
  • 7

1 Answers1

0

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks for your answer however MS documentation menstions: "If two or more Command objects are executed on the same connection and either Command object is a stored procedure with output parameters, an error occurs. To execute each Command object, use separate connections or disconnect all other Command objects from the connection." – Andre Gotlieb Jan 01 '23 at 14:20
  • Besides of the form's own rowsources (tables / views), my form's modules use mostly queries for lookups etc... I intend to move them to stored procedures for improving performance instead of Pass through queries, wouldn't that be an element to take into account ? – Andre Gotlieb Jan 01 '23 at 14:37
  • The connection can't be used for two commands simultaneously. But your application is single-threaded, with all user interactions posting messages to the Window and being processed sequentially by the UI thread. Stored Procedures and Pass-Through queries have nearly-identical performance. Stored procedures get the TSQL out of your front-end application which can be a benefit. – David Browne - Microsoft Jan 01 '23 at 15:54