MDI recordsets management VERY VERY URGENT!!!!!! 3

[WETFOX]

Hi! I already finished the coding and tested my newly created application but I get stuck on this one problem:

" recordsets management under MDI application with many child forms using the same recordset name connected to same/different table and sometimes stated differently "

THIS IS REALLY VERY URGENT ....HELP ME.!!!

I really need some practical and enlightening advice on how to manage recordsets opened in different child forms under MDI App.
(I want here clean management of recordsets and connection place under a single location)


What I usually do is create a module and declare in it a connection and recordsets so that all forms can refer into it. Then, in my MAIN FORM, I set and instantiate them.

When I need a a certain recordset, I just Open a table for it, for example, in childform1 so that a childform1 has recordset1 connected to a table1.

Then I created a second child form and use also the same recordset1 but connected to a different table (sometimes with the same table as childform1 but with different statement)

My problem goes like thisI know u can guess what it is)!

When I run the MDI App., and call childform1, recordset1 fires...so no problem about this. Then, without closing childform1, I call childform2 with the same recordset1 in it connected or stated differently from recordset1 in childform1.....and there the problem goes...

" recordset1 is still open, so I can't open again unless
I close it."

But closing it will affect my databound controls in childform1 so I don't want to close it. I don't want to use many instances of recordsets and clones. The same thing happens when I close recordset1 in childform2...childform1 will be affected!!!!

PLEASE GIVE ME SOME SUREFIRE TECHNIQUES ON THIS!!!!!!!!!

ERA
era@nittsu.com.ph
era@digitelone.com

 

[vb5prgrmr]

An easy way (and the only I can think of) is to declare/create/destroy a recordset object in each child form in the various form events load/unload. That way you will never have to worry about where or who opened the single recordset last. This idea would also require very little code changes in your module. Basically you would have a recordset in the form and when you normally call your getrecordset function it would get what you want and pass it back into the forms recordset, or even a clone if you want.

Good Luck

 

[WETFOX]

This means that if I have 20 childforms, do I have to create 20 unique recordsets name so that they won't have conflicts and scramble when 2 childforms simultaneously opened?

ERA
era@nittsu.com.ph
era@digitelone.com

 

[CCLINT]

In addition to vb5prgrmr suggestion of letting each form create/destroy it's own recordset object, you can use the following global method to have the form request a new recordset off of a global connection object (the connection object can be global and left opened, or can be opened only for the duration of opening the recordset, and closed closed afterwards. This requires disconnecting the recordset from the connection, an some sort of batch updating - adLockBatchOptimistic and rs.UpdateBatch).
The main thing is to use ONE connection object per DB, but avoid REUSING recordset objects unless they are closed AND destroyed.

In Form1, add a datagrid (DataGrid1):
[blue]

Option Explicit
Private WithEvents m_rsOrders As ADODB.Recordset
Private Sub Form_Load()
    Dim sqlString As String
    sqlString = "SELECT * FROM SomeTable"
    Set m_rsOrders = GetRecordset(sqlString)
    Set DataGrid1.DataSource = m_rsOrders
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not m_rsOrders Is Nothing Then
    If m_rsOrders.State <> adStateClosed Then m_rsOrders.Close
End If
End Sub

[black]
In Public Module1:
[blue]

Option Explicit
Public Conn As ADODB.Connection

Public OpenConnection()
    Set Conn = New ADODB.Connection
    With Conn 
        .CursorLocation = adUseServer
        .Mode = adModeShareDenyNone
        .Provider = YourProvider
        .Properties("OLE DB Services") = -1
        .Properties("User ID") = "Admin"
        .Open MyDatabasePathAndName
    End With
End Sub

Public Function GetRecordset(sqlString As String, Optional RunAsync As Boolean = False) As ADODB.Recordset
    Dim rsLocal         As ADODB.Recordset
    Dim Options         As Long
    Dim PropertyItem    As Variant
    If Not CheckConnection() Then
        MsgBox "Cannot Connect"
        Exit Function
    End If
    Set rsLocal = New ADODB.Recordset
    With rsLocal
        .Source = sqlString
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic 'adLockBatchOptimistic 
        Set .ActiveConnection = Module1.conn
        Options = adCmdText
        If RunAsync Then
            Options = Options Or adAsyncFetchNonBlocking
            .Properties("Background Fetch Size") = 100
        End If
        .Open Options:=Options
        
        'Disconnect is desired. Reconnect for (Batch)Updating
        'Set .ActiveConnection = Nothing

        Set GetRecordset = rsLocal
        Set rsLocal = Nothing
    End With
End Function
Public Sub Main()
    OpenConnection
End Sub

Public Function CheckConnection() As Boolean
    If ConnState = adStateClosed Then Conn.Open

    'Here you should rather use the ConnectionComplete event
    CheckConnection = ConnState = adStateOpen
End Sub

Public Sub MainClose()
    'Called when the MDI form Unloads
    If Not Conn Is Nothing Then
        If Conn.State <> adClose The Conn.Close
    End If
    Set Conn = Nothing
End Sub

[black]
The Sub Main is called when the project opens and establishes connection to the db.
You might want to put the OpenConnection in a class module, or the main MDI form, and declare the connection there also using WithEvents.
Then you will have connection events available to you.

Please note that this code doesn't include error handling or accurate verification that the connection succedded (use the ConnectionComplete event for this), and is just an outline to get you started...

 

[shaileng]

Yes, I agree to what vb5prgrmr say. You will have to create local copies of the given recordset in order to solve the problem. You will have to destroy it while closing the form. One more suggestion you can do this either by assigning the recordset to local copy or even you can create a clone so as if you dont want to loose the record pointers.

 

[vb5prgrmr]

And as a further explanation of what I think your second question is ... no you will not need to create n number of unique names for each forms recordset. Each form can have the same object name for their recordsets. This is a matter of scope of the objects declared in each form. i.e. ...

in Form1 ...
[tt]
Option Explicit
Private WithEvents rs As ADODB.Recordset
[/tt]

and in FormN
[tt]
Option Explicit
Private WithEvents rs As ADODB.Recordset
[/tt]

The scope of each recordset object is private to each form so there will not be any problems between Form1's recordset and Form2's recorset object. However!, IF you have two recordsets in different forms that are from the same table or they have some of the same fields and you alter the underlying data in say Form1 and then goto Form2 (Form2 was shown before Form1). Form2 will not show the modified data unless you use the refresh method or do a requery on the recordset (perhaps the activate event might be a good place for this action).

Good Luck

 

[WETFOX]

I'll try your advice guys but before that, I just want you to confirm to me that the procedures I made is not practical and should be changed: (here it is again)BTW My application is standalone.


I created a module (.BAS) and declare the following in general decl:

Public cnLeave As Connection '(I always left it open throughout the entire project and just discarded it when my App exits)

Public rsAdmin As Recordset
Public rsBranch As Recordset
Public rsDepartment As Recordset
Public rsEmployee As Recordset
Public rsLeaveType As Recordset
Public rsTitlePos As Recordset
Public rsTrans As Recordset
Public rsClone As Recordset
Public rsSetLeave As Recordset
Public fso As FileSystemObject

--------------------------------------
then in my main MDI form load, I set ALL recordsets and connection:


Set cnLeave = New Connection
cnLeave.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects\LeaveCredits\Leave.mdb;Persist Security Info=False"
cnLeave.Open

Set rsAdmin = New Recordset
With rsAdmin
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With

Set rsBranch = New Recordset
With rsBranch
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
Set rsDepartment = New Recordset
With rsDepartment
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
Set rsEmployee = New Recordset
With rsEmployee
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
Set rsLeaveType = New Recordset
With rsLeaveType
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
Set rsTitlePos = New Recordset
With rsTitlePos
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
Set rsTrans = New Recordset
With rsTrans
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With
Set rsSetLeave = New Recordset
With rsSetLeave
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With

------------------------------------
then in childform1: (this happens also to childform nth declaring different recordsets)

rsSetLeave.Open "select * from setleave", cnLeave
rsEmployee.Open "select * from employee order by name", cnLeave
rsDepartment.Open "select * from department order by department", cnLeave
Set rsClone = rsEmployee.Clone

rsBranch.Open "select * from branch order by branch", cnLeave
Set DataCombo2.RowSource = rsBranch
DataCombo2.ListField = "brcode"
Set DataCombo2.DataSource = rsEmployee
DataCombo2.DataField = "branch"

rsTitlePos.Open "select * from titlepos order by titlepos", cnLeave
Set DataCombo3.RowSource = rsTitlePos
DataCombo3.ListField = "titlepos" ......etc..


----------------------------------------------------
So your advice is to scatter with each childform the setting of recordsets depending on what childform uses it:

childform1: SET rs..OPEN....close

childform2: SET rs..OPEN...close

childform nth SET rs...OPEN...close


Is my understanding correct?








ERA
era@nittsu.com.ph
era@digitelone.com

 

[WETFOX]

OK. I already submitted my Project BECAUSE IT'S ALREADY FINISHED AND IT'S RUNNING VERY VERY SMOOTH UNLIKE BEFORE!WHHEW!!!!

BIG THANKS TO "VB5PRGRMR", "SHAILENG" AND AGAIN FOR THE SECOND TIME "CCLINT"..... THANKS FOR SAVING MY NECK!!



ERA
era@nittsu.com.ph
era@digitelone.com