Single Connection Object

BACKGROUND:
I have an existing VB application that is a FAT client with all the GUI, Business and Data logic inter-mixed. This application is a simple client server application (the server is SQL 2000).

I want to break up the application so that the GUI code is seperate from the Business Logic which is seperate from the Data Access logic.

I have created an ActiveX DLL to house all the data logic. This DLL will exist on the client.


QUESTION:
I would like to create just one connection object when the client is started and use this conneciton object for all database calls.

Dim objCon As ADODB.Connection

In other words, I do not want to create a connection object each time I call one of the data service objects.

All of the data service object would make use of this one connection object.

I have not been able to figure out a way to make this work otherr than declaring a connection object in the main program and passing it into the Data Access object calls.


Users.GetUsersBySite(objCon,SiteID)

I would like to avoid having to pass in the connection object to every method I call.

Any ideas?

I have looked into making a Persistable cConnection class but I have not been able to get this to work.

Thanks.

This is the exact thing that MTS and COM+ are for. Look into those, they support connection pooling.

hellswraith,

Thanks for the response but I do not want to implement my tiers on the client.

I want to break apart the application into GUI, Business and Data layers but implement all those layers on the client.

To my understanding MTS, COM+, etc.. only come into play when you want to implement the Business and/or Data layers on machines other than the client.

Sorry, thought you were putting your data objects on another machine.


Why don't you create a data component that creates a connection and keeps it going. Then any thing that accesses the database will send an sql statement to that object that is holding the connection, and then the object will pass on the sql statement to be processed, then return a recordset result.

or at least something like that...