Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...it was ingeniously designed and all those clicks were for my own good... and that was even before I got my speedy and useful answer to my tekkie question that I eventually posted..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Other topics Forum

Problem binding subform to disconnected ADO Recordset

thread181-1684199
Read More Threads Like This One
lameid (Programmer)
25 May 12 10:25
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 (Programmer)
25 May 12 10:26
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 (Programmer)
25 May 12 10:29
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 (Programmer)
25 May 12 10:31
If it matters my connection string is built by...

CODE

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

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

CODE

? TypeOf Me.recordset Is ADODB.Recordset False
lameid (Programmer)
25 May 12 10:34
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 (Programmer)
26 May 12 10:48
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 (Programmer)
26 May 12 11:05
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.

CODE

' 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 (Programmer)
30 May 12 10:59
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>

CODE

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 (Programmer)
31 May 12 18:01
Error line requires a SET.
formerTexan (Programmer)
3 Jun 12 0:04
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 (Programmer)
3 Jun 12 0:07
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 (Programmer)
4 Jun 12 7:59
When I added the Set word to the line in error it resolved the issue.
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Other topics

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close