ADO bound recordsets in a form/subform scenario (1 Viewer)

ashwah

New member
Local time
Today, 01:26
Joined
Sep 15, 2009
Messages
5
I have a form and subform. The main form shows some customer details, and the continuous sub form shows that customer's charity donations.

The code below runs when the form opens, and binds ADO recordsets to the two forms. The binding appears to be successful.

However whatever I do I can't make the subform update correctly to show the relevant customer donations. For example, when I use the **'d lines to update the link child/master fields, I get a "Data Provider Could Not Be Initialized" error.

Any idea how I can get this to work?

Code:
Private Sub Form_Load()

    Dim cn As New ADODB.Connection
    Dim rsCust As New ADODB.Recordset
    Dim rsDons As New ADODB.Recordset
    
    Dim strConnect As String

    strConnect = "DSN=MYSQL DSN"
    
    cn.CursorLocation = adUseClient
    cn.Open strConnect
    
    With rsCust
        Set .ActiveConnection = cn
        .Source = "customer"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
    End With
        
    With rsDons
        Set .ActiveConnection = cn
        .Source = "donations"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
    End With
        
    Set Me.Recordset = rsCust
    Set Me.frmCustomerDonation.Form.Recordset = rsDons
    
    Me.frmCustomerDonation.LinkChildFields = "URN"      '**
    Me.frmCustomerDonation.LinkMasterFields = "URN"     '**
    
    Set rsCust = Nothing
    Set rsDons = Nothing
    Set cn = Nothing

End Sub
 

rzw0wr

I will always be a newbie
Local time
Yesterday, 21:26
Joined
Apr 1, 2012
Messages
489
Set rsCust = New ADODB.Recordset before the With Command
Set rsDons = NEW ADODB.Recordset Before the With Command

Just a guess. I have never used ADODB. Just going by the example in the help file.

Dale
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:26
Joined
Jan 20, 2009
Messages
12,849
The problem is that LinkMasterFields and LinkChildFields are properties of the subformcontrol, not the form.

BTW
Set rsCust = New ADODB.Recordset

This is not required because the variable is self instantiated in the Dim.
 

rzw0wr

I will always be a newbie
Local time
Yesterday, 21:26
Joined
Apr 1, 2012
Messages
489
Got it.
Thanks Galaxion.

Just a guess.

Dale
 

ashwah

New member
Local time
Today, 01:26
Joined
Sep 15, 2009
Messages
5
The problem is that LinkMasterFields and LinkChildFields are properties of the subformcontrol, not the form.

But I'm setting those properties of the subform, am I doing this correctly? The problem is the error that occurs when I try to set those properties. Am I missing something?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:26
Joined
Jan 20, 2009
Messages
12,849
One problem with changing the LinkFields dynamically is that Access insists that the LinkMaster and LinkChild have the same number and type of fields listed. When the first one changes an error is thrown so it is necessary to change OnError to Resume Next before the first change.

However I don't think that is the real issue in your case because you are not getting the mismatch LinkFields error.

Perhaps though this mismatch results in the error you are geetting because it tries to send a broken query to MySQL. Just guessing.

I have never tried to change the LinkFields on an ADO connected object while it is open.

I avoid using link field like this on ADO connection because I get better performance by dynamically loading the subform recordset as the current record changes in the main form.

I don't know about using it with MySQL but I got the best performance from MS SQL Server by using the main form's Current event procedure to run a parameterised Stored Procedure and reload the subform's ADO recordset.
 

ashwah

New member
Local time
Today, 01:26
Joined
Sep 15, 2009
Messages
5
Cheers for the insight Galaxiom, I've got something to go on now.

I've been around the houses a little, changed the data object to DAO but that brought me back to an older problem of poor performance on doing a ctr + F search.

So you're saying to create the sub form's recordset on the fly, so when the on-current event is fired. Hmm, that makes sense. I'll give it a go, when I next look at this.

I'm shelving this for a bit as it's an on-going project, and I might start tearing my hair out soon. But I'll let you know how I get on.

Cheers again.
 

kleky

Just gettin' by..
Local time
Today, 01:26
Joined
Apr 11, 2006
Messages
43
Thanks Galaxiom.

I did what you suggested and it works a treat!

For others:

1- Create a function that initialises an ADODB recordset in a module

Code:
Global g1Con As New ADODB.Connection
Global g1RS As ADODB.Recordset

Public Enum rrCursorType
    rrOpenDynamic = adOpenDynamic
        'Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement
        'through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.
    rrOpenForwardOnly = adOpenForwardOnly
        'Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward
        'through records. This improves performance when you need to make only one pass through a Recordset.
    rrOpenKeyset = adOpenKeyset
        'Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although
        'records that other users delete are inaccessible from your Recordset. Data changes by other users are still
        'visible.
    rrOpenStatic = adOpenStatic
        'Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports.
        'Additions, changes, or deletions by other users are not visible.
End Enum

Public Enum rrLockType
    rrLockOptimistic = adLockOptimistic
        'This value indicates optimistic locking, record by record. The provider uses optimistic locking, locking records
        'only when the Update method is called. This lock type is not supported by the OLE DB Provider for DB2.
    rrLockReadOnly = adLockReadOnly
        'This value indicates read-only records where the data cannot be altered.
End Enum

Public Enum rrDatabase
    rrASAM
    rrOrganisations
    rrDBSecurity
End Enum

'------------------------------------------------------------------------------------------------------
' Procedure : g1OpenRecordset
' Function  : Opens a full recordset for specified database that will allow you to add or edit data
' Return    : g1OpenRecordset-    True when rows returned
'------------------------------------------------------------------------------------------------------
Public Function g1OpenRecordset(ByRef rs As ADODB.Recordset, strSQL As String, SelDatabase As rrDatabase, _
            Optional rrCursor As rrCursorType, Optional rrLock As rrLockType, Optional blnClientSide As Boolean) As Boolean
    
   On Error GoTo g1OpenRecordset_Error

    If g1Con.State = adStateClosed Then
        g1Con.ConnectionString = g1ConnectionStr(SelDatabase)
        g1Con.Open
    ElseIf Left(g1Con.ConnectionString, Len(g1ConnectionStr(SelDatabase))) <> g1ConnectionStr(SelDatabase) Then
        'Database changed so change connection
        g1Con.Close
        g1Con.ConnectionString = g1ConnectionStr(SelDatabase)
        g1Con.Open
    End If

    Set rs = New ADODB.Recordset
    
    With rs
        .ActiveConnection = g1Con
        
        If blnClientSide Then
            .CursorLocation = adUseClient
        Else
            .CursorLocation = adUseServer
        End If
        
        .CursorType = IIf((rrCursor = 0), adOpenStatic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockReadOnly, rrLock)
        .Open strSQL
        
        If .EOF And .BOF Then Exit Function
    End With
    
    g1OpenRecordset = True

   On Error GoTo 0
   Exit Function

g1OpenRecordset_Error:

    MsgBox Err.Description & " [g1ADODB]", vbInformation + vbOKOnly
End Function

'------------------------------------------------------------------------------------------------------
' Procedure : g1ConnectionStr
' Function  : Set the connection string here for use throughout the database
'------------------------------------------------------------------------------------------------------
Public Function g1ConnectionStr(SelDatabase As rrDatabase) As String
    Dim strDatabase As String
    
   On Error GoTo g1ConnectionStr_Error
    
    Select Case SelDatabase
        Case Is = 0 'ASAM
            strDatabase = "ASAM"
        Case Is = 1 'Organisations
            strDatabase = "Organisations"
        Case Is = 2 'DBSecurity
            strDatabase = "DBSecurity"
    End Select
    
    g1ConnectionStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & strDatabase & ";Data Source=ra_xpp_d37\dev"
    'g1ConnectionStr = "ODBC;DSN=ASAM_Dev;DATABASE=" & strDatabase & ";Trusted_Connection=True;"

   On Error GoTo 0
   Exit Function

g1ConnectionStr_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure g1ConnectionStr of Module g1ADODB"
End Function

2- From Form_Current of the Parent to the subform you wish to pass the recordset, create the recordset and assign to forms Recordset property

Code:
Private Sub Form_Current()

If g1OpenRecordset(g1RS, "SELECT * FROM tblSigned WHERE intFK_AppsID = " & Me.Recordset!intPKAppsID, rrASAM _
                                    , rrOpenKeyset, rrLockOptimistic, True) = False Then Exit Sub
                                        
                                        
    Set Me.childSigned.Form.Recordset = g1RS
    Set g1RS = Nothing

3- repeat for all subforms
 

Users who are viewing this thread

Top Bottom