DAO Recordset Dynamic Update - How to Order by Timestamp prior to Update
How can I get this dynamic update to Order by TimeStamp prior to updating.
I have a Many to Many dynamic update that takes information from Table 1
and information from Table 2 for each instance of Table 1 Current and each
instance of Table 2 where Table1Key = Table2Key then updates an XML procedural page.
The following code works fine to update however I need to make sure the update is being done in order by the timestamp field located in table 2
in other words
for each Table1.current.key the Table2.URL must update by Table2.Timestamp
ascending to the XML file
Dim GoTu as String
Dim CheckURL as String
Dim rstdoc as DAO.Recordset
Dim rstAssess as DAO.Recordset
Set rstdoc = CurrentDB.OpenRecordset("T
Set rstAssess = CurrentDB.OpenRecordset("T
With rstdoc ' --------------------------
.MoveFirst
Do While Not .EOF
If rstdoc!Current = True Then '-------------------------
record in table 1
With rstAssess ' --------------------------
Do While Not .EOF
GetKey = rstdoc!VBKey '-------------------------
record in table 2 that
matches table 1
If rstAssess!VBKey = GetKey Then
GoTu = rstAssess!AssessmentURL
CheckURL = Left(GoTu, 4)
' ------------Check to see that it is a valid URL
If CheckURL = "http" Then
' -----------------------Ver
If rstAssess!Updated = False Then
'--------------Allow an XML page to execute
based on the URL passed
ShellExecute 0, "open", GoTu, Chr$(0), "", False *
.Edit '------------------Change the Update flag to True
rstAssess!Updated = True
.Update
End If
End If
End If
.MoveNext
Loop
End With
End If
.MoveNext
Loop
End With
rstAssess.Close
rstdoc.Close
I have a Many to Many dynamic update that takes information from Table 1
and information from Table 2 for each instance of Table 1 Current and each
instance of Table 2 where Table1Key = Table2Key then updates an XML procedural page.
The following code works fine to update however I need to make sure the update is being done in order by the timestamp field located in table 2
in other words
for each Table1.current.key the Table2.URL must update by Table2.Timestamp
ascending to the XML file
Dim GoTu as String
Dim CheckURL as String
Dim rstdoc as DAO.Recordset
Dim rstAssess as DAO.Recordset
Set rstdoc = CurrentDB.OpenRecordset("T
Set rstAssess = CurrentDB.OpenRecordset("T
With rstdoc ' --------------------------
.MoveFirst
Do While Not .EOF
If rstdoc!Current = True Then '-------------------------
record in table 1
With rstAssess ' --------------------------
Do While Not .EOF
GetKey = rstdoc!VBKey '-------------------------
record in table 2 that
matches table 1
If rstAssess!VBKey = GetKey Then
GoTu = rstAssess!AssessmentURL
CheckURL = Left(GoTu, 4)
' ------------Check to see that it is a valid URL
If CheckURL = "http" Then
' -----------------------Ver
If rstAssess!Updated = False Then
'--------------Allow an XML page to execute
based on the URL passed
ShellExecute 0, "open", GoTu, Chr$(0), "", False *
.Edit '------------------Change the Update flag to True
rstAssess!Updated = True
.Update
End If
End If
End If
.MoveNext
Loop
End With
End If
.MoveNext
Loop
End With
rstAssess.Close
rstdoc.Close
ASKER
The updates are creating assessments, The XML link does this assessment automatically based on the parameters passed in the URL. If the assessment is not done in the order that they were selected, it could Over Assess or Under assess.
For instance if the user choses to clear all previous assessments, then enters new ones, the order that they are updated in becomes critical.
For instance if the user choses to clear all previous assessments, then enters new ones, the order that they are updated in becomes critical.
the problem that you have is that you are opening both recordsets by simpley opening the table, with no ordering at all.
try this change:
Dim GoTu as String
Dim CheckURL as String
Dim rstdoc as DAO.Recordset
Dim rstAssess as DAO.Recordset
dim strAssess as String
Set rstdoc = CurrentDB.OpenRecordset("T
With rstdoc ' --------------------------
.MoveFirst
Do While Not .EOF
If rstdoc!Current = True Then '-------------------------
strAssess = "Select Updated from Table2 where VBKey = '" & rstDoc!VBKey & "' ORDER BY Table2.Timestamp"
Set rstAssess = CurrentDB.OpenRecordset( strAssess,dbOpenDynaset)
' this will only return the records for the desired VBKey
With rstAssess ' --------------------------
Do While Not .EOF
GoTu = rstAssess!AssessmentURL
CheckURL = Left(GoTu, 4)
' ------------Check to see that it is a valid URL
If CheckURL = "http" Then
' -----------------------Ver
If rstAssess!Updated = False Then
'--------------Allow an XML page to execute
based on the URL passed
ShellExecute 0, "open", GoTu, Chr$(0), "", False *
.Edit '------------------Change the Update flag to True
rstAssess!Updated = True
.Update
End If
End If
.MoveNext
Loop
End With
End If
.MoveNext
Loop
End With
rstAssess.Close
rstdoc.Close
note that the rstAssess is only opened after a specific rstDoc record has been accessed, so the rstAssess will only ever hold the records that are relevant to the current VBKey, from rstDoc. The recordset also only pulss the Updated field, rather than ALL of the fields, so the performance should be better. It sould be further imporved by only pulling those records for which Update is currently FALSE, since those are the only records whose values you are Updating to True.
AW
try this change:
Dim GoTu as String
Dim CheckURL as String
Dim rstdoc as DAO.Recordset
Dim rstAssess as DAO.Recordset
dim strAssess as String
Set rstdoc = CurrentDB.OpenRecordset("T
With rstdoc ' --------------------------
.MoveFirst
Do While Not .EOF
If rstdoc!Current = True Then '-------------------------
strAssess = "Select Updated from Table2 where VBKey = '" & rstDoc!VBKey & "' ORDER BY Table2.Timestamp"
Set rstAssess = CurrentDB.OpenRecordset( strAssess,dbOpenDynaset)
' this will only return the records for the desired VBKey
With rstAssess ' --------------------------
Do While Not .EOF
GoTu = rstAssess!AssessmentURL
CheckURL = Left(GoTu, 4)
' ------------Check to see that it is a valid URL
If CheckURL = "http" Then
' -----------------------Ver
If rstAssess!Updated = False Then
'--------------Allow an XML page to execute
based on the URL passed
ShellExecute 0, "open", GoTu, Chr$(0), "", False *
.Edit '------------------Change the Update flag to True
rstAssess!Updated = True
.Update
End If
End If
.MoveNext
Loop
End With
End If
.MoveNext
Loop
End With
rstAssess.Close
rstdoc.Close
note that the rstAssess is only opened after a specific rstDoc record has been accessed, so the rstAssess will only ever hold the records that are relevant to the current VBKey, from rstDoc. The recordset also only pulss the Updated field, rather than ALL of the fields, so the performance should be better. It sould be further imporved by only pulling those records for which Update is currently FALSE, since those are the only records whose values you are Updating to True.
AW
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured I would have to base this on a query, I was concerned with the processing speed by adding the extra step, This is a great solution - thanks
I also came up with basing the rstassess on selectquerytable1 instead of directly from table1. - basically the same idea, but yours is better because it limits the result set.
I also came up with basing the rstassess on selectquerytable1 instead of directly from table1. - basically the same idea, but yours is better because it limits the result set.
Glad to be of assistance. May all your days get brighter and brighter.
Why do you think that you must do the update in a specific order?
AW