troubleshooting Question

Extracting records from DB. Extract from full table or query.

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft Access
6 Comments2 Solutions101 ViewsLast Modified:
I am looking at and trying to improve an Access 2003 application that was developed a while ago.  Eventually moving it to Access 2013.

There are a lot of subroutine and functions in this application whose sole purpose its to pull a subset of information from one record in the table based on some passed selection criterion, usually a record ID.

In some of the routines the developer is selecting records from a query, in others they are selecting from the actual access table in the backed mdb.

In the example below the user want information from exactly 1 record in a table of about 500,000 records.  There is no updating, just pulling the information on the record.  The fields needed from the record are a subset of all of the fields on the record.  In this case the user is selecting the fields from the entire table 'tblTaxRecs '.  In similar situations I always create a query with the subset of fields needed and select from the query rather than the full table.

My question:  I always thought it was more efficient to use a query on the main table, with only the fields needed, instead of selecting from the entre table.  Am I right?  It's easy enough to create one query and change this code to select from the new query but this is only one of dozens like this in the application.  It would take some time to find them all and change them.  Just wondering if from an efficient processing standpoint it is worth the effort
If anyone has other ideas to make this routine more efficient please let me know.

Here it is:
Public Sub getTaxRecInfoFromIDForOverlay(passedTaxRecID As Long, _
                                         returnTaxHeaderID As Long, _
                                         returnPropertyID As Long, _
                                         returnBRT As Long, _
                                         returnTaxYear As Long, _
                                         returnPrincipalAmt As Double, _
                                         returnInterestAmt As Double, _
                                         returnPenaltyAmt As Double, _
                                         returnLienCost As Double, _
                                         returnAttyFeesAmt As Double, _
                                         returnYearTotal As Double, _
                                         returnPayStatusID As Long)

selectString = "Select Top 1  [TaxHeaderID], [PropertyID], [BRT], [TaxYear], [PrincipalAmt], [PenaltyAmt], [InterestAmt], [LienCost], [AttyFeesAmt], [PayStausID] " & _
               " From tblTaxRecs Where [ID] = " & passedTaxRecID
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open selectString, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

If rs.EOF Then

        returnTaxHeaderID = Nz(rs!TaxHeaderID, 0)
        returnPropertyID = Nz(rs!PropertyID, 0)
        returnBRT = Nz(rs!BRT, 0)
        returnTaxYear = Nz(rs!TaxYear, 0)
        returnPrincipalAmt = Nz(rs!PrincipalAmt, 0)
        returnInterestAmt = Nz(rs!InterestAmt, 0)
        returnPenaltyAmt = Nz(rs!PenaltyAmt, 0)
        returnLienCost = Nz(rs!LienCost, 0)
        returnAttyFeesAmt = Nz(rs!AttyFeesAmt, 0)
        returnYearTotal = Round(returnPrincipalAmt + returnInterestAmt + returnPenaltyAmt + returnLienCost + returnAttyFeesAmt, 2)
        returnPayStatusID = Nz(rs!PayStausID, 0)
End If
Set rs = Nothing

End Sub
Get vaccinated; Social distance; Wear a mask
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros