Link to home
Start Free TrialLog in
Avatar of skillilea
skillilea

asked on

Access: Disconnected recordset

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
Avatar of skillilea
skillilea

ASKER

One more bit.

We use the command object to open the RS.

All calls are stored procedures.

tnx tons!
Avatar of Brook Braswell
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
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?
ASKER CERTIFIED SOLUTION
Avatar of datAdrenaline
datAdrenaline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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