Where to release the ADO in a DLL

[xunxm]

in the ActiveX DLL, I add a module, in the module I add the sub_main, and create the ADODB.Connetion.
when my programs used the DLL first time, it will run the sub_main, but when I close the program, I want to set g_Conn = nothing, but I can't find the procedure to do, Does the program release it automatic?

[mendhak]

It does release the connections, but if you want to stay on the safe side, you can put that code in the class_terminate() event.

[xunxm]

if my DLL have 2 Class Module, all of the Class Module will the g_conn,if one of the Class Module use the class_terminate() event to release the g_conn, another one will wrong.

[mendhak]

The very fact that you have two g_conns in your DLL implies that they are private in scope to each module. There shouldn't be a problem when you close these in the class_terminate() event.

However, if it still scares you, you can create a procedure that closes everything for you, and call this procedure in your form's Query Unload event.

[xunxm]

in fact, In my program, I neet 3-5 class module, and my program maybe will have 10 users, this means, if they are open my program, maybe I create 30 - 50 connection.

[mendhak]

Dude, that shouldn't matter. For each user, you'll be having a separate instance of the class. Each object, upon termination will call the code to close the ADO connections. That's the basic concept behind OOPs!

[xunxm]

But I think if I create the instance, I have to open the connection, and I release the instance,It will close the connection, but somebody told me:I will waste time to wait it open, and I will waste time to wait it close!How can I do?I think maybe use the COM+ is better!

[Wokawidget]

COM+ is nothing to do with what you are trying top achieve. The time to open/close a connection is neglegable as the connections are pooled in memory.
Don't create the connection in Sub Main, just create the connection when u need it, and then destroy it as soon as u have finished with it.

Woak

[xunxm]

if I am not use COM+, client always open/close connection, is a good choose? the connection will pooled in memory?

[Wokawidget]

if I am not use COM+, client always open/close connection

What?
that makes no sense...

Woka

PS Stupid tags

[mendhak]

Your friend has obviously given you incorrect information. You can safely do as we've told you in this thread.

Trust us.

Sincerely,
Frog and Badger.

[Wokawidget]

Here is what I use in my database module:
I use the property DBConnection to get the connection object.
If I am using tranasctions then I use BeginTranascation before I get the DBConnection.
Hope it makes sense.

Woka

Option Explicit
 
Private Const CLASS_NAME As String = "modDatabase"
 
Private Const DB_CONN As String = "Provider=SQLOLEDB.1;Svr Security Info=False;User ID=MBELots;Password = MBELots;Initial Catalog=MBELots;Data Source=MESDEV"
 
Private madoConn        As Connection
Public gstrUserUID      As String
 
Private mlngCount       As Long
 
Private Property Get DBConnString(ByVal pblnAuthenticationReq As Boolean) As String
On Error GoTo ErrHandler
    If Len(gstrUserUID) = 0 And pblnAuthenticationReq Then
        Err.Raise vbObjectError, CLASS_NAME, "Server authentication failed, user not logged in."
    End If
    DBConnString = DB_CONN
    Exit Property
ErrHandler:
    HandleError CLASS_NAME, "DBConnString", Err
End Property
 
Public Property Get DBConnection(ByVal pblnAuthenticationReq As Boolean) As Connection
Dim adoConn     As Connection
On Error GoTo ErrHandler
    If madoConn Is Nothing Then
        Set adoConn = New Connection
        adoConn.CursorLocation = adUseClient
        adoConn.Open DBConnString(pblnAuthenticationReq)
    Else
        Set adoConn = madoConn
    End If
    Set DBConnection = adoConn
    Set adoConn = Nothing
    Exit Property
ErrHandler:
    Set adoConn = Nothing
    HandleError CLASS_NAME, "DBConnection", Err
End Property
 
Public Property Get DateTimeSnapshot() As Date
Static dteDate     As Date
    If dteDate = 0 Then
        dteDate = Now
    End If
    DateTimeSnapshot = dteDate
End Property
 
Public Property Get TransactionUID() As String
Static strUID   As String
Dim objFuncs    As VBTools.GeneralFunctions
On Error GoTo ErrHandler
    If Len(strUID) = 0 Then
        Set objFuncs = New VBTools.GeneralFunctions
        strUID = objFuncs.CreateUID
        Set objFuncs = Nothing
    End If
    TransactionUID = strUID
    Exit Property
ErrHandler:
    Set objFuncs = Nothing
    HandleError CLASS_NAME, "TransactionUID", Err
End Property
 
Public Property Get TransactionIndex() As Long
Static lngIndex     As Long
    lngIndex = lngIndex + 1
    TransactionIndex = lngIndex
End Property
 
Public Sub BeginTransaction()
On Error GoTo ErrHandler
    If mlngCount = 0 Then
        Set madoConn = New Connection
        madoConn.CursorLocation = adUseClient
        madoConn.Open DBConnString(True)
        madoConn.BeginTrans
    End If
    mlngCount = mlngCount + 1
    Exit Sub
ErrHandler:
    HandleError CLASS_NAME, "BeginTransaction", Err
End Sub
 
Public Sub CommitTransaction()
On Error GoTo ErrHandler
    If mlngCount = 0 Then
        Err.Raise 1
    End If
    mlngCount = mlngCount - 1
    If mlngCount = 0 Then
        madoConn.CommitTrans
        madoConn.Close
        Set madoConn = Nothing
    End If
    Exit Sub
ErrHandler:
    HandleError CLASS_NAME, "CommitTransaction", Err
End Sub
 
Public Sub RollbackTransaction()
    If mlngCount = 0 Then
        Err.Raise 1
    End If
    mlngCount = mlngCount - 1
    If mlngCount = 0 Then
        madoConn.RollbackTrans
        madoConn.Close
        Set madoConn = Nothing
    End If
End Sub
 
Public Sub AddEventLog(ByVal pstrItemUID As String, ByVal pstrItem As String, ByVal pstrAction As String)
Dim objEvent    As EventLogSvr
On Error GoTo ErrHandler
    Set objEvent = New EventLogSvr
    objEvent.AddLog gstrUserUID, pstrItemUID, pstrItem, pstrAction
    Set objEvent = Nothing
    Exit Sub
ErrHandler:
    Set objEvent = Nothing
    HandleError CLASS_NAME, "AddEventLog", Err
End Sub

[xunxm]

Oh, I am sorry, i made a mistake.My english is poor, so I always make mistake, I am so sorry!

Originally posted by xunxm
if I am not use COM+, client always open/close connection, is a good choose? the connection will pooled in memory?

I want to say:If I create the ActiveX DLL, used in client computer, not used in server(I read some book told me, server can create the Connection Pool,when a lot user use the connection, server can reload the connection quickly),Does Client can create the pool like server, and load it quickly.

in the DLL, I add some method like this.

Public Sub DeleteRecord()
   Dim Conn as New ADODB.Connection
   Dim strSQL as String
   ..........
   Set Conn = Nothing
End Sub
 
Public Sub GetRecord() as Recordset
   Dim Conn as New ADODB.Connection
   .....
   Set Conn = Nothing
End Sub

Can I neglect the time of "Dim conn......"?
thanks a lot.
thank mendhak and Wokawidget, thank you very much.

[Wokawidget]

Yes, I think it takes something like 20ms to create a connection. Neglegable really.
What I would do, is instead of creating the connection in each sub I would have:

'In a module
Option Explicit
 
Private Const CONN_STRING As String = "Your DB Connection String"
 
Public Function DBConnection() As ADODB.Connection
Dim adoConn   As ADODB.Connection
   Set adoConn = New ADODB.Connection
   With adoConn
      .CursorLocation = adUseClient
      .Open CONN_STRING
   End With
   Set DBConnection = adoConn
   Set adoConn = Nothing
End Function

Then in your subs use:

Public Sub DeleteRecord()
Dim adoConn   As ADODB.Connection
   Set adoConn = DBConnection
   'Blah Blah Blah
End Sub

Or for your select code you can use:

Public Sub FetchRecord()
Dim adoRec   As ADODB.Recordset
   Set adoRec = New Recordset
   adoRec.Open "SELECT * FROM Table", DBConnection
   'Blah Blah Blah

For your fetch function above you don't even need to create a connection. If you make the CONN_STRING PUBLIC instead of PRIVATE then you can do:

Public Sub FetchRecord()
Dim adoRec   As ADODB.Recordset
   Set adoRec = New Recordset
   adoRec.Open "SELECT * FROM Table", CONN_STRING
   'Blah Blah Blah

Hope that helps,

Woka

[yrwyddfa]

Public Sub SQLOpen(ByRef oCn As ADODB.Connection)
 
    On Error GoTo ERR_GetConnectionString
    
    Const REGISTRY_LOCATION As String = "SOFTWARE\MyCompany\DataAccess"
    Const DEFAULT_CONNECTION_STRING As String = "Provider=%1;Password=%2;User ID=%3;Data Source=%4"
    
    Dim oSPM As SharedPropertyGroupManager
    Dim fAlreadyExists As Boolean
    Dim oGroup As SharedPropertyGroup
    Dim oSQLCnString As SharedProperty
    Dim sProvider As String
    Dim sServer As String
    Dim sDatabase As String
    Dim sUsername As String
    Dim sPassword As String
    Dim SQLConnectionString As String
 
    '***************************************************************
    '* Get the shared property (create it if it doesn't exist . . .
    '***************************************************************
    Set oSPM = New SharedPropertyGroupManager
    Set oGroup = oSPM.CreatePropertyGroup("ConnectionStrings", LockMethod, Process, fAlreadyExists)
    Set oSQLCnString = oGroup.CreateProperty("SQLConnectionString", fAlreadyExists)
    
    '*****************************************************************************************
    '* If we don't have this shared property, then get the information from the registry . . .
    '*****************************************************************************************
    If Not fAlreadyExists Then
    
        Call Registry.GetValue(HKEY_LOCAL_MACHINE, REGISTRY_LOCATION, "SQLProvider", sProvider)
        Call Registry.GetValue(HKEY_LOCAL_MACHINE, REGISTRY_LOCATION, "SQLDatabase", sDatabase)
        Call Registry.GetValue(HKEY_LOCAL_MACHINE, REGISTRY_LOCATION, "SQLServer", sServer)
        Call Registry.GetValue(HKEY_LOCAL_MACHINE, REGISTRY_LOCATION, "SQLUser", sUsername)
        Call Registry.GetValue(HKEY_LOCAL_MACHINE, REGISTRY_LOCATION, "SQLPassword", sPassword)
        
        '********************************************
        '* Replace tokens with the correct data . . .
        '********************************************
        SQLConnectionString = DEFAULT_CONNECTION_STRING
        SQLConnectionString = Replace(SQLConnectionString, "%1", sProvider)
        SQLConnectionString = Replace(SQLConnectionString, "%2", sPassword)
        SQLConnectionString = Replace(SQLConnectionString, "%3", sUsername)
        SQLConnectionString = Replace(SQLConnectionString, "%4", sServer)
        
        oSQLCnString.Value = SQLConnectionString
 
    Else
        SQLConnectionString = oSQLCnString.Value
    End If
 
    Set oSQLCnString = Nothing
    Set oGroup = Nothing
    Set oSPM = Nothing
    
    '*******************************************************
    '* Release existing connection, then recreate it . . .
    '*******************************************************
    SQLClose oCn
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = SQLConnectionString
    oCn.Open
    
    Exit Sub
    
ERR_GetConnectionString:
 
    SQLClose oCn
    
    Set oSQLCnString = Nothing
    Set oGroup = Nothing
    Set oSPM = Nothing
    
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    
End Sub
 
Public Function SQLClose(oCn As ADODB.Connection)
 
    If Not oCn Is Nothing Then
        If oCn.State = adStateOpen Then
            oCn.Close
        End If
        Set oCn = Nothing
    End If
    
End Function

I would then use this format in a function:

public AFunction() as boolean
 
   Dim oCn as ADODB.Connection
 
   SQLOpen oCn
   oCn.Execute"Do Something Nice"
   SQLClose oCn
 
   'do something else.
 
end function

Works a treat on a COM+ server. You will need to change the registry access, and add the relevant COM+ stuff.

[xunxm]

Thanks!!!!