locked
Transaction query RRS feed

  • Question

  • Is it possible to create a transaction query that will a new record to a parent table and then a child table while placing the primary field value (number) into the linked field of the child table?  If so, can you show me?
    Sunday, February 25, 2018 5:12 AM

All replies

  • No. There is no such thing as a "Transaction Query" in Access.

    If you want a transaction, you will have to write VBA. Look up BeginTrans in the help file.

    To retrieve the autonumber from the parent table, you can use the LastModified technique: https://social.msdn.microsoft.com/Forums/office/en-US/91deb351-7b51-440d-b878-d79ace09d858/get-autonumber-after-addnew-using-dao?forum=accessdev


    -Tom. Microsoft Access MVP

    Sunday, February 25, 2018 5:49 AM
  • You'll find an example of code which executes two SQL statements within a transaction to insert a row into a referenced  table and a referencing in Families.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes the following function:

    Public Function FamilyTransaction(strFamilyName As String, strFirstname As String)

        On Error GoTo Err_Handler
        
        Dim strSQL As String
        Dim strCriteria As String
        
        DBEngine.BeginTrans
           
        ' insert row into Families table if doesn't exist
        If IsNull(DLookup("FamilyName", "Families", "FamilyName = """ & strFamilyName & """")) Then
            strSQL = "INSERT INTO Families(FamilyName) " & _
                 "VALUES(""" & strFamilyName & """)"
            CurrentDb.Execute strSQL, dbFailOnError
        End If
       
       ' insert matching row into FamilyMembers table
        strSQL = "INSERT INTO FamilyMembers(FamilyName, Firstname)  " & _
            "VALUES(""" & strFamilyName & """,""" & strFirstname & """)"
        CurrentDb.Execute strSQL, dbFailOnError
        
        DBEngine.CommitTrans
        
    Exit_Here:
        Exit Function
        
    Err_Handler:
        MsgBox Err.Description
        DBEngine.Rollback
        Resume Exit_Here
        
    End Function

    For the purposes of demonstrating the methodology, the above example uses the text columns FamilyName as the 'natural' primary key of the referenced table, and the corresponding foreign key in the referencing table.  In real life this would not be done of course as names can legitimately be duplicated.  A method using 'surrogate' numeric keys is illustrated in the CopyTree demo in the same OneDrive folder.  This copies rows from a referenced  table and two referencing tables in a three level hierarchy rather than two.  The trick here is not to allow the system to insert the next number in sequence into the autonumber key of the referenced  table, but to compute the next number in sequence and then insert it as a literal.  The example is a deliberately absurd one which copies a country, its regions and their cities, but the methodology is sound, and is effected by the following function, which does not execute the SQL statements in a transaction per se, however:

    Public Function CopyCountryTree(lngCountryID As Long)

        Dim strSQL As String
        Dim lngNewCountryID As Long
        Dim lngNewRegionID As Long
        Dim lngNewCityID As Long
        Dim rstRegions As DAO.Recordset
        
        ' get next CountryID value
        lngNewCountryID = DMax("CountryID", "Countries") + 1
        
        'insert new row into Countries
        strSQL = "INSERT INTO Countries(CountryID,Country) " & _
            "SELECT " & lngNewCountryID & ",Country " & _
            "FROM Countries WHERE CountryID = " & lngCountryID
        CurrentDb.Execute strSQL, dbFailOnError
        
        ' get Max RegionID value
        lngNewRegionID = DMax("RegionID", "Regions")
        
        strSQL = "SELECT Region FROM Regions WHERE CountryID = " & lngCountryID
        Set rstRegions = CurrentDb.OpenRecordset(strSQL)
        
        ' loop through set of Region values in country being copied
        ' and insert a new row into Regions, incrementing value of RegionID by 1
        ' at each iteration of the loop
        With rstRegions
            .MoveLast
            .MoveFirst
            Do While Not .EOF
                lngNewRegionID = lngNewRegionID + 1
                strSQL = "INSERT INTO Regions(RegionID,Region,CountryID) " & _
                    "VALUES(" & lngNewRegionID & ",""" & .Fields("Region") & """," & lngNewCountryID & ")"
                CurrentDb.Execute strSQL, dbFailOnError
               .MoveNext
            Loop
        End With
        
         ' get Max CityID value
        lngNewCityID = DMax("CityID", "Cities")
       
        strSQL = "SELECT R1.RegionID, City FROM Regions AS R1,Cities AS C1 " & _
            "WHERE CountryID = " & lngNewCountryID & _
            " AND EXISTS" & _
                "(SELECT * " & _
                "FROM (Cities AS C2 INNER JOIN Regions  AS R2 " & _
                "ON C2.RegionID = R2.RegionID) " & _
                "INNER JOIN Countries " & _
                "ON Countries.CountryID = R2.CountryID " & _
                "WHERE Countries.CountryID = " & lngCountryID & _
                "AND R2.Region = R1.Region " & _
                "AND C2.CityID = C1.CityID)"
              
        Set rstRegions = CurrentDb.OpenRecordset(strSQL)
        
        ' loop through set of Region/City values in country being copied
        ' and insert a new row into Cities, incementing value of CityID by 1
        ' at each iteration of the loop
        With rstRegions
            .MoveLast
            .MoveFirst
            Do While Not .EOF
                lngNewCityID = lngNewCityID + 1
                strSQL = "INSERT INTO Cities(CityID,City,RegionID) " & _
                    "VALUES(" & lngNewCityID & ",""" & .Fields("City") & """," & .Fields("RegionID") & ")"
                CurrentDb.Execute strSQL, dbFailOnError
               .MoveNext
            Loop
        End With
        
    End Function

    With a simple two level hierarchy the code to insert rows into the Cities table can be ignored of course, and if it merely a case of inserting one row into each table, it would not be necessary to iterate through a loop to insert the row into the referencing table.  The code would consequently be much simpler and easily executed within a transaction, as in my first example.  The key thing is that the key of the referenced table is inserted as a literal, obtained with the expression lngNewCountryID = DMax("CountryID", "Countries") + 1, which can thus be the same value for the foreign key when inserting the row into the refencing table

    Ken Sheridan, Stafford, England

    Sunday, February 25, 2018 6:18 PM