Slow Code using FindFirst

Access2003 in XP
I'm using the code below to append any new records from
(tbl_From_Mainframe) into (tbl_Appended_Data).
It takes more than a minute to search 7000 records for a dozen new
records.
The file I'm searching is in a data farm so I'm stuck with using it in
its present format.
Linking to the file or importing it as a local table have no effect on
speed.
How can I get this code to perform quicker?

Public Function Append_New_ITRs()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strCriteria As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_Appended_Data", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("tbl_From_Mainframe", dbOpenDynaset)

Do Until rst2.EOF

strCriteria = "[ITR]='" & Right(rst2.Fields(7), 5) & "' " _
& "AND [Rel]='" & rst2.Fields(1) & "' " _
& "AND [Part Number]='" & rst2.Fields(0) & "'"

With rst
.FindFirst (strCriteria)
If .NoMatch Then
.AddNew
.Fields(0) = Right(rst2.Fields(7), 5)
.Fields(1) = rst2.Fields(0)
.Fields(2) = rst2.Fields(1)
.Fields(3) = rst2.Fields(2)
.Fields(4) = rst2.Fields(9)
.Fields(5) = rst2.Fields(5)
.Update
End If
End With
rst2.MoveNext
Loop

rst.Close
rst2.Close
dbs.Close

End Function

Re: Slow Code using FindFirst

Hi,
I use this and it performs well

set rst = dbs.openrecordset(strCriteria,dbopendynaset)
if rst.eof then
rst.addnew
etc....

hope it helps
bobh.

On Oct 23, 1:01 pm, Rick wrote:
> Access2003 in XP
> I'm using the code below to append any new records from
> (tbl_From_Mainframe) into (tbl_Appended_Data).
> It takes more than a minute to search 7000 records for a dozen new
> records.
> The file I'm searching is in a data farm so I'm stuck with using it in
> its present format.
> Linking to the file or importing it as a local table have no effect on
> speed.
> How can I get this code to perform quicker?
>
> Public Function Append_New_ITRs()
>
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
> Dim rst2 As DAO.Recordset
> Dim strCriteria As String
>
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecordset("tbl_Appended_Data", dbOpenDynaset)
> Set rst2 = dbs.OpenRecordset("tbl_From_Mainframe", dbOpenDynaset)
>
> Do Until rst2.EOF
>
> strCriteria = "[ITR]='" & Right(rst2.Fields(7), 5) & "' " _
> & "AND [Rel]='" & rst2.Fields(1) & "' " _
> & "AND [Part Number]='" & rst2.Fields(0) & "'"
>
> With rst
> .FindFirst (strCriteria)
> If .NoMatch Then
> .AddNew
> .Fields(0) = Right(rst2.Fields(7), 5)
> .Fields(1) = rst2.Fields(0)
> .Fields(2) = rst2.Fields(1)
> .Fields(3) = rst2.Fields(2)
> .Fields(4) = rst2.Fields(9)
> .Fields(5) = rst2.Fields(5)
> .Update
> End If
> End With
> rst2.MoveNext
> Loop
>
> rst.Close
> rst2.Close
> dbs.Close
>
> End Function

Re: Slow Code using FindFirst

On Oct 23, 12:01 pm, Rick wrote:
> Access2003 in XP
> I'm using the code below to append any new records from
> (tbl_From_Mainframe) into (tbl_Appended_Data).
> It takes more than a minute to search 7000 records for a dozen new
> records.
> The file I'm searching is in a data farm so I'm stuck with using it in
> its present format.
> Linking to the file or importing it as a local table have no effect on
> speed.
> How can I get this code to perform quicker?
>
> Public Function Append_New_ITRs()
>
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
> Dim rst2 As DAO.Recordset
> Dim strCriteria As String
>
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecordset("tbl_Appended_Data", dbOpenDynaset)
> Set rst2 = dbs.OpenRecordset("tbl_From_Mainframe", dbOpenDynaset)
>
> Do Until rst2.EOF
>
> strCriteria = "[ITR]='" & Right(rst2.Fields(7), 5) & "' " _
> & "AND [Rel]='" & rst2.Fields(1) & "' " _
> & "AND [Part Number]='" & rst2.Fields(0) & "'"
>
> With rst
> .FindFirst (strCriteria)
> If .NoMatch Then
> .AddNew
> .Fields(0) = Right(rst2.Fields(7), 5)
> .Fields(1) = rst2.Fields(0)
> .Fields(2) = rst2.Fields(1)
> .Fields(3) = rst2.Fields(2)
> .Fields(4) = rst2.Fields(9)
> .Fields(5) = rst2.Fields(5)
> .Update
> End If
> End With
> rst2.MoveNext
> Loop
>
> rst.Close
> rst2.Close
> dbs.Close
>
> End Function

Is there no chance of creating an combination index and opening it as
a dbOpenTable? Seek is the fast way to search a database.
Another option may be to try ADO instead of DAO, although it may not
be any faster.
Also: you are opening rst2 as dbOpenDynaset, but it doesn't appear
that you need read/write capabilities... could you use dbOpenSnapshot
instead?
One more thing: you may be able to do a mass update using INSERT INTO
and an outer join. I know Access doesn't support the outer join, but
it can simulate it with a couple of inner joins. Search the news
groups for examples.

Re: Slow Code using FindFirst

Rick wrote:

> Access2003 in XP
> I'm using the code below to append any new records from
> (tbl_From_Mainframe) into (tbl_Appended_Data).
> It takes more than a minute to search 7000 records for a dozen new
> records.
> The file I'm searching is in a data farm so I'm stuck with using it in
> its present format.
> Linking to the file or importing it as a local table have no effect on
> speed.
> How can I get this code to perform quicker?
>
> Public Function Append_New_ITRs()
>
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
> Dim rst2 As DAO.Recordset
> Dim strCriteria As String
>
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecordset("tbl_Appended_Data", dbOpenDynaset)
> Set rst2 = dbs.OpenRecordset("tbl_From_Mainframe", dbOpenDynaset)
>
> Do Until rst2.EOF
>
> strCriteria = "[ITR]='" & Right(rst2.Fields(7), 5) & "' " _
> & "AND [Rel]='" & rst2.Fields(1) & "' " _
> & "AND [Part Number]='" & rst2.Fields(0) & "'"
>
> With rst
> .FindFirst (strCriteria)
> If .NoMatch Then
> .AddNew
> .Fields(0) = Right(rst2.Fields(7), 5)
> .Fields(1) = rst2.Fields(0)
> .Fields(2) = rst2.Fields(1)
> .Fields(3) = rst2.Fields(2)
> .Fields(4) = rst2.Fields(9)
> .Fields(5) = rst2.Fields(5)
> .Update
> End If
> End With
> rst2.MoveNext
> Loop
>
> rst.Close
> rst2.Close
> dbs.Close
>
> End Function
>
Maybe a query can work
Create a query, Query1, for "tbl_Appended_Data". Create another column
in the query like
ITRShort :Right(Field7Name,5)

Now create another query. Add "tbl_From_Mainframe" and "Query1". Drag
link lines between ITR/ITRShort, Rel, and Part Number. Go to each link
line and set it to All In Mainframe and Matching in Query1.

Drag the fields you want to append from MainFrame into AppendData.

Drag ITRShort, Rel, and Part Number from Query1. Set show off on them.
In the criteria row enter IsNull in each of those columns.

Now run the query. Does it work well? If so, go to the menu, select
Query, select Append and tell it which fields to append.

Re: Slow Code using FindFirst

On Oct 23, 11:43 am, Salad wrote:
> Rick wrote:
> > Access2003 in XP
> > I'm using the code below to append any new records from
> > (tbl_From_Mainframe) into (tbl_Appended_Data).
> > It takes more than a minute to search 7000 records for a dozen new
> > records.
> > The file I'm searching is in a data farm so I'm stuck with using it in
> > its present format.
> > Linking to the file or importing it as a local table have no effect on
> > speed.
> > How can I get this code to perform quicker?
>
> > Public Function Append_New_ITRs()
>
> > Dim dbs As DAO.Database
> > Dim rst As DAO.Recordset
> > Dim rst2 As DAO.Recordset
> > Dim strCriteria As String
>
> > Set dbs = CurrentDb
> > Set rst = dbs.OpenRecordset("tbl_Appended_Data", dbOpenDynaset)
> > Set rst2 = dbs.OpenRecordset("tbl_From_Mainframe", dbOpenDynaset)
>
> > Do Until rst2.EOF
>
> > strCriteria = "[ITR]='" & Right(rst2.Fields(7), 5) & "' " _
> > & "AND [Rel]='" & rst2.Fields(1) & "' " _
> > & "AND [Part Number]='" & rst2.Fields(0) & "'"
>
> > With rst
> > .FindFirst (strCriteria)
> > If .NoMatch Then
> > .AddNew
> > .Fields(0) = Right(rst2.Fields(7), 5)
> > .Fields(1) = rst2.Fields(0)
> > .Fields(2) = rst2.Fields(1)
> > .Fields(3) = rst2.Fields(2)
> > .Fields(4) = rst2.Fields(9)
> > .Fields(5) = rst2.Fields(5)
> > .Update
> > End If
> > End With
> > rst2.MoveNext
> > Loop
>
> > rst.Close
> > rst2.Close
> > dbs.Close
>
> > End Function
>
> Maybe a query can work
> Create a query, Query1, for "tbl_Appended_Data". Create another column
> in the query like
> ITRShort :Right(Field7Name,5)
>
> Now create another query. Add "tbl_From_Mainframe" and "Query1". Drag
> link lines between ITR/ITRShort, Rel, and Part Number. Go to each link
> line and set it to All In Mainframe and Matching in Query1.
>
> Drag the fields you want to append from MainFrame into AppendData.
>
> Drag ITRShort, Rel, and Part Number from Query1. Set show off on them.
> In the criteria row enter IsNull in each of those columns.
>
> Now run the query. Does it work well? If so, go to the menu, select
> Query, select Append and tell it which fields to append.- Hide quoted text -
>
> - Show quoted text -

I followed your instructions and it worked up to the last line.
It required one more query because the null field names created
duplicate field names and query two got confused.
Made query two a make table and used that table to append via query 3.
Running query 2 also runs query 1 then I follow by running query 3.
By the time I can get the append table open the records are there in a
blur.
Some how I thought code would naturally run faster than a group of
queries.
Who knew?
Your solution was also faster to create than writing the half page of
code.

Thanks to all who answered, this forum is great.
Rick