Solved

VB - Looping to Add records with a DAO instruction to test if record already exists

Posted on 2014-01-17
19
373 Views
Last Modified: 2014-01-19
Hello all you experts!

I have a procedure that is taking way too long, and a lot longer than I imagined it would to run.

Essentially, I have a vb that adds records to a table.  Whatever the user has indicated they want added it will run to add.  HOWEVER, before it adds them, it tests to make sure the record hasn't already been defined in the table.  I do that test with a xxxx.DAO.Recordset and strSQLxx string

Not completely understanding DAO.Recordset and requery I don't know if my process is so slow because of my ordering or my testing.

My entire code is as follows.  Am I doing the SET rs..... correct or is there a better way?

Private Sub cmdSavedata()
Dim q As Long, r As Long
Dim s As Long, s1 As Long
Dim t As Long
Dim u As Long, u1 As Long
Dim v As Long
Dim b As Long, p As Long
Dim w As Long, x As Long, y As Long, z As Long
Dim strCo As String, strBkType As String, strState As String
Dim rs As DAO.Recordset
Dim strSQL As String

q = Me.cboSelectCounty.Column(0)
r = Me.cboSelectBkType.Column(0)

With Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Bks].Form
    If ![cboSelectBk].Visible = True Then
        s = ![cboSelectBk].Column(2) ' Main Book Nbr
        t = ![cboSelectBk].Column(3) ' Sub Book Nbr
        b = 1
    Else
        s = ![cboSelectBkBegin].Column(2) ' Main Book Nbr Begin Range
        s1 = ![cboSelectBkEnd].Column(2) ' Main Book Nbr End Range
        t = ![cboSelectBkBegin].Column(3) ' Sub Book Nbr
        b = (s1 - s) + 1
    End If
End With

With Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Pgs].Form
    If ![cboSelectPg].Visible = True Then
        u = ![cboSelectPg].Column(1) ' Main Page#
        p = 1
    Else
        u = ![cboSelectPgBegin].Column(1) ' Main Page# Begin Range
        u1 = ![cboSelectPgEnd].Column(1) ' Main Page# End Range
        p = (u1 - u) + 1
    End If
End With

If Nz(Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Pgs].Form![cboSelectSubPg], 1) = 1 Then
    v = 1
Else
    v = Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Pgs].Form![cboSelectSubPg].Column(0) ' Sub Page #
End If

strState = Me.cboSelectState.Column(1)
strCo = Me.cboSelectCounty.Column(3)
strBkType = Me.cboSelectBkType.Column(2)

w = u
x = v
y = 0
z = 0

If b > 1 Then ' More than 1 Book#?
    If p > 1 Then ' More than 1 Page#?
        '*** Multiple Books & Pages
        Do Until z = b ' THIS RUNS IF MULTI BOOKS AND PAGES
            Forms![frm_ProcessingWait].Form![txtBkNbr].Value = "Updating Book # " & s
            Forms![frm_ProcessingWait].Form![txtwait].SetFocus
            Do Until y = p
                strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v
                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                If rs.RecordCount = 0 Then
                    CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
                Else
                    MsgBox "The " & strBkType & " Book # and Page# you entered has already been created for " & strCo & ", " & strState & " " & strBkType & "s." & vbNewLine & vbNewLine & _
                    "Please review the Book Type, Book # and Page # to make sure you are not trying to create duplicate information.", vbOKOnly + vbExclamation, "Error - Duplicate Info"
                End If
                u = u + 1
                y = y + 1
            Loop
            y = 0
            u = w
            z = z + 1
            s = s + 1
            Forms![frm_ProcessingWait].Form![txtBkNbr].Value = "Updating Book # " & s
            Forms![frm_ProcessingWait].Form![txtBkNbr].SetFocus
        Loop
    Else
        ' ***** Multiple Books, 1 Page only
        Do Until z = b ' THIS RUNS IF MULTI BOOKS BUT ONLY ONE PAGE
            Do Until y = p
                CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
                u = u + 1
                y = y + 1
            Loop
            z = z + 1
            t = t + 1
        Loop
    End If
Else
    If p > 1 Then ' More than 1 Page#?
    '*** One Book With Multiple Pages
        Do Until y = p ' THIS RUNS IF ONLY ONE BOOK AND MULTI PAGES
            strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v
            Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
            If rs.RecordCount = 0 Then
                CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
            Else
                MsgBox "The " & strBkType & " Book # and Page# you entered has already been created for " & strCo & ", " & strState & " " & strBkType & "s." & vbNewLine & vbNewLine & _
                "Please review the Book Type, Book # and Page # to make sure you are not trying to create duplicate information.", vbOKOnly + vbExclamation, "Error - Duplicate Info"
            End If
            u = u + 1
            y = y + 1
        Loop
    Else
    
    ' **** RUNS IF ONLY ONE BOOK AND ONE PAGE
        strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        If rs.RecordCount = 0 Then
            CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
        Else
            MsgBox "The " & strBkType & " Book # and Page# you entered has already been created for " & strCo & ", " & strState & " " & strBkType & "s." & vbNewLine & vbNewLine & _
            "Please review the Book Type, Book # and Page # to make sure you are not trying to create duplicate information.", vbOKOnly + vbExclamation, "Error - Duplicate Info"
        End If
    End If

End If

rs.Close
Set rs = Nothing
DoCmd.Close acForm, "frm_ProcessingWait", acSaveNo

End Sub

Open in new window

0
Comment
Question by:wlwebb
  • 7
  • 4
  • 3
  • +2
19 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 143 total points
ID: 39788109
are these fields

CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID

indexed ?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 144 total points
ID: 39788116
I have a procedure that is taking way too long, and a lot longer than I imagined it would to run.
How long does it take, and how many records are are you adding?

How complex is qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty? If it's anywhere near as complex as the name, that could be your trouble :)

How many records does qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty generally return?

What are the average "count" values of outer loops and inner loops? If the OL valueis commonly a 10, and the IL value commonly a 20, then you're going to run through 200 iterations on your code(OL will loop 10 times, and IL will loop 20 times for each of those 10 outer loops)?

Up to Line 55 you're just accumulating data, so I can't imagine that takes too long. After line 55 is most likely where all the heavy lifting is done.

After line 55, the only thing that stands out to me is this:

strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Instead, you could do this:

 strSQL = "SELECT CountyCodeID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v

Open in new window


Since you're really looking for the Count, and don't care whether those other fields are included (and don't do anything with them after the DAO recordset is opened).

You could also do this:

 strSQL = "SELECT COUNT(*) AS RecCount FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If rs("RecCount") >0 Then

Open in new window

0
 
LVL 84
ID: 39788117
<off topic> You're up awfully late, cap :) (or very, very early)
0
 

Author Comment

by:wlwebb
ID: 39788120
Yes.... All of them are.  This is a many to many table.

Note, even the recBkNbr and RecBkPg are indexed these are actually ID fields that I just used the LongNumber as the Key
0
 

Author Comment

by:wlwebb
ID: 39788123
PS..... I don't sleep very much.......
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39788130
(very, very early) ;-)
0
 

Author Comment

by:wlwebb
ID: 39788138
As a Clarification....

I have a Table for each of those items.  (That way Admin can control Book Types, Book Numbers (and a Book Sub Number), Book Page Number begin and ending (and a Book Page sub number)
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 143 total points
ID: 39788139
<This is a many to many table.> this is BAD news..

I will try to break this M-M relationship before I proceed with this project.


see this demo
http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033


just my 2¢
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:wlwebb
ID: 39788155
Cap........
So you get a mental pic.....

County's have Book Types

Book Types have Book Numbers (and Book Sub Numbers)

Book Numbers have Book Pages (and Book Sub Pages)

I have a Table for County's
I have a Table for Book Types
I have a Table for Book Numbers
I have a Table for Book Sub Numbers
I have a Table for Book Pages
I have a Table for Book Sub Pages

Step one is Define Book # With Book Number + Book Sub Number
Step two is Define Book Pg with Book Page + Book Sub Page
Step three Define County and Book Type

Step four Define County+Book Type with Book #s
Step five Define County+BookType+Book #'s with Book Pg (with sub pg_

Thus the qry you all are looking at is the combination of all of those limitations
0
 

Author Comment

by:wlwebb
ID: 39788159
Scott

Will changing that code change how it tests with each looping in terms of speed ?????
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 142 total points
ID: 39788341
I think I would be inclined to do this very differently.

I would open the recordset once, outside the loop, then use FindFirst (or maybe Seek if you have a compound index as that would probably be way faster).  something like the following.

Note, I don't really see any difference between the code inside the various parts of the two outer If Then Statements, (if B > 1 and if P > 1) so I've incorporated it into a single sequence.  If B = 1 or P = 1, you still have to execute the insert at least once.  I've replaced your Do While with a For - Next construct (personal preference).
Dim strCriteria as string

strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, " _
              & "RecBkPg, RecBkPgSubID " _
       & "FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty " _
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

strCriteria = "CountyCodeID=" & q & " AND RecBkTypeID=" & r _
            & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t _
            & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v

rs.FindFirst strCriteria

if .noMatch = True then

   For Z = 1 to b ' THIS RUNS IF MULTI BOOKS AND PAGES
        Forms![frm_ProcessingWait].Form![txtBkNbr].Value = "Updating Book # " & s
        Forms![frm_ProcessingWait].Form![txtwait].SetFocus
         
        For y = 1 to p
            rs.AddNew
            rs.CountyCodeID = q
            rs.RecBkTypeID = r
            rs.recBkNbr = s
            rs.RecBkNbrSubID = t
            rs.RecBkPg = u
            rs.RecBkPgSubID = v
            rs.Update
            u = u + 1
            y = y + 1
        Next
        u = w
        s = s + 1
        Forms![frm_ProcessingWait].Form![txtBkNbr].Value = "Updating Book # " & s
        Forms![frm_ProcessingWait].Form![txtBkNbr].SetFocus
   Next

Else

    MsgBox "The " & strBkType & " Book # and Page# you entered has already been created for " _
         & strCo & ", " & strState & " " & strBkType & "s." & vbNewLine & vbNewLine _
         & "Please review the Book Type, Book # and Page # to make sure you are not " _
         & "trying to create duplicate information.", _
         vbOKOnly + vbExclamation, "Error - Duplicate Info"
    
End If
rs.close
set rs = nothing

Open in new window

I'm not sure I have those counters on lines 26-29 quite right, but I think the logic here is pretty solid, and only opening the recordset once, using the FindFirst and writing directly to the recordset should be a huge time saver.
0
 
LVL 84
ID: 39788456
Will changing that code change how it tests with each looping in terms of speed ?????

Open in new window

That's hard to say without testing, but anytime you hit the database you should request as little information as needed. You're looking for a Count only, so there's no reason to return other fields (unless you use them down the line for something else).

Regarding Dale's suggestion:

The value of "u" changes for each iteration of the Inner Loop, and the value of "s" changes for each iteration of the Outer Loop. The initial creation of the recordset includes those values in the criteria, but is OUTSIDE of those loops, so it would seem that you need to run your SQL again for each IL and each OL iteration.

The use of the existing recordset may make a difference in speed, assuming that recordset is updateable. With a M-2-M join this is often not the case.

Curious still how long the operation runs, and how many records in total you're inserting.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 142 total points
ID: 39788554
Scott,

Good point about the updateability of the recordset, forgot that it is a query, not a table.  But the INSERT statement is inserting into that same query, so that (updateability) should not be an issue.  Although that could also be part of the problem, why insert into the query and not into the source table?

Maybe a better solution would be to create the query to a temptable, which would be easy to search (FindFirst or Seek) and update, then create a single query that pushes new records from the temp table back into the main table.

Or maybe you just insert the new records into a new temp table, continue to use the recordset I opened once, outside the loop to determine whether that combination of stuff in strCriteria exists in the query using the findfirst, and then after all of the looping is done, append the new records from the temp table into the main table.

Would be nice to see what that query actually looks like.

Dale
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 71 total points
ID: 39788907
An observation - There has to be some happy medium between names of 65 characters and names of 1 character.  If I had to look at the code and try to understand it, I would be hard pressed to not get dizzy from all the backward referencing I would have to do to translate the 1 character arbitrary names to the 65 character meaningful names to make sense out of what was going on.

Dale has already pointed out that you were running two queries inside the loop and one of them should have been outside the loop.  The second he converted to use .AddNew rather than running an insert query.  These changes will remove the bulk of the overhead.  I would make one additional change and that is instead of reading using a query without criteria and later using a Find to get the record you want, I would simply add criteria to the query.  That limits the set of data that must be retrieved to ONLY what you need.  Why bring back 100,000 rows when you only needed 1?  When you open a recordset based on a table or unqualified query, Access has to bring over ALL the rows.  This is especially critical if your BE is SQL Server.  It is less of a problem for Jet/ACE since they are optimized for this type of action.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 144 total points
ID: 39788938
But the INSERT statement is inserting into that same query, so that (updateability) should not be an issue.
I too am fairly confident that it's updateable, but anytime I see a M-2-M table mentioned in regard to a query it's a good idea to bring up the subject.

Still - 2 of the 6 criteria that are used to filter the initial recordset (with the FindFirst) are modified in either the inner or outer loop. I'm not entirely sure of the logic for these operations, but if the inner or outer loop is dependent on the results of the FindFirst - and from the original code, that seems to be the case - then you'd have to perform other FindFirst somewhere inside those loops to determine course of action.
0
 

Author Closing Comment

by:wlwebb
ID: 39790770
I am awarding all responders equal points based on nbr of responses.  The input is great so I am also going to open a NEW QUESTION detailing more about what I've got/done so that more points can be awarded than just the original 500 because the issue is most critical
0
 

Author Comment

by:wlwebb
ID: 39792038
EE Moderator.
If you review the original question, the questions answers led to the realization that it should have been broken down into smaller portions.  The discussion on original link led to that conclusion.  However the ultimate solution has not be achieved.  The code here only works if you add multi page to multi books.  The original code I posted had various combinations if single book, multi page,  multi book single page, single book single page etc..
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 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

18 Experts available now in Live!

Get 1:1 Help Now