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
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
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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Me.txtCRHour.ControlSource
Me.txtDRHour.ControlSource
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
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
ASKER
We use the command object to open the RS.
All calls are stored procedures.
tnx tons!