Using filter results to append records

[Bill Parker]

I am using a form (frmSearchAlpha) that uses as its structure Allen Browne's
Very Excellent filter code. The filter works great, and we have been using
it for some time. We take the filter and use it in a VB SQL statement to
append the filter results to another table (tblRetrievalVest) The problem is
we are now filtering almost 500,000 records. The filtering doesn't take a
long time, particularly after the first search, but the append SQL is
beginning to. Is there code that allows us to append directly from the
filter results instead running the filter all over again in the SQL?

Here is the code we are using now:
Private Sub Command69_Click()

If Me.FilterOn = True Then
Dim strAdd As String
strAdd = "INSERT INTO tblRetrievalVest ( ID, DOB, [Exam Year], Notes,
[Job#], [Box#], [Rec Pos], [Volume#], Name, MRN, OrgID, Accession )SELECT
tblAlpha.ID, tblAlpha.DOB, tblAlpha.[Exam Year], tblAlpha.Notes,
tblAlpha.[Job#], tblAlpha.[Box#], tblAlpha.[Rec Pos], tblAlpha.[Volume#],
tblAlpha.Name, tblAlpha.MRN, tblAlpha.OrgID, tblAlpha.Accession From TBLALPHA
WHERE " & Me.Filter
End If
Call Command78_Click
Me.ChooseJob = Null
DoCmd.RunSQL strAdd
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

As always, thank you for your kind consideration in advance,
Bill

 

[Allen Browne]

There are several factors that could affect the time here. Indexes will be
crucial.

Your form draws records from TBLALPHA. For the filter controls you offer the
user, make sure those fields are indexed. So Access can use the indexes,
avoid leading wildcards if possible. Also, use the correct data types, e.g.
don't use Like on a Number field.

The code inserts records into tblRetrievalVest. Remove any spurious indexes
on fields in this table, as Access has to create these indexes as well as
insert the data. If there are multiple indexes on a field, or if some fields
are indexed that don't need to be, that will slow it down.

You could try Execute instead of DoCmd.RunSQL. The disadvantage is that you
lose the progress meter. The advantage is that you can wrap the entire
insert in a transaction, which may be faster, but certainly is safer. If
transactions are new, here's the low-down:
Archive: Move Records to Another Table
at:
http://allenbrowne.com/ser-37.html

Finally, for some situations, you may not need all those fields in the
target table. It might be enough just to write the ID, and then use an inner
join query with TBLALPHA to pick up the rest of the fields when you need
them.

Hope that helps.

 

[Bill Parker]

Thanks, Allen, those tips have decreased the filter time.

Best,
Bill
--
William E. Parker
IT Manager
MetalQuest

There are several factors that could affect the time here. Indexes will be
crucial.

Your form draws records from TBLALPHA. For the filter controls you offer the
user, make sure those fields are indexed. So Access can use the indexes,
avoid leading wildcards if possible. Also, use the correct data types, e.g.
don't use Like on a Number field.

The code inserts records into tblRetrievalVest. Remove any spurious indexes
on fields in this table, as Access has to create these indexes as well as
insert the data. If there are multiple indexes on a field, or if some fields
are indexed that don't need to be, that will slow it down.

You could try Execute instead of DoCmd.RunSQL. The disadvantage is that you
lose the progress meter. The advantage is that you can wrap the entire
insert in a transaction, which may be faster, but certainly is safer. If
transactions are new, here's the low-down:
Archive: Move Records to Another Table
at:
http://allenbrowne.com/ser-37.html

Finally, for some situations, you may not need all those fields in the
target table. It might be enough just to write the ID, and then use an inner
join query with TBLALPHA to pick up the rest of the fields when you need
them.

Hope that helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using a form (frmSearchAlpha) that uses as its structure Allen
Browne's
Very Excellent filter code. The filter works great, and we have been
using
it for some time. We take the filter and use it in a VB SQL statement to
append the filter results to another table (tblRetrievalVest) The problem
is
we are now filtering almost 500,000 records. The filtering doesn't take a
long time, particularly after the first search, but the append SQL is
beginning to. Is there code that allows us to append directly from the
filter results instead running the filter all over again in the SQL?

Here is the code we are using now:
Private Sub Command69_Click()

If Me.FilterOn = True Then
Dim strAdd As String
strAdd = "INSERT INTO tblRetrievalVest ( ID, DOB, [Exam Year], Notes,
[Job#], [Box#], [Rec Pos], [Volume#], Name, MRN, OrgID, Accession )SELECT
tblAlpha.ID, tblAlpha.DOB, tblAlpha.[Exam Year], tblAlpha.Notes,
tblAlpha.[Job#], tblAlpha.[Box#], tblAlpha.[Rec Pos], tblAlpha.[Volume#],
tblAlpha.Name, tblAlpha.MRN, tblAlpha.OrgID, tblAlpha.Accession From
TBLALPHA
WHERE " & Me.Filter
End If
Call Command78_Click
Me.ChooseJob = Null
DoCmd.RunSQL strAdd
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

As always, thank you for your kind consideration in advance,
Bill