wlwebb
asked on
VB - Looping to Add records with a DAO instruction to test if record already exists
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?
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<off topic> You're up awfully late, cap :) (or very, very early)
ASKER
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
Note, even the recBkNbr and RecBkPg are indexed these are actually ID fields that I just used the LongNumber as the Key
ASKER
PS..... I don't sleep very much.......
(very, very early) ;-)
ASKER
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Scott
Will changing that code change how it tests with each looping in terms of speed ?????
Will changing that code change how it tests with each looping in terms of speed ?????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Will changing that code change how it tests with each looping in terms of speed ?????
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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..
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..