ADO objects as global vars 1

[BrianB]

Hello,

I am building a VB6 application (replacing an Access 97 app) that connects to a SQL 7 DB via ODBC using ADO connection and recordset objects.

At present, I am declaring connection and recordset objects at the procedure level. My SaveRecord sub declares, initializes, opens, closes, and destroys its own connection and recordset objects. My most complex form loads and saves data to seven related data tables, so this is a lot of recordsets to open.

I am considering moving these objects to the module level or even the global level. Is there any reason I would not want to do this? I’m not worried about runtime errors because I intend to write “Does objCN exist?” and “Is objCN open?” code to test for the presence and status of these objects before using them.

My main uncertainties relate to performance. Does making ADODB objects global vars hurt performance?

Any comments or suggestions of resources are appreciated.

-Brian


_________________________________


Brian Begy
Developer
Chicago Data Solutions

http://www.chicagodatasolutions.com

tel: 773 350 8389
fax: 773 262 5163
email: brian@chicagodatasolutions.com
_________________________________

 

[culshaja]

I explicitly create a Connection object and declare this either at module or global level. The connectrion is what takes longest to initialise so by reusing the connection object you save a lot of time. You do however use a lot of resources on the server this way as the server ha to
maintain your connection information.

If you have a recordset where the data is heavily reused you could store that at the module/gloabl level but remember to use CursorLocation = adClient as this means that the recordset does not chew up resources on the server. It does, however, take up space on the client so you have to be aware of what you are doing, or you are going to impact performance.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[dbleyl]

Hi,

I think global variables increase performance at the design's expense. You should also consider if these recordsets are similar and if they would benefit from different access methods. Try using stored procedures and views to run on the server. It will improve performance and make client code cleaner.
Also consider using SQLOLEDB to connect to SQL Server.

-Don