Solved

Search on ADO Recordset

Posted on 2003-12-04
20
2,988 Views
Last Modified: 2013-12-25
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

0
Comment
Question by:kenspencer
  • 9
  • 6
  • 2
  • +3
20 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 250 total points
Comment Utility
Have you tried using Filter instead of Find.  It gives much better performance.

Leon
0
 
LVL 8

Expert Comment

by:spongie
Comment Utility
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
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
 
LVL 48

Expert Comment

by:Mikal613
Comment Utility
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
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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
 
LVL 1

Expert Comment

by:MsLim
Comment Utility
The author one is to lock the record , now only searching , so do not require to use adlockoptimistic,mikal613 is ok
0
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
>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
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
*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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
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
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
Any error message ?
have you try using Cnn.Execute method ?
0
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Ken,

Thanks for the grade.

Leon
0
 
LVL 3

Author Comment

by:kenspencer
Comment Utility
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now