Writing a Connection Class 1

[ALSav]

Hi,

I have a project with a about 16 forms. On each form I access a recordset from an Access database via ado so the Form_load of each form contains

Set cn = New ADODB.Connection
cn.ConnectionString = "DSN=MyDatabase;"
cn.Open

Is there any way I can write this just once and establish a single connection to the database which will provide for all the recordsets required for the various forms while the program runs.

I did read somewhere that the connections can be written into a class and then made into a DLL which can be called each time you a connection is required.
Is this correct? Will it improve performance because at the moment it is painfully slow.

Any help much appreciated

AL

 

[chiph]

Here's one more incentive towards writing a connection class: every time one of your forms opens it's making a connection to the database. This is OK for Access, but in SQL Server connections are licensed (cost $$$$).

The easy way to do this is not through a class, but through a .bas module. Write a public function like this:
[tt]
Private m_oConn as ADODB.Connection

Public Function GetConnection() as ADODB.Connection
[tab]If m_oConn Is Nothing Then
[tab][tab]Set m_oConn = New ADODB.Connection
[tab][tab]'
[tab][tab]' set other connection properties here
[tab][tab]'
[tab]End If
[tab]Set GetConnection = m_oConn
End Function
[/tt]
Call this function whenever you need a connection object:
[tt]
[tab]On Error Goto GotAnError
[tab]Dim MyConnection as ADODB.Connection

[tab]Set MyConnection = GetConnection()
[tab]' Do ado work with the connection

[tab]goto Cleanup

GotAnError:
[tab]' Log your error here
[tab]' Fall through into cleanup
Cleanup:
[tab]Set MyConnection = Nothing
[/tt]

You'll need another function to call when you app is closing to free the connection. You'll want to make sure your calls to GetConnection and setting the local connection variable to Nothing are exactly balanced. You do this with the Cleanup routine above. In .NET you would do this with a try..catch..finally block.

Chip H.

 

[petermeachem]

Note that Chip says close 'the' connection when the app is closed.
Don't open a connection per form. Open it once when the programme starts and close it when it stops. Peter Meachem
peter@accuflight.com

 

[ALSav]

Many thanks,I was sure that out there somewhere was a better way of handling this than the way I was doing it.
AL

 

[ALSav]

One more question

If I write

Set cn = GetConnection()

in the Form_Load event of each form that requires a connection to the database and

Set cn = Nothing

in the Form_Unload event, does that amount to a new connection for each form or is that OK?

AL

 

[petermeachem]

What I do is to open the connection when the Programme loads and close it when the programme closes. Forms and whatever just use that connection to create recordset etc. Just open the connection once in the life of the programme. Peter Meachem
peter@accuflight.com

 

[chiph]

Al -

That's OK, because all you're doing is getting a reference to the connection object, not creating a new connection object. This is a tough concept for people new to OOP to grasp. Here's an example:

Assume that the number "5" is a first-class object (your connection object).
[tab][tt]Set I = New 5
[tab]Set J = I[/tt]
After these two lines, there's still only one "5" object -- variables I & J both have references to "5". When both I is set to nothing:
[tab][tt]Set I = Nothing[/tt]
"5" still exists because J also has a reference to it. Setting J to nothing:
[tab][tt]Set J = Nothing[/tt]
will finally dispose of the "5" object since all references to it have been freed.

In the case of the sample code I posted earlier, the connection object will hang around until you call the "FreeConnection" function you wrote because the module-level variable m_oConn still has a reference to it. This is also the reason why it's so important to use the Cleanup method I showed, in that if one of the program's variables still has a reference to the connection object, then the object will never be freed, even if you call the FreeConnection function!

Hope this helps.
Chip H.

 

[DavidAWH]

Al
if you put your connection code in a Sub Main() routine on the .bas module you can then change the projects properties so that Sub Main is the startup object ( Project on the menu then Project1 Properties

Public Sub Main()
ADO Connection code
frmMain.Load
frmMain.Show
End Sub

where frmMain was the original startup form