primitive db connection manager

[Ed Lampman]

Compile the code below as an ActiveX DLL named CNLib.CN
(You may need to change the connection string to match
your provider, etc)

Dim cn as ADODB.Connection

Private Sub Class_Initialize()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;" & _
"Password=;Persist Security Info=True;" &_
"UserID=sa;Initial Catalog=YourDB;" &_
"Data Source=YOUR_SERVER"
End Sub

Private Sub Class_Terminate()
cn.Close
set cn = Nothing
End Sub

Public Function GetConnection() As ADODB.Connection
Set GetConnection = cn
End Function


Example usage in a VB form

Dim ProvideCN As CNLib.CN

Private Sub Form_Load()
Set ProvideCN = New CNLib.CN
End Sub

Hope this helps....

[timekiller]

Hey, thanks Ed. Let me give this a try. But it seems like it will create a new connection with each New.

I'll check and see ...

[Ed Lampman]

I got the code from the October 2001 issue of Visual Studio magazine (formerly Visual Basic Programmers Journal). The article says that the DLL will take advantage of ADO and OLEDB's built-in connection pooling.

The entire code is attached if you want it. I forgot to tell you to set the CNLib project's property-threading model to be apartment-threaded.

[timekiller]

Very nice, Ed. It does the trick for me. Thank you very much for following up.

[timekiller]

Well, I've tried to implement this and it works in a form or in situations where the instance of the object is globally available and not being passed around as a parameter.

I'm trying to use it from 2 DLLs and a GUI and would like to use this one connection from all three. So, I either have to pass the object to each DLL back and forth or create new instances of the provider which will also create a new connection object (not what I want).

To test, just create 2 simple DLLs and a GUI. The GUI kick start the call to DLL1 that gets a recordset, then from DLL1 call DLL2 for another recordset and compare the differences between the 2 recordsets while the whole time in DLL1. When done, return back to GUI.

I'm getting 2 distinct instances of the provider (providerCN). I need the GUI and 2 DLLs to use the same instance of providerCN.

Hope this makes sense. Thanks.