Solved

Access 2007 How to insert into 3 Tables (one to many relationships) and maintain referential integrity

Posted on 2009-07-07
3
208 Views
Last Modified: 2012-08-13
I have 3 tables with the following relationship: T1 one-to-many on T2 one-to-many on T3. Effectively T1 and T2 are groupings on T3 which holds Journal Entry Postings such as the Amount, the Currency and whether it's a debit or credit.

Now I'm building a mechanism to generate a Journal reversal, reading from the top level. I will effectively be inserting one new record in T1, several in T2 and more in T3 but the records in T3 will have the Debit/Credit flag switched.

Each Table has a primary Key (T1_ID, T2_ID and T3_ID, say) which is an auto-number and T2 and T3 hold single foriegn keys to link them to the 'Parents' (T1 and T2) respectively.

What I want is a nice way to update all 3 tables. The problem I have got is the T1 (Holds transaction ID and Name ... where this name is unique) But T2 and T3 only have the ID (auto-number) as the unique field.

I had thought about doing the following:
1 Grab all records and reverse the debit - credit flag.
2 Load it to my data class structure; see the code.
3 Then insert into T3
4 Read the Primary Key from T3 (the auto-number) and set foriegn key
5 Insert into T2
6 Read Primary Key from T2 and set foriegn key
7 Insert into T3

The problem is how do i keep my tables locked while I do this. I do not believe I can use a Begin Trans/Commit as I would not be able to read the ID Auto-number until I commit. There will be more than one user so how can I update and maintain concurrency?

Have spoken to a colleague who mentioned that I should investigate "Select for Insert" to lock my tables but don't know if Access supports anything like this.

Comments Welcome

Jon
Public Function ReverseTransaction() As Boolean
 
Dim strSQL As String
Dim strSQLWhere As String
Dim strTitle As String
Dim strTxt As String
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim oJERecord As clsJERecord
Dim oJDRecord As clsJDRecord
Dim lngRowsInserted As Long
Dim strTransName As String
 
On Error GoTo ErrorHandler
 
ReverseTransaction = False
'-----------------------------------------------------------------------------------------------------
'------ get records to reverse and reverse Debit Credit flags ----------------------------------------
'-----------------------------------------------------------------------------------------------------
    strSQL = ""
    strSQL = " SELECT"
    strSQL = strSQL & " JE_ID"
    strSQL = strSQL & ", JE_Transaction"
    strSQL = strSQL & ", JE_Date"
    strSQL = strSQL & ", JE_Name"
    strSQL = strSQL & ", JE_Type"
    strSQL = strSQL & ", JE_Position_Ref"
    strSQL = strSQL & ", JE_Component_Ref"
    'strSQL = strSQL & ", JE_Comments"
    strSQL = strSQL & ", JE_Auto_Reverse"
    strSQL = strSQL & " FROM [Journal Entries]"
    strSQL = strSQL & " WHERE JE_Transaction= " & Me.Transaction.ID
    strSQL = strSQL & ";"
    
    'get Journal entry
    Set rs1 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbForwardOnly, dbPessimistic)
 
    strSQL = ""
    strSQL = " SELECT"
    strSQL = strSQL & " JD_ID"
    strSQL = strSQL & ", JD_JournalEntry"
    strSQL = strSQL & ", JD_Account"
    strSQL = strSQL & ", JD_Currency"
    strSQL = strSQL & ", IIf([JD_CreditDebit]=""C"",""D"",""C"") AS CreditDebit" 'reverse entry here
    strSQL = strSQL & ", JD_SourceAmount"
    strSQL = strSQL & ", JD_BaseAmount"
    strSQL = strSQL & ", JD_FXRate"
    strSQL = strSQL & " FROM [Journal Details]"
 
Do Until rs1.EOF
    'load entries and details to data clases
    Set oJERecord = New clsJERecord
 
    oJERecord.ID = CLng(rs1("JE_ID") + 0)
    oJERecord.Transaction = CLng(rs1("JE_Transaction") + 0)
    oJERecord.rDate = CDate(rs1("JE_Date") + 0)
    oJERecord.Name = Trim$(rs1("JE_Name") & "")
    oJERecord.rType = Trim$(rs1("JE_Type") & "")
    oJERecord.ISIN = CLng(rs1("JE_Position_Ref") + 0)
    oJERecord.ComponentCode = Trim$(rs1("JE_Component_Ref") & "")
    'oJERecord.Comments = Trim$(rs1("JE_Comments") & "")
    oJERecord.AutoReverse = CBool(rs1("JE_Auto_Reverse"))
    
    strSQLWhere = strSQLWhere & " WHERE JD_JournalEntry= " & CStr(oJERecord.ID)
    strSQLWhere = strSQLWhere & ";"
    
    'get journal details
    Set rs2 = m_dbs.OpenRecordset(strSQL & strSQLWhere, dbOpenDynaset, dbForwardOnly, dbPessimistic)
    
    Do Until rs2.EOF
        Set oJDRecord = New clsJDRecord
        
        oJDRecord.ID = CLng(rs2("JD_ID") + 0)
        oJDRecord.JE_ID = CLng(rs2("JD_JournalEntry") + 0)
        oJDRecord.Account = Trim$(rs2("JD_Account") & "")
        oJDRecord.Ccy = Trim$(rs2("JD_Currency") & "")
        oJDRecord.CreditDebit = Trim$(rs2("CreditDebit") & "")
        oJDRecord.SourceAmount = CDbl(rs2("JD_SourceAmount") + 0)
        oJDRecord.FXRate = CDbl(rs2("JD_BaseAmount") + 0)
        oJDRecord.BaseAmount = CDbl(rs2("JD_FXRate") + 0)
        oJERecord.JDRecords.Add oJDRecord
        
        Set oJDRecord = Nothing
        rs2.MoveNext
    Loop
    If oJERecord.JDRecords.Count > 0 Then
        Me.Transaction.JERecords.Add oJERecord
    End If
    Set oJERecord = Nothing
    rs1.MoveNext
Loop
 
If Not rs1 Is Nothing Then Set rs1 = Nothing
If Not rs2 Is Nothing Then Set rs2 = Nothing
 
If Me.Transaction.JERecords.Count = 0 Then Exit Function
 
'-----------------------------------------------------------------------------------------------------
'now write records to Transaction, Journal Entry and Journal Detail tables repectively
'-----------------------------------------------------------------------------------------------------
 
strSQL = " INSERT INTO "
strSQL = strSQL & " Transactions"
strSQL = strSQL & " SET T_Trans_Name = ""Reversal of " & Me.Transaction.Name & """"
strSQL = strSQL & ", T_Comments = " & Me.Transaction.Comment
strSQL = strSQL & ";"

Open in new window

0
Comment
Question by:Guernsey_jon
  • 2
3 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 24792798
You have the option to use dbdenywrite in the openrecordset command which prevents other users from modifying or adding records to the table. I think this will mean that you would have to do your own insertions and modifications through the recordset rather than through sql commands.
0
 

Author Comment

by:Guernsey_jon
ID: 24792923
The dbdenywrite will be really useful. Yes had been thinking that a DAO recordset might be the way to go. Giving it a try now, gona try and open a recordset with a query joining all 3 tables and then try the rs.Addnew and see if I can unload data onto it ... will get back if it works ... Many thanks for the reply

Jon
0
 

Accepted Solution

by:
Guernsey_jon earned 0 total points
ID: 24806069
It was a painful exercise, but finally I have something that works:

Recap:
I have a bound form that uses a nested subform to edit my three tables ; Transaction (1 to M on) [Journal Entries] (1 to M on) [Journal Detail]

Now the form has a Reversal button to reverse the postings across all tables and toggling the Debit/Credit flag at leaf level.

The Transaction table has an Auto-number as key (as do they all) but the Trans name is also unique so no probs there.

Now the middle table only has the Auto-number as a unique ID which gets generated on Insert .... so how to read it and know its the right one???

First I tried to Lock my tables ... eg ...Set rs1 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbDenyWrite). But the form is bound to the tables I what to lock!

So I set the forms to 'No Lock' in code ... no go!

So I created a hidden form that acted as a form handler and created an Instance of my original form.

This seem to be working after I found that I had to use the Activate event (interesting that it seems to fire twice on a load hence the flag m_blnActivated )

Private Sub Form_Activate()
On Error GoTo ErrorHandler
    Me.Visible = False
    If Not m_blnActivated Then
        'Set m_ofrmJ = New Form_frmJournals
        'm_ofrmJ.Owner = Me
        'm_ofrmJ.Visible = True
        DoCmd.OpenForm "frmJournals", acNormal, "", "", acFormPropertySettings, acWindowNormal
       
        Me.Visible = False
        m_blnActivated = True
    End If
ExitCode:
    Exit Sub
ErrorHandler:
    DisplayError Err, Me.Name & ".Form_Activate"
    Resume ExitCode
End Sub


The original idea was to set my instance to nothing just before I got my locks on my three tables but 'Set myFormObj = nothing' did not close/kill the form.

Finally I found that 'docmd.Close acForm, myformName' would close the form but still I could not lock my tables and got Runtime Error 3008


So finally I figure as this is an append I should do the following:

Use three recordsets set to dbOptimistic and keep them open until I complete the update.
Use the transaction name to get the Transaction ID after the insert on that table.
Use the Journal Entry Comments field to enter a unique (ish) value but use Environ$(Username) in there so when I read the JE_ID I use and order by Desc on JE_ID so I get the lattest record inserted by that person!!! This works for me and no Locks required.
Finally insert the Journal details passing the JE_ID as a foreign key


Code below comments welcome
Public Function ReverseTransaction() As Boolean
Dim strSQL As String
Dim strSQLWhere As String
Dim strTitle As String
Dim strTxt As String
Dim rs0 As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim oJERecord As clsJERecord
Dim oJDRecord As clsJDRecord
Dim strTransName As String
Dim strJEComment As String
 
On Error GoTo ErrorHandler
 
ReverseTransaction = False
'-----------------------------------------------------------------------------------------------------
'------ get records to reverse and reverse Debit Credit flags ----------------------------------------
'-----------------------------------------------------------------------------------------------------
    strSQL = ""
    strSQL = " SELECT"
    strSQL = strSQL & " JE_ID"
    strSQL = strSQL & ", JE_Transaction"
    strSQL = strSQL & ", JE_Date"
    strSQL = strSQL & ", JE_Name"
    strSQL = strSQL & ", JE_Type"
    strSQL = strSQL & ", JE_Position_Ref"
    strSQL = strSQL & ", JE_Component_Ref"
    strSQL = strSQL & ", JE_Comments"
    strSQL = strSQL & ", JE_Auto_Reverse"
    strSQL = strSQL & " FROM [Journal Entries]"
    strSQL = strSQL & " WHERE JE_Transaction= " & Me.Transaction.ID
    strSQL = strSQL & ";"
    
    'get Journal entry
    Set rs1 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbForwardOnly, dbPessimistic)
    strSQL = ""
    strSQL = " SELECT"
    strSQL = strSQL & " JD_ID"
    strSQL = strSQL & ", JD_JournalEntry"
    strSQL = strSQL & ", JD_Account"
    strSQL = strSQL & ", JD_Currency"
    strSQL = strSQL & ", IIf([JD_CreditDebit]=""C"",""D"",""C"") AS CreditDebit" 'reverse entry here
    strSQL = strSQL & ", JD_SourceAmount"
    strSQL = strSQL & ", JD_BaseAmount"
    strSQL = strSQL & ", JD_FXRate"
    strSQL = strSQL & " FROM [Journal Details]"
    Do Until rs1.EOF
        'load entries and details to data clases
        Set oJERecord = New clsJERecord
    
        oJERecord.ID = CLng(rs1("JE_ID") + 0)
        oJERecord.Transaction = CLng(rs1("JE_Transaction") + 0)
        oJERecord.rDate = CDate(rs1("JE_Date") + 0)
        oJERecord.Name = Trim$(rs1("JE_Name") & "")
        oJERecord.rType = Trim$(rs1("JE_Type") & "")
        oJERecord.ISIN = CLng(Nz(rs1("JE_Position_Ref"), 0))
        oJERecord.ComponentCode = Trim$(rs1("JE_Component_Ref") & "")
        oJERecord.Comments = Trim$(rs1("JE_Comments") & "")
        oJERecord.AutoReverse = CBool(rs1("JE_Auto_Reverse"))
        
        strSQLWhere = " WHERE JD_JournalEntry= " & CStr(oJERecord.ID)
        strSQLWhere = strSQLWhere & ";"
        
        'get journal details
        Set rs2 = m_dbs.OpenRecordset(strSQL & strSQLWhere, dbOpenDynaset, dbForwardOnly, dbPessimistic)
        
        Do Until rs2.EOF
            Set oJDRecord = New clsJDRecord
            
            oJDRecord.ID = CLng(rs2("JD_ID") + 0)
            oJDRecord.JE_ID = CLng(rs2("JD_JournalEntry") + 0)
            oJDRecord.Account = Trim$(rs2("JD_Account") & "")
            oJDRecord.Ccy = Trim$(rs2("JD_Currency") & "")
            oJDRecord.CreditDebit = Trim$(rs2("CreditDebit") & "")
            oJDRecord.SourceAmount = CDbl(rs2("JD_SourceAmount") + 0)
            oJDRecord.FXRate = CDbl(rs2("JD_BaseAmount") + 0)
            oJDRecord.BaseAmount = CDbl(rs2("JD_FXRate") + 0)
            oJERecord.JDRecords.Add oJDRecord
            
            Set oJDRecord = Nothing
            rs2.MoveNext
        Loop
        If oJERecord.JDRecords.Count > 0 Then
            Me.Transaction.JERecords.Add oJERecord
        End If
        Set oJERecord = Nothing
        rs1.MoveNext
    Loop
    
    If Not rs1 Is Nothing Then Set rs1 = Nothing
    If Not rs2 Is Nothing Then Set rs2 = Nothing
    
    If Me.Transaction.JERecords.Count = 0 Then Exit Function
'-----------------------------------------------------------------------------------------------------
'now write records to Transaction, Journal Entry and Journal Detail tables repectively
'-----------------------------------------------------------------------------------------------------
    strSQL = ""
    strSQL = strSQL & " SELECT"
    strSQL = strSQL & " T_ID"
    strSQL = strSQL & ", T_Trans_Name"
    strSQL = strSQL & ", T_Auto_Reverse"
    strSQL = strSQL & ", T_Comments"
    strSQL = strSQL & " FROM"
    strSQL = strSQL & " Transactions"
    strSQL = strSQL & " WHERE T_ID=-1"
    strSQL = strSQL & ";"
    
    Set rs1 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbFailOnError, dbOptimistic)
    rs1.AddNew
        rs1("T_Trans_Name").Value = Me.Transaction.Name
        rs1("T_Auto_Reverse").Value = Me.Transaction.AutoReverse
        rs1("T_Comments").Value = Me.Transaction.Comment
    rs1.Update
    '----------------------------------------------------------------------------------------
    'get Transaction ID
    '----------------------------------------------------------------------------------------
            strSQL = ""
            strSQL = " SELECT"
            strSQL = strSQL & " T_ID"
            strSQL = strSQL & " FROM"
            strSQL = strSQL & " Transactions"
            strSQL = strSQL & " WHERE"
            strSQL = strSQL & " T_Trans_Name = " & fnQPlus(Me.Transaction.Name)
            strSQL = strSQL & ";"
        
            Set rs0 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbForwardOnly, dbPessimistic)
        
            If Not rs0.EOF Then
                Me.Transaction.ID = CLng(Nz(rs0("T_ID").Value, 0))
            End If
            If Not Me.Transaction.ID > 0 Then
                'error
            End If
            Set rs0 = Nothing
            '---------------------------------------------------------------------------------------
            'write JE Record
            '---------------------------------------------------------------------------------------
                    strSQL = ""
                    strSQL = " SELECT"
                    strSQL = strSQL & " JE_ID"
                    strSQL = strSQL & ", JE_Transaction"
                    strSQL = strSQL & ", JE_Date"
                    strSQL = strSQL & ", JE_Name"
                    strSQL = strSQL & ", JE_Type"
                    strSQL = strSQL & ", JE_Position_Ref"
                    strSQL = strSQL & ", JE_Component_Ref"
                    strSQL = strSQL & ", JE_Comments"
                    strSQL = strSQL & ", JE_Auto_Reverse"
                    strSQL = strSQL & " FROM [Journal Entries]"
                    strSQL = strSQL & " WHERE JE_ID=-1"
                    strSQL = strSQL & ";"
    
                    Set rs2 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbFailOnError, dbOptimistic)
                    
                    For Each oJERecord In Me.Transaction.JERecords
                        'write JE then loop through all detail and append to child table Journal Details
                        rs2.AddNew
                            rs2("JE_Transaction").Value = Me.Transaction.ID
                            rs2("JE_Date").Value = Me.Transaction.ReverseDate
                            rs2("JE_Name").Value = oJERecord.Name
                            rs2("JE_Type").Value = oJERecord.rType
                            rs2("JE_Position_Ref").Value = oJERecord.ISIN
                            rs2("JE_Component_Ref").Value = oJERecord.ComponentCode
                            rs2("JE_Auto_Reverse").Value = oJERecord.AutoReverse
                            strJEComment = "Reversal of " & oJERecord.Name _
                                & " By " & Environ$("Username") _
                                & " (Original Posting Dated: " & oJERecord.rDate & ")"
                            rs2("JE_Comments").Value = strJEComment
                        rs2.Update
                        '--------------------------------------------------------------------------------
                        'get journal entry ID
                        '--------------------------------------------------------------------------------
                            strSQL = ""
                            strSQL = strSQL & " SELECT TOP 1"
                            strSQL = strSQL & " JE_ID"
                            strSQL = strSQL & " FROM"
                            strSQL = strSQL & " [Journal Entries]"
                            strSQL = strSQL & " WHERE"
                            strSQL = strSQL & " JE_Comments = " & fnQPlus(strJEComment)
                            strSQL = strSQL & " ORDER BY"
                            strSQL = strSQL & " JE_ID DESC"
                            strSQL = strSQL & ";"
                            
                            'Debug.Print strSQL
                            
                            
                            Set rs0 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbForwardOnly, dbPessimistic)
            
                            If Not rs0.EOF Then
                                oJERecord.ID = CLng(Nz(rs0("JE_ID").Value, 0))
                            End If
                            If Not oJERecord.ID > 0 Then
                                'error
                            End If
                            Set rs0 = Nothing
                            '------------------------------------------------------------------------------------
                            'write Journal detail records
                            '------------------------------------------------------------------------------------
                                If rs3 Is Nothing Then 'then set it
                                    strSQL = ""
                                    strSQL = " SELECT"
                                    strSQL = strSQL & " JD_ID"
                                    strSQL = strSQL & ", JD_JournalEntry"
                                    strSQL = strSQL & ", JD_Account"
                                    strSQL = strSQL & ", JD_Currency"
                                    strSQL = strSQL & ", JD_CreditDebit"
                                    strSQL = strSQL & ", JD_SourceAmount"
                                    strSQL = strSQL & ", JD_BaseAmount"
                                    strSQL = strSQL & ", JD_FXRate"
                                    strSQL = strSQL & " FROM [Journal Details]"
                                    strSQL = strSQL & " WHERE"
                                    strSQL = strSQL & " JD_ID=-1"
                                    strSQL = strSQL & ";"
                                   
                                    Set rs3 = m_dbs.OpenRecordset(strSQL, dbOpenDynaset, dbFailOnError, dbOptimistic)
                                End If
                                
                                For Each oJDRecord In oJERecord.JDRecords
                                    rs3.AddNew
                                        rs3("JD_JournalEntry").Value = oJERecord.ID
                                        rs3("JD_Account").Value = oJDRecord.Account
                                        rs3("JD_Currency").Value = oJDRecord.Ccy
                                        rs3("JD_CreditDebit").Value = oJDRecord.CreditDebit
                                        rs3("JD_SourceAmount").Value = oJDRecord.SourceAmount
                                        rs3("JD_BaseAmount").Value = oJDRecord.BaseAmount
                                        rs3("JD_FXRate").Value = IIf(oJDRecord.FXRate > 100, 1, oJDRecord.FXRate) 'just for testing because of rubbish test data!!!!!
                                    rs3.Update
                                Next oJDRecord
                    Next oJERecord
                    
    ReverseTransaction = True
ExitCode:
    If Not rs0 Is Nothing Then Set rs0 = Nothing
    If Not rs1 Is Nothing Then Set rs1 = Nothing
    If Not rs2 Is Nothing Then Set rs2 = Nothing
    If Not rs3 Is Nothing Then Set rs3 = Nothing
     
    Exit Function
ErrorHandler:
    DisplayError Err, "clsJournals.ReverseTransaction"
    Resume ExitCode
 
End Function

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question