Update Data in multiple Tables based on query result.

[tobypsl]

I am tryting to update a field that appears in multiple tables within my DB. These tables all share an identical structure (which I gather is less than ideal DB design). The DB is a commercial product and I use MS Access Linked Tables to generate the reports I use. I cannot change the underlying structure.

So to query all of the data in these tables I first construct a temporary table (using the code I have pasted below)- this compiles all the data from seperate tables into one single table with an additional field that records the name of the parent table. I then query the temp table. The query will give me results similar to below:

Job ObjectID Status
J000300 ABC1 H
J000300 CAB1 H
J000297 BBB2 H
J000297 CCC2 H
J000295 DDD3 H

The Job field is actually the table from which the other data came - and is the name of the tables I wish to update. So for the query above I would like to update the status for records in tables J000300, J000297 and J000295 from H to B.

Changing the status for the records in the query could be done with an update query. However those changes would not cascade through to the parent tables (J000300, J000297, J000295).

I guess what I am looking for is a piece of code that will change records in the tables identified in the query. If that makes sense.

The code used to build the temp table is as follows:

Private Sub Command0_Click()
Dim db As Database
Dim rsRjobs As Recordset
Dim rsRapps As Recordset
Dim LengthofUnionSQL As Long
Dim sql As String
Dim UnionSQL As String
Set db = CurrentDb
Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot)
Do While Not rsRjobs.EOF
UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, Status, """ & rsRjobs!JobID & """ AS Job from [" & rsRjobs!JobID & "] Union "
rsRjobs.MoveNext
Loop
'following two lines are to remove the trailing word Union from the string unionsql
LengthofUnionSQL = Len(UnionSQL)
UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7)
' Now variable Unionsql will hold the value something like
' Select ObjectID, SearchNo, DateSearched, Consultant from J000145
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147
MsgBox UnionSQL


Set db = CurrentDb
Dim rsUnionquery As Recordset
Dim rstemp As Recordset
Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges)
Set rsUnionquery = db.OpenRecordset(UnionSQL)
Do While Not rsUnionquery.EOF
rstemp.AddNew
rstemp!ObjectID = rsUnionquery!ObjectID
rstemp!SearchNo = rsUnionquery!SearchNo
rstemp!DateSearched = rsUnionquery!DateSearched
rstemp!Consultant = rsUnionquery!Consultant
rstemp!Status = rsUnionquery!Status
rstemp!Job = rsUnionquery!Job

rstemp.Update
rsUnionquery.MoveNext
Loop

End Sub

 

[Golom]

Maybe something like this

Private Sub Command0_Click()
    Dim db                          As DAO.Database
    Dim rsRjobs                     As DAO.Recordset
    Dim rsTemp                      As DAO.Recordset
    Dim SQL                         As String
    Dim UnionSQL                    As String
    
    Set db = CurrentDb
    Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot)
    Do While Not rsRjobs.EOF
        UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, Status, """ & _
                   rsRjobs!JobID & """ AS Job from [" & rsRjobs!JobID & "] Union "
        rsRjobs.MoveNext
    Loop
    rsRjobs.Close
    Set rsRjobs = Nothing
    
    [blue]'Remove the trailing word Union from the string UnionSQL[/blue]
    UnionSQL = Left$(UnionSQL, Len(UnionSQL) - 7)
    [blue]' Now variable Unionsql will hold the value something like[/blue]
    [blue]' Select ObjectID, SearchNo, DateSearched, Consultant, Status, "J000145" As Job from J000145[/blue]
    [blue]' Union Select ObjectID, SearchNo, DateSearched, Consultant, Status, "J000146" As Job from J000146[/blue]
    [blue]' Union Select ObjectID, SearchNo, DateSearched, Consultant, Status, "J000147" As Job from J000147[/blue]
    
    [blue]' Now build the TEMP table[/blue]
    db.Execute ("INSERT INTO Temp (ObjectID, SearchNo, DateSearched, Consultant, Status, Job) " & _
                "Select ObjectID, SearchNo, DateSearched, Consultant, Status, Job " & _
                "FROM (" & UnionSQL & ")")
                
    [blue]' Update Records in the tables[/blue]
    Set rsTemp = db.OpenRecordset("Select * From Temp", dbOpenSnapshot)
    Do Until rsTemp.EOF
        SQL = "UPDATE " & rsTemp![Job] & " SET " & rsTemp![ObjectID] & " = 'B' " & _
              "WHERE " & rsTemp![ObjectID] & " = '" & rsTemp![Status] & "'"
        db.Execute SQL
        rsTemp.MoveNext
    Loop
    rsTemp.Close
    Set rsTemp = Nothing
End Sub

It is not evident to me that the UnionSQL recordset contains the information about what the new value of the field should be. Clearly "Status" contains the current value of the field "ObjectID" but the assertion that it should be changed to "B" occurs only in your description.

Also a bit confusing are the two sentences

I would like to update the status for records in tables J000300, J000297 and J000295 from H to B

and

those changes would not cascade through to the parent tables (J000300, J000297, J000295).

The first seems to say that you want those tables updated and the second says that you don't. Which is it?

 

[TheAceMan1]

How are ya tobypsl . . .

Using your tblTemp, consider parsing thru a recordset with a sum SQL source (returns single record Jobs). The loop replaces [blue]Job[/blue] proper in an [blue]Update SQL[/blue]. An included [blue]subquery SQL[/blue] using the [purple]In[/purple] clause, selects the proper records to update. Have a look:

[blue]   Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   
   [green]'Setup Recordset, Single Jobs Only![/green]
   SQL = "SELECT Job " & _
         "FROM tblTemp " & _
         "GROUP BY Job;"
   Set [purple][b]rstName[/b][/purple] = db.OpenRecordset(SQL, dbOpenDynaset)
   
   [green]'Parse thru tables & update status to "B"[/green]
   Do Until [purple][b]rstName[/b][/purple].EOF
      SQL = "UPDATE " & [purple][b]rstName[/b][/purple]!Job & " " & _
            "SET Status = 'B' " & _
            "WHERE ([ObjectID] In (SELECT OBjectID " & _
                              "FROM tbltemp " & _
                              "WHERE [Job] = '" & [purple][b]rstName[/b][/purple]!Job & "'));"
      rstName.MoveNext
   Loop
   
   Set rstName = Nothing
   Set db = Nothing[/blue]

Note [blue]tblTemp[/blue] appears in two places in the code. Replace it with your actual TempTableName!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[tobypsl]

Hi Golom

I want to update the data in the tables J000nnn.

The data I am querying is not held in those tables though. The J000nnn tables (which all have an identical structure) have been joined into one bigger Temporary table - using the code I posted. The point I am making is that an update query will update the temporary table - but next time the temporary table is created it will hold the original data as the tables where that data comes from (the J000nnn tables) will not have been updated.

Hi AceMan1

this looks like well worth a try. I'll set up a mock of the DB tonight and give it a go and then get back to you. Thanks for the code !

 

[TheAceMan1]

tobypsl . . .

Forgot the all important line in [purple]purple![/purple]

[blue]   Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   
   'Setup Recordset, Single Jobs Only!
   SQL = "SELECT Job " & _
         "FROM tblTemp " & _
         "GROUP BY Job;"
   Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
   
   'Parse thru tables & update status to "B"
   Do Until rstName.EOF
      SQL = "UPDATE " & rstName!Job & " " & _
            "SET Status = 'B' " & _
            "WHERE ([ObjectID] In (SELECT OBjectID " & _
                                  "FROM tbltemp " & _
                                  "WHERE [Job] = '" & rstName!Job & "'));"
      [purple][b]db.Execute SQL, dbFailOnError[/b][/purple]
      rstName.MoveNext
   Loop
   
   Set rstName = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[tobypsl]

Hey AceMan - you're a genius, this seems to be working when I just tested it. Need to try it on the full DB where the J000nnn tables are linked rather than local, but I don't see any problems.

I had to insert an addition to the WHERE clause into the code as it was changing the status of all records in the table rather than just those with a certain status.

(in case you are wondering the status codes I have used in this post are arbitrary just for testing purposes - they are probably different from those I originally mentioned)

Thanks for you r help !!

code, inclusive of very minor mod, as follows for anyone having a similar need in future:

Private Sub Command2_Click()
Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String

Set db = CurrentDb

'Setup Recordset, Single Jobs Only!
SQL = "SELECT Job " & _
"FROM tempallreview " & _
"GROUP BY Job;"
Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)

'Parse thru tables & update status to "B"
Do Until rstName.EOF
SQL = "UPDATE " & rstName!Job & " " & _
"SET Status = 'B' " & _
"WHERE ([ObjectID] In (SELECT OBjectID " & _
"FROM tempallreview " & _
"WHERE [Job] = '" & rstName!Job & "' AND [Status] = 'R'));"
db.Execute SQL, dbFailOnError
rstName.MoveNext
Loop

Set rstName = Nothing
Set db = Nothing
End Sub

 

[tobypsl]

I have saved this code as a public function. I use the above piece of code to change records that have a status of R to a status of B.

If I wanted to also use the code to change records that have a status of, say, G to a status of N is there a means of defining this in the () part of the public function ? thus saving the use of a seperate function.

 

[PHV]

Sub yourSubName(oldStatus As String, newStatus As String)
  Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
   Set db = CurrentDb
   'Setup Recordset, Single Jobs Only!
   SQL = "SELECT Job " & _
         "FROM tempallreview " & _
         "GROUP BY Job;"
   Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
   'Parse thru tables & update status to newStatus
   Do Until rstName.EOF
      SQL = "UPDATE " & rstName!Job & " " & _
            "SET Status = '" & newStatus & "' " & _
            "WHERE ([ObjectID] In (SELECT OBjectID " & _
                                  "FROM tempallreview " & _
                                  "WHERE [Job] = '" & rstName!Job & "' AND [Status] = '" & oldStatus & "'));"
      db.Execute SQL, dbFailOnError
      rstName.MoveNext
   Loop
   rstName.Close
   Set rstName = Nothing
   Set db = Nothing
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886