troubleshooting Question

Awesome Error '-2147217915 (80040E05)' with ADO Recordset.Requery method!!!!!!

Avatar of sierracebrian
sierracebrian asked on
Databases
5 Comments1 Solution2141 ViewsLast Modified:
PROBLEM: Getting non trappable runtime error (even when going into the ado connection.errors collection, it is *NOT* trappable, Error '-2147217915 (80040E05) an Object has been Opened' when doing a requery in any flavour you think of; AdAffecCurrent, Group or AllChapters, no matter what.

WHAT I AM DOING??? The following code is written in VB 6.0, having installed MDAC 2,71 and SP5 for VB, also running the JET Engine Version 4.0 with SP3. This following code trhows no error at all, I can do UdateBatch, Transactions (Begin,Commit and RollBack), add or delete records to the parent and to the child recordset, anything! but it breaks down when calling RsParent.Requery or RsParent.Resync

PLEASE GIVE ME SERIOUS ADVISE, DON´T COME UP WITH CHEESE ANSWERS :) I´M OFFERING 200 POINTS FOR THE SOLLUTION OF THIS PROBLEM.  

THANKS IN ADVANCE

CARLOS SIERRA FROM MEXICO
*SEE THE FOLLOWING CODE*




Option Explicit
Public RsParent As New ADODB.Recordset
Public RsChild As New ADODB.Recordset
Public Cmd As New ADODB.Command
Public Cnx As New ADODB.Connection

Public Sub PrepareData()
Dim strCS As String, txtSQL As String

strCS = "Provider=MSDataShape.1;Persist Security Info=False;" & _
               "Data Source=C:\Archivos de programa\ObjGen 2.1 Demo\Nwind.mdb;" & _
               "Data Provider=MICROSOFT.JET.OLEDB.4.0"
             
 txtSQL = "SHAPE {SELECT * FROM `Customers`}  AS Customers " & _
                  "APPEND ({SELECT * FROM `Orders`}  AS Orders " & _
                  "RELATE 'CustomerID' TO 'CustomerID') AS Orders"

'First, prepare and open the connection object

With Cnx
    .ConnectionString = strCS
    .CursorLocation = adUseClient
    .Mode = adModeShareDenyNone
    .Open
End With

'Then go with the command object

With Cmd
    .CommandText = txtSQL
    .ActiveConnection = Cnx
    .CommandType = adCmdText
End With

'Then go with the recordsets
With RsParent
        .CursorType = adOpenDynamic
        .LockType = adLockBatchOptimistic
        .CursorLocation = adUseClient
        .StayInSync = True
        .Properties("Unique Table") = "Customers"
End With
       
With RsChild
        .CursorType = adOpenDynamic
        .LockType = adLockBatchOptimistic
        .CursorLocation = adUseClient
        .StayInSync = True
        .Properties("Unique Table") = "Orders"
End With

'At Last, create both recordsets and work with em'
Set RsParent = Cmd.Execute
If Not (RsParent.EOF And RsParent.BOF) Then
    RsParent.MoveLast
    RsParent.MoveFirst
    Else
    RsParent.Move 0
End If

Set RsChild = RsParent.Fields("Orders").UndelyingValue
End Sub

PROBLEM COMES UP WHEN CALLING:
RsParent.Requery
or
RsParent.Requery AdAffectCurent
or
RsParent.Requery AdAffectGroup 'when using filter
or
RsParent.Requery AdAffectAllChapters
or
RsParent.Resync
or
RsParent.Resync AdAffectCurrent
or
RsParent.Resync AdAffectGroup
or
RsParent.Resync AdAffectAllChapters


ASKER CERTIFIED SOLUTION
modulo

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros