Problem binding subform to disconnected ADO Recordset

[lameid]

I am having problems setting a disconnected ADO Recordset to a subform.

I am alos having problems with getting my post to take on the site here... So if this short version takes I'll try breaking it up into smaller bits.

 

[lameid]

I've never had cause to use a disconnected recordset nor to use a recordset as a recordsource....

I have the below (or approximately so) in my target subform's module.
It is failing assigning the forms recordset property with Error 3251 "Operation is not supported for this type of object."

 

[lameid]

Public Sub RequerySub(lngVisitID)
'On Error GoTo RequerySub_err
Dim cnn As New ADODB.Connection
Dim RS As New ADODB.Recordset
cnn.ConnectionString = xADOCon
cnn.Open
RS.CursorType = adOpenKeyset
RS.CursorLocation = adUseClient
RS.Open "dbo.SqlSproc" & lngVisitID, cnn, adOpenDynamic, adLockOptimistic
RS.ActiveConnection = Nothing
cnn.Close
Set cnn = Nothing
Me.Recordset = RS
Exit Sub
RequerySub_err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "RequerySub on " & Me.Name
End Sub

 

[lameid]

If it matters my connection string is built by...

xADOCon = "Driver={SQL Server};" & _
           "Server=" & conServer & ";" & _
           "Database=" & conDatabase & ";" & _
           "Uid=" & conUserName & ";" & _
           "Pwd=" & conPassword

For what it is worth if I debug it....

? TypeOf Me.recordset Is ADODB.Recordset
False

 

[lameid]

I'm using Access 2010 with SQL 2008 R2.
Based on everything I have read this seems like it should at least be possible but I am not sure what thing I am missing.

If anyone can tell me which stone I did not turn over, it would be much appreciated. And please do not suggest populating data another way as an alternative, I need to do it this way to meet several requirents.



My experience with posting not working was if the post box I am typing in gets a scrollbar, the post will not go... Which was why the one post is missing the TGML for Code... I thought it might be the tags itself. Apparently not they worked elsewhere. I'll try the code one more time... If it doesn't work I won't post again unless it is a response.

 

[formerTexan]

Hello Lameid,

I think you will need to set the recordset to the form before closing the connection. Also remember that closing the connection means that data changes are only reflected in the recordset, not saved to the tables.

Though probably irrelevant to your issue, you may want to have a look at available ADO cursor types. I vaguely recall that what you designate and what you get may not be the same.

Cheers,
formertexan

 

[formerTexan]

Greetings again,

Some years back when I was tinkering with disconnected recordsets, I wrote the following function to see what I was actually getting, Try plugging it in after opening the ADO recordset.

' input: ADO recordset
' returns: string with Supports and other recordset info
' created August, 2007
' by Bill Butler
Public Function pfSupports(ByRef rs As ADODB.Recordset) As String
Dim s As String
    With rs
        s = "ActiveConnection " & .ActiveConnection & vbCrLf
        s = s & "CursorPosition " & .CursorLocation & vbCrLf
        s = s & "CursorType " & .CursorType & vbCrLf
        s = s & "EditMode " & .EditMode & vbCrLf
        s = s & "LockType " & .LockType & vbCrLf
        s = s & "Source " & .Source & vbCrLf
        s = s & "RecordCount " & .RecordCount & vbCrLf
        s = s & vbCrLf & "---SUPPORTS---   " & vbCrLf
        s = s & "AddNew " & .Supports(adAddNew) & vbCrLf
        s = s & "ApproxPosition " & .Supports(adApproxPosition) & vbCrLf
        s = s & "Bookmark " & .Supports(adBookmark) & vbCrLf
        s = s & "Delete " & .Supports(adDelete) & vbCrLf
        s = s & "Find " & .Supports(adFind) & vbCrLf
        s = s & "HoldRecords " & .Supports(adHoldRecords) & vbCrLf
        s = s & "Index " & .Supports(adIndex) & vbCrLf
        s = s & "MovePrevious " & .Supports(adMovePrevious) & vbCrLf
        s = s & "Notify " & .Supports(adNotify) & vbCrLf
        s = s & "Resync " & .Supports(adResync) & vbCrLf
        s = s & "Seek " & .Supports(adSeek) & vbCrLf
        s = s & "UpDate " & .Supports(adUpdate) & vbCrLf
        s = s & "UpDateBatch " & .Supports(adUpdateBatch) & vbCrLf & vbCrLf
        s = s & Now()
    End With
pfSupports = s
End Function

 

[lameid]

Back from holiday weekend and day off...

Thanks for the response, no luck yet.

I tried setting the recordset before clearing the connection. I also used your function and below is the output with each value needing to be obfuscated being replaced with <value>

ActiveConnection Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server;SERVER=<ServerName>;UID=<UserName>;PWD=<Password>;APP=Microsoft Office 2010;WSID=<WorkstationName>;DATABASE=<DatabaseName>"

CursorPosition 3
CursorType 3
EditMode 0
LockType 3
Source dbo.usp_VisitMembers_List -1
RecordCount 2

---SUPPORTS---   
AddNew True
ApproxPosition True
Bookmark True
Delete True
Find True
HoldRecords True
Index False
MovePrevious True
Notify True
Resync True
Seek False
UpDate True
UpDateBatch True

5/30/2012 10:49:37 AM

 

[lameid]

Error line requires a SET.

 

[formerTexan]

Hello lameid,

I looked back at my sample and noticd something I ran into. When I thought I was binding the recordset to the form, the form was simply assuming the recordset's source as the form recordsource. In my case this source was a table or SQL string. So disconnecting the recordset was moot - the form didn't care.

Since my purpose at the time was to see if I could mate a form and disconnected ADO recordset, I used an intervening step and created a connectionless, sourceless recordset to bind. Eureka. No end runs by the form.

So now I am wondering if your form isn't gagging when attempting to apply "dbo.usp_VisitMembers_List -1" as a recordsource.

Anyway, I've attached a sample database. It does prove you are not at a deadend. Maybe you can work backwards from it and get your recordset to stick.

Cheers,
formertexan

 

[formerTexan]

Alas, I see that the attachment didn't attach. I'll have to see if there is a provision on tek-tips for uploading attachments.

formertexan

 

[lameid]

When I added the Set word to the line in error it resolved the issue.