Access: Disconnected recordset

skillilea
skillilea used Ask the Experts™
on
MSAccess 2010
No tables in access.  We directly write the VBA to SQL using unbound controls on most except for...my issue :).

I'll try to explain.

What I want to do is bind a RS from a sql call to a form.
Allow the user to change the data on the form.
Using the afterupdate event we will handle the data.
The form needs to be a continuous form.
On every SQL call we open, select and close the connection.
I don't want to use a temp table.



What is the best way for me to coax access to let this work?

Couple ideas...

1) Dump the Return to an array, loop and populate a an RS and bind it.  This might work???
2) Somehow load the form and convince access it is closed and allow for trapping of the errors to edit the data.

Any help would be greatly appreciated.

tnx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
One more bit.

We use the command object to open the RS.

All calls are stored procedures.

tnx tons!
Brook BraswellApplication Development Manager

Commented:
Using ADO you could open a dynamic recordset...

Public Sub OpenRS_SRV(ByRef rs As ADODB.Recordset, ByVal SQL As String)
           Dim iErr As Integer
         iErr = 0
         On Error GoTo PROC_ERR
         If rs Is Nothing Then Set rs = New ADODB.Recordset
         If rs.State = 1 Then rs.Close
         rs.CursorLocation = adUseServer
         rs.open SQL, myConn, adOpenDynamic, adLockReadOnly
Proc_Exit:
        Exit Sub
Proc_Err:
        ' your error trapping here
End Sub
Top Expert 2014

Commented:
In theory, yes.  You could create a disconnected recordset variable at a module level (scope) and then assign the form's recordset = your disconnected recordset variable.

However, forms and controls usually define their source of data by way of the RowSource property at design time.  There might be some confusion as the form loads.

I think your idea is worth exploring.  Have you tried anything?
You can do this fairly easily.  I do (have done) similar things often.  What I do is first create a linked table (or series of linked tables in order to build a Query object that has all the data I wish to present), then build my Form objects using the linked table (or Query object).  I do this so the controls have there Control Source property set to the Field name that reflects what the disconnected recordset will contain.  Please note that I do this only as a conveinience because you can manipulate the Control Source property through VBA at run to reflect the field you want to bind to with out much trouble.

Once I have designed my Form object, I blank out the RecordSource property of the Form object (or the RowSource property of Combo/List boxes if they will be populated with a disconnected recordset as well).

Then in code you can create your disconnected recordset with cursor location of adUseClient.  Also, as an FYI, my preference is to use a cursor type of adOpenStatic. Then use that disconnected recordset to Set the Form objects' (or combo/list boxes) Recordset property.  For example:

Set obj.Recordset = disconnectedRecordsetObjectVariable

(Where obj is an object reference to a Form or control that has the .Recordset property)

Please note that it is not neccessary to use a Module declared object variable for the disconnected recordset.

This will work since the controls on your form have been pre-defined to be bound to specific field names using the linked table or query object.  Once the form is working correctly, I delete the linked table or query object I used during the design process.

Author

Commented:
Here is where I got to...seems to work.

Private Sub Load_Data()
    Dim x As Integer
    Dim ar As Variant
    Dim aRS As New ADODB.Recordset

'get an array...actually is turned by rs.getrows in the proc
    ar = ModSQL.ReturnCRMatrixHour()
   
    If IsArray(ar) Then
        For x = 0 To UBound(ar, 1)
'fake the column headings
            aRS.Fields.Append "f" & Trim(Str(x)), adVarChar, 500
        Next
       
        With aRS
            .LockType = adLockOptimistic
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .Open
        End With

        For x = 0 To UBound(ar, 2)
            aRS.AddNew
            aRS.Fields(0).Value = ar(0, x)
            aRS.Fields(1).Value = ar(1, x)
            aRS.Fields(2).Value = ar(2, x)
            aRS.Fields(3).Value = ar(3, x)
            aRS.Update
        Next
        aRS.MoveFirst
       
        Set Me.Recordset = aRS
        Me.txtEntity.ControlSource = "f1"
        Me.txtCRHour.ControlSource = "f2"
        Me.txtDRHour.ControlSource = "f3"
       
        aRS.Close
        Set aRS = Nothing
       
    End If
   
End Sub
That should work just fine.  I do (have done) the same before --- create a recordset, then populate it.  I view a 'created' recordset slightly different than a 'disconnected' recordset simply because a created one was never connected in the first place, so you don't really disconnect it --- but that is how I separate it in my brain :)

I have posted on-line at UtterAccess, and have attached that same sample to this post.  You may be able to get some thoughts out of it <shrug> ... in the sample, I don't use the cursortype I suggested above, but the sample is rather old (its amazing that a forward only actually works -- it must be that Access magic!)
TableLessRecordset.zip

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial