Microsoft Access Project 2000-- set a ado recordset as a recordsource
Debbie R
I am trying to set create a recordset in my code and then use the
recordset as the recordset of a subform.
In a module I created Global variables:
Public GlobalSubSubRS As ADODB.Recordset
And then I try to create the recordset and then use it as the
recordset of a sub of a sub form. I have successfully done this with
recordsets filled by a stored procedure and conceptually I dont see
why the difference? I know the recordset has the correct records
since I followed along in the code.
Any Suggestions,
Thanks,
Debbie
.............
Set GlobalSubSubRS = New ADODB.Recordset
With GlobalSubSubRS
.Fields.Append "ChannelAddress", adInteger
.Fields.Append "ChannelType", adChar, 30
.Open
End With
If NetworkType = "T-1" Then
While I <= 24
With GlobalSubSubRS
.AddNew
![ChannelAddress] = I
![ChannelType] = "Idle"
.Update
End With
I = I + 1
Wend
ElseIf NetworkType = "E-1" Then
While I <= 32
With GlobalSubSubRS
.AddNew
![ChannelAddress] = I
![ChannelType] = "Idle"
.Update
End With
I = I + 1
Wend
ElseIf NetworkType = "T-3" Then
While I <= 28
With GlobalSubSubRS
.AddNew
![ChannelAddress] = I
![ChannelType] = " "
.Update
End With
I = I + 1
Wend
ElseIf NetworkType = "E-3" Then
While I <= 32
With GlobalSubSubRS
.AddNew
![ChannelAddress] = I
![ChannelType] = " "
.Update
End With
I = I + 1
Wend
ElseIf NetworkType = "OC-12" Then
While I <= 12
With GlobalSubSubRS
.AddNew
![ChannelAddress] = I
![ChannelType] = " "
.Update
End With
I = I + 1
Wend
End If
Me.CircuitType.SourceObject = "Provision_PhysicalLayer_Add_SubForm"
Set Forms!Provision_Circuit_Add!CircuitType!QryChannels_SubForm_Information.Form.RecordSource
= GlobalSubSubRS.Clone
'Set GlobalSubSubRS = Nothing
End If
End Sub
Steve Jorgensen
to a database or at least to have been connected to an actual database
at one time. It does not work with a fabricated recordset (not
without being tricky, anyway - keep reading).
The easy work-around is to call a stored procedure that makes an empty
temporary table with the columns you want, and selects * from that.
Then, disconnect the recordset ( Set rs.ActiveConnection = Nothing ).
For this to work, the recordset must be opened using a client-side
cursor and static recordset type. Note that once you have done this
once, you can save the disconnected recordset to a stream and produce
new copies of it when you need them later without having to connect
back to the server. If you persist the data as XML, you can even
print out the text, and write a function to build a stream from the
text strings, so you can run the code later without connecting to
anything at all.
Now, the -really- clever work-around is to write code that lets you
create a fabricated recordset, save it to XML, then transform the XML
so that when you use that to open a new recordset, it will look like a
disconnected recordset.
Function to make a fabricated recordset look like a disconnected
recordset (requires reference to Microsoft XML library to run):
===============
Public Sub SCJ_ADOMakeFabRSBoundEd( _
rsFabricated As ADODB.Recordset _
, rsBoundEdit As ADODB.Recordset _
)
Dim stmPersist As ADODB.Stream
Dim xmd As MSXML2.DOMDocument
Dim xmn As MSXML2.IXMLDOMNode
Dim xma As MSXML2.IXMLDOMAttribute
Set stmPersist = New ADODB.Stream
stmPersist.Type = adTypeText
stmPersist.Open
rsFabricated.Save stmPersist, adPersistXML
Set xmd = New MSXML2.DOMDocument
stmPersist.Position = 0
xmd.loadXML stmPersist.ReadText
stmPersist.Close
Set stmPersist = Nothing
For Each xmn In xmd.getElementsByTagName("s:AttributeType")
Set xma = xmd.createAttribute("rs:basetable")
xma.Value = "T"
xmn.Attributes.setNamedItem xma
Set xma = xmd.createAttribute("rs:basecolumn")
xma.Value = xmn.Attributes.getNamedItem("name").nodeValue
xmn.Attributes.setNamedItem xma
Next
Set xma = Nothing
Set xmn = Nothing
Set stmPersist = New ADODB.Stream
stmPersist.Type = adTypeText
stmPersist.Open
stmPersist.WriteText xmd.xml
Set xmd = Nothing
stmPersist.Position = 0
Set rsBoundEdit = New ADODB.Recordset
rsBoundEdit.Open stmPersist
stmPersist.Close
Set stmPersist = Nothing
End Sub
===============
Code behind form making use of the SCJ_ADOMakeFabRSBoundEd function
above:
===============
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim rsFabricated As ADODB.Recordset
Dim rsBoundEd As ADODB.Recordset
Dim fld As ADODB.Field
Set rsFabricated = New ADODB.Recordset
rsFabricated.Fields.Append _
"TestID", adInteger, , adFldKeyColumn + adFldRowID
rsFabricated.Fields.Append _
"TestString", adVarChar, 25 _
, adFldIsNullable + adFldMayBeNull + adFldUpdatable
rsFabricated.Open
SCJ_ADOMakeFabRSBoundEd rsFabricated, rsBoundEd
rsFabricated.Close
Set rsFabricated = Nothing
Set Me.Recordset = rsBoundEd
Set rsBoundEd = Nothing
End Sub
Private Sub Form_Unload(Cancel As Integer)
Dim rsBoundEd As ADODB.Recordset
' Hide the detail band so the user does not see all the fields
' flash #Error before the form closes.
Me.Detail.Visible = False
Set rsBoundEd = Me.Recordset
Set Me.Recordset = Nothing
rsBoundEd.Close
Set rsBoundEd = Nothing
End Sub
On 11 Dec 2002 13:58:39 -0800, Debbie....@anixter.com (Debbie R)
wrote:
Lyle Fairfield
I have success with this concept when I include these two lines:
.CursorLocation = adUseClient
.LockType = adLockOptimistic
before opening the recordset.
Lyle Fairfield
news:1b9gvu0n240mr6s0u...@4ax.com:
> Access will not do this unless the recordset appears to be connected
> to a database or at least to have been connected to an actual database
> at one time. It does not work with a fabricated recordset (not
> without being tricky, anyway - keep reading).
Please, see my reply to thee original poster. Access will do this.
--
Lyle
Steve Jorgensen
wrote:
>Debbie....@anixter.com (Debbie R) wrote in message news:<6ee6fc4.02121...@posting.google.com>...
>> Help!
>> I am trying to set create a recordset in my code and then use the
>> recordset as the recordset of a subform.
>>
...
>
>I have success with this concept when I include these two lines:
>
> .CursorLocation = adUseClient
> .LockType = adLockOptimistic
>
>before opening the recordset.
I just tried this out, and it does seem to work, but is not
user-editable via the form after binding. If you use a disconnected
recordset or a properly faked disconnected recordset, you can edit it
through the form in Access 2002.
Let me know if your results differ, I see I'm not using the latest
MDAC update, so behavior may have changed.
Debbie R
I get the same error message over and over again:
Run-Time error 7965
The object you entered is not a valid recordset property.
The only time I recieved a different error message was when I tried to
update the recordset I disconnected from the temporary table.
All the ideas are great but I must be missing something if it works
for you guys.
When I try using:
Set globalsubsubrs = New ADODB.Recordset
With globalsubsubrs
.Fields.Append "ChannelAddress", adInteger
.Fields.Append "ChannelType", adChar, 30
.CursorLocation = adUseClient
.LockType = adLockOptimistic
End With
globalsubsubrs.Open
.CursorLocation = adUseClient
.LockType = adLockOptimistic
While i <= 32
With globalsubsubrs
.AddNew
![ChannelAddress] = i
![ChannelType] = "Idle"
.Update
End With
i = i + 1
Wend
Me.CircuitType.SourceObject = "Provision_PhysicalLayer_Add_SubForm"
Set Forms!Provision_Circuit_Add!CircuitType!QryChannels_SubForm_Information.Form.Recordset
= globalsubsubrs.Clone
---
globalsubsubrs is declared as a global recordset
I get the following error message:
Run-Time error 7965
The object you entered is not a valid recordset property.
When I tried setting the forms recordset to the XML document:
Set rsFabricated = New ADODB.Recordset
rsFabricated.Fields.Append _
"ChannelAddress", adInteger, , adFldKeyColumn + adFldRowID
rsFabricated.Fields.Append _
"ChannelType", adVarChar, 30 _
, adFldIsNullable + adFldMayBeNull + adFldUpdatable
rsFabricated.Open
While i <= 32
With rsFabricated
.AddNew
![ChannelAddress] = i
![ChannelType] = "Idle"
.Update
End With
i = i + 1
Wend
SCJ_ADOMakeFabRSBoundEd rsFabricated, rsBoundEd
rsFabricated.Close
Set rsFabricated = Nothing
Me.CircuitType.SourceObject = "Provision_PhysicalLayer_Add_SubForm"
Set Forms!Provision_Circuit_Add!CircuitType!
QryChannels_SubForm_Information.Form.Recordset = rsBoundEd
Set rsBoundEd = Nothing
-------
I recieve the same error message. Truly I am at a loss......
I verified the subform is being properly referenced by checking the
name property. Perhaps my references are incorrect or I really don't
know.
Thanks for all the help and please don't give up on me.
Thanks,
Debbie
Steve Jorgensen <nos...@nospam.nospam> wrote in message news:<kvjgvugbqsn9k43ik...@4ax.com>...
Steve Jorgensen
selected in Tools/References is the latest version available in the
list. If it isn't, uncheck the selected version, and check the newer
one. While you're at it, see if DAO is the lates, if you're using it
- DAO has nothing to do with your problem, though.
On 12 Dec 2002 10:25:15 -0800, Debbie....@anixter.com (Debbie R)
wrote:
>I have all the ideas provided and I am still unable to make this work.
Lyle Fairfield
Set globalsubsubrs = New ADODB.Recordset
With globalsubsubrs
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Fields.Append "ChannelAddress", adInteger
.Fields.Append "ChannelType", adChar, 30
.Open
While i <= 32
.AddNew
![ChannelAddress] = i
![ChannelType] = "Idle"
.Update
i = i + 1
Wend
End With
Me.CircuitType.SourceObject = "Provision_PhysicalLayer_Add_SubForm"
Set Forms!Provision_Circuit_Add!CircuitType!QryChannels_SubForm_Information.Form.Recordset
= globalsubsubrs
Debbie....@anixter.com (Debbie R) wrote in message news:<6ee6fc4.02121...@posting.google.com>...
Debbie R
on using a local recordset and emptying out as soon as possible, I did
not clean up my code with the change from local to global, but thanks
for the point out.
I have tried everything with a thousand variations. My MDAC is the
latest and greatest, I have all the SR for Office 2000, but still no
go. I ended up created a session ID table and then assigning this
value to all records created by the user in a table used for temporary
evaluation. I hate giving up and I appreciate all your help but to be
quite frank I am absolutely clueless? However curiousity is eating
away at me so if you had any other clues I would be interested.
Thanks,
Debbie
lyle...@yahoo.com (Lyle Fairfield) wrote in message news:<42d36dfd.02121...@posting.google.com>...