Solved

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

Posted on 2009-07-07
3
201 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now