Re: Updating only changed records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You could put a timestamp on each record when you run your query, and again
when the data's updated through the form, and base your queries on those
timestamp values. However, you really haven't given us enough details to go
on.

What exactly are the queries doing? Is it really necessary to run Append
queries, or can you simply update in place? In my November, 2003 "Access
Answers" column in Pinnacle Publication's Smart Access, I show how to build
a query that will update matching records, and add records that don't match.
You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tony Williams" <tw@xxxxxxxxxxx> wrote in message
news:u8q6PwHFHHA.3776@xxxxxxxxxxxxxxxxxxxxxxx
I have a procedure that runs three queries, 2 update queries and 1 append
query. These queries need to be run before a report is produced from the
database. However it is possible that the user will edit records after the
procedure is run and this will mean that the procedure will have to be
rerun to recreate the updated report. However because this adds duplicate
records from the append query. How can I change my code so that the queries
will only run on records that have been changed since it was last run? Here
is my code:
Private Sub cmdupdatedata_Click()
On Error GoTo Err_cmdupdatedata_Click
'Step1 Update Euro records
Dim stDocName1 As String
stDocName1 = "qryYTDUpdateIrisha"

'Step2 Update UK data
Dim stDocName2 As String
stDocName2 = "qryYTDUpdatea"

'Step3 update UK data with Euro data
Dim stDocName3 As String
stDocName3 = "qryeurovaluea"

Dim Msgstr1 As String
Dim Msgstr2 As String
Dim Msgstr3 As String
Msgstr1 = "You are about to:" & vbCrLf _
& "1. Update the records for the Euro zone companies by calculating the
quarterly data from the YTD data" & vbCrLf _
& "2. Update the records for the UK companies " _
& "by calculating the quarterly data from the YTD data" & vbCrLf _
& "3. Add the Euro company data to the UK data" & vbCrLf _
& "(HAVE YOU CREATED THE NEW EURO RATE RECORD FOR THIS QUARTER?)" &
vbCrLf & vbCrLf _
& "Are you sure you want to do all of this?"
Msgstr2 = "To update the data you must enter the current quarter!"
Msgstr3 = "To update the data you must enter the previous quarter!"

Me.txtqtr2.SetFocus
If Nz(Me.txtqtr2.Text) <> "" Then
Me.txtqtr3.SetFocus
If Nz(Me.txtqtr3.Text) <> "" Then
If MsgBox(Msgstr1, vbYesNo, "Updating all UK and Euro data") = vbNo
Then
DoCmd.Close
Else
DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.OpenQuery stDocName3, acNormal, acEdit
End If
Else
MsgBox Msgstr3, vbOKOnly, "Missing Previous Quarter"
Me.txtqtr3.SetFocus
End If
Else
MsgBox Msgstr2, vbOKOnly, "Missing Current Quarter"
Me.txtqtr2.SetFocus
End If

Exit_cmdupdatedata_Click:
Exit Sub

Err_cmdupdatedata_Click:
MsgBox Err.Description
Resume Exit_cmdupdatedata_Click
End Sub


Thanks for any help
Tony



.

Relevant Pages

  • Re: As a generale rule - Query or VBA?
    ... But I end up with loads of queries with long names like: ... Stored queries have a precompiled query plan. ... For a code jockey the SQL string is tempting. ...
    (comp.databases.ms-access)
  • Updating only changed records
    ... I have a procedure that runs three queries, 2 update queries and 1 append ... Dim stDocName1 As String ... Dim Msgstr2 As String ...
    (microsoft.public.access.formscoding)
  • Re: Make Multiple Tables via Query
    ... Execute method with a select query. ... Select queries contain a SELECT statement and can return ... Dim strExcelFileName As String ...
    (microsoft.public.access.queries)
  • Re: Data type mismatch in criteria expression. (Error 3464)
    ... I keep getting the Error 3464 when I run a particular query. ... This query is based on two other queries. ... Public Function FirstNumber(ByVal StringIn As String) As String ... Dim strChar As String ...
    (microsoft.public.access.formscoding)
  • Re: What do I need?
    ... Dim strFirst As String, strSecond As String, strThird As String ... Set rst = New ADODB.Recordset ... who have not audited a site on the occasions of its last three audits queries ... i have the query but i only want to assign certain values to variables. ...
    (microsoft.public.access.queries)