Solved

Search on ADO Recordset

Posted on 2003-12-04
20
2,998 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
ID: 9875235
Have you tried using Filter instead of Find.  It gives much better performance.

Leon
0
 
LVL 8

Expert Comment

by:spongie
ID: 9875412
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
ID: 9875949
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
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 29

Expert Comment

by:leonstryker
ID: 9876019
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
ID: 9876189
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
ID: 9876643
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
ID: 9879606
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
ID: 9882783
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
ID: 9882896
>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
ID: 9883209
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 9883630
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
ID: 9884334
*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
ID: 9884409
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
ID: 9884766
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
ID: 9886346
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
ID: 9895908
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
ID: 9900850
Any error message ?
have you try using Cnn.Execute method ?
0
 
LVL 3

Author Comment

by:kenspencer
ID: 9903998
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
ID: 9904021
Ken,

Thanks for the grade.

Leon
0
 
LVL 3

Author Comment

by:kenspencer
ID: 9904074
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

770 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