Search on ADO Recordset

Hi,
I am using VB6 (SP5) to open Access 2000 database/tables via ADO to copy/update one recordset to another.  Both tables are identically defined, with a combination key of ProgramID (6 chars) and RecordID (Autonum).  The problem I have is with a few isolated updates (not all of them, and not the inserts).  I use a generic copy routine which reads a value in the source record ("I" or "U" -- Insert or Update), and then does the following:

'src is source/transaction ADO Recordset
'dest is target/master ADO Recordset, and is opened with ORDER BY ProgramID, RecordID
'bFound is Boolean
'sFind is String
'earlier statement of  -  With src  -  was executed

    Case "U"
        bFound = False
        sFind = "[ProgramID] = '" & ![programid] & "'"
        dest.MoveFirst
        dest.Find sFind, , adSearchForward
        If dest.EOF = False Then
            dest.Find "[RecordID] = " & ![RecordID], , adSearchForward
            If dest.EOF = False Then
                If dest![programid] = ![programid] Then bFound = True
            End If
        End If

    If bFound = False Then
        g_iSkipped = g_iSkipped + 1
        GoTo CopyData_Loop
    End If

The great majority of the time a record which already exists, is indeed found.  However, there have been a few instances where the search finds the ProgramID, but on the subsequent search for the RecordID either does not find it or finds it in another ProgramID (further down in the recordset).  VERY irritating and inconsistent.  Anyone got an idea what's happening?

Ken

LVL 3
kenspencerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
Have you tried using Filter instead of Find.  It gives much better performance.

Leon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spongieCommented:
Hi again. the Find method would only look for the first instance that satisfies your criteria.  What you're doing is looking for the record that satisfies the first criteria and continue searching for the record that satifies the second criteria (without necessarily satisfying the previous or the first criteria.  Leon's right. You should use filter instead:


        dest.Filter = "[ProgramID] = '" & ![programid] & "' and [RecordID] = " & ![RecordID]

Hope that helps :0)
0
kenspencerAuthor Commented:
Initial test worked.  However, it is hard to duplicate at will the problem I described.  So, I will do a LOT of testing for the rest of this week, and assuming it holds up, will close & award this question early next week.  Just curious, though, do either of you see why my code would miss a record that I know is in there?

Ken
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

leonstrykerCommented:
ProgramID is a char datatype while RecordID is a number it is possible, it is possible something (a space or a special character) exists and therefore a match is not made.

Leon
0
Mikal613Commented:
Use RS.Filter =

But dont 4get to declare the recirdset and open like this:


Set RS= New ADODB.Recordset
    RS.CursorLocation = adUseClient

RS.Open SQL, DBConn, adOpenDynamic, adLockReadOnly, adCmdText
0
kenspencerAuthor Commented:
Mikal613,
Your .Open statement is not correct for what I need to do.  There is no concurrency issue here, and I need to update the destination recordset.  I simply go through the source recordset sequentially.  The destination recordset is opened with the following parameters:

    SQL, cnConnection, adOpenStatic, adLockOptimistic

Ken
0
MsLimCommented:
The author one is to lock the record , now only searching , so do not require to use adlockoptimistic,mikal613 is ok
0
kenspencerAuthor Commented:
A problem with .Filter has occurred.  I created a transaction file with 1065 records (a reasonable number that the central office here may receive from an area office; I coded them all as updates) to process using the .Filter technique and received the following error:

    Data provider or other service returned an E_FAIL status.

I believe this causes a cascade error of '3219 - Operation is not allowed in this context', which seems to occur just after the first error.  The process updated 330 records before receiving this error.  Is there more information needed for you all to assist with this?

Ken
0
leonstrykerCommented:
>Data provider or other service returned an E_FAIL status.

This seems like an error message generated by application (error handler) code.  Which line did this occur on?

Leon
0
kenspencerAuthor Commented:
Right on the .Update statement.  After that, I exit the copy routine and close the recordset, which is what is generating the second error listed above.  Based on what's happening, is it somehow possible that I'm losing my connection?  The connection parameters are:

    Cursor = adUseClient
    Persist Security Info=True
    Provider=Microsoft.Jet.OLEDB.4.0

Of course, just to make things interesting, the error does not always occur.  I'm always good for at least one good run after a Compact/Repair on the destination DB, sometimes 2 or 3, but inevitably I get the error.  I did change the .Filter process to:

      dest.Filter = ""
      DoEvents
      dest.Filter = "[ProgramID] = '" & ![programid] & "' and [RecordID] = " & ![RecordID]

just to see if that helps, but it does not seem to.

Ken
0
leonstrykerCommented:
Change this;

dest.Filter = "[ProgramID] = '" & ![programid] & "' and [RecordID] = " & ![RecordID]

to:

strProgId = ![programid]
strRecordID = ![RecordID]
Debug.Print strProgId & " " & strRecordID
dest.Filter = "[ProgramID] = '" & strProgId  & "' and [RecordID] = " & strRecordID

and see if there is anything special about those values then the error occurs.

Leon
0
kenspencerAuthor Commented:
*Sigh*... Nothing remarkable about the record it stops on.  The Program ID is actually a 6-digit number that is chosen from a predefined list, and it is a valid number.  The Record ID is just an ordinary long int -- nothing special (except it is defined as Autonum).  I even created a new test (transaction) file and refreshed the target database with the one the user provided.  This crashed on a different record, but again, there does not seem anything (on the surface) wrong with the 2 key fields' data.

What do you think of trying to resume at the .Update when I get the E_Fail error?  I could count the number of re-tries (for the same record) to avoid an endless loop.

Ken
0
leonstrykerCommented:
Ken,

Instead of doing the .Update  I would go with the SQL UPDATE and INSERT statements.  You won't need to build the second recordset, just concatenate the SQL string and execute it on the connection object

strSQL = "INSERT INTO table2 VALUES('" & ![programid] & "', '" & ![RecordID] & "')"
cnConn.Execute strSQL

Same for UPDATE.

Leon

0
kenspencerAuthor Commented:
Well, that makes for significant work, so I was hoping for something simpler and easier.  But, I will leave this open for a bit and if no further suggestions are forthcoming, I will close and award.

Thanks to everyone for their ideas and comments.

Ken
0
MYLimCommented:
the less of Find method i face before:
http://oldlook.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20693072.html

so,i will support filter method.
0
kenspencerAuthor Commented:
I had noticed that you cannot support multi-column .Find's, which is why I coded it the way I originally did.  However, .Filter certainly seems better.  Now if I could just figure out why the .Update does not always work ....

Ken
0
MYLimCommented:
Any error message ?
have you try using Cnn.Execute method ?
0
kenspencerAuthor Commented:
MYLim,
The .Update problem is outside the scope of my original question, so I am going to close this thread and award.  I will be testing various update methods (notable SQL Update statement) to try to circumvent the problem I am encountering.

Ken
0
leonstrykerCommented:
Ken,

Thanks for the grade.

Leon
0
kenspencerAuthor Commented:
Folks,
Thanks to all for your comments and helpful ideas.  leonstryker gets the points for first mentioning the .Filter method, which does seem to always find the record I want.  Although spongie provided a coded example, I already was familiar with a filter's syntax (opening forms, reports) -- I just didn't think of trying it here.

Ken
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.