Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

[ACCESS] - one-to-one linke doesn't keep tables in sync

Options
  • 24-03-2017 10:11am
    #1
    PrzemoF
    Registered Users Posts: 1,931 ✭✭✭


    I have a master table with ID field (primary key, autonumber, indexed, no duplicates) and 5 dependent tables with ID field in each (number, indexed, no duplicates). The realtionships are set one-to-one with enforcing integrity/deleting and updating. It worked fine for quite a while. A few days ago I decided to split the database into back-end/front-end. I did it manually, because access splitter couldn't handle it. Yesterday I used an append query to create some new items in the master table and I realised that:
    1. There is one item (the last before the append query) that is not mirrored in the 5 dependant tables
    2. None of the items appended to the master table with the append query are in the 5 dependant tables

    Any idea what is happening? Is the DB split the problem? Or the append query? Or there is a corruption going on in the background?

    I should be able to clear the mess manually, but I don't know what is the reason and that worries me a bit..

    P.S. I just added a new entry to the master table and it doesn't show up in the the 5 dependant tables :-/


Comments

  • Options
    #2
    PrzemoF
    Registered Users Posts: 1,931 ✭✭✭PrzemoF


    Looks like access only creates entries in corresponding (one-to-one linked tables) if something changes in the corresponding table. So it works fine from a query or when form sets a default value, but doesn't work directly on tables :-/


  • Options
    #3
    PrzemoF
    Registered Users Posts: 1,931 ✭✭✭PrzemoF


    That seems to work:
    Private Sub Form_AfterUpdate()
' IDs have to be set in all corresponding tables.
' This is a workaround for Access not adding entries to related tables despite
' one-to-one links between ID fields being in place.
    
    new_id = Me.INSTRUMENTS_ID.Value
    If IsNull(Me.ASSIGNMENTS_ID) Then
        Me.ASSIGNMENTS_ID = new_id
    End If
    If IsNull(Me.PROPERTIES_ID) Then
        Me.PROPERTIES_ID = new_id
    End If
    If IsNull(Me.PROPERTIES_PS_ID) Then
        Me.PROPERTIES_PS_ID = new_id
    End If
    If IsNull(Me.PROPERTIES_RD_ID) Then
        Me.PROPERTIES_RD_ID = new_id
    End If
    If IsNull(Me.PROPERTIES_VA_ID) Then
        Me.PROPERTIES_VA_ID = new_id
    End If
End Sub


  • Options
    #4
    Seth Brundle
    Moderators, Politics Moderators Posts: 39,200 Mod ✭✭✭✭Seth Brundle


    I'm only seeing this now but a one to many relationship between two tables doesn't automatically place a record in the dependent tables if a record is added to the master. The relationship simply means that any record added to the dependent has to have a corresponding record in the master.
    In a library system, a member can exist (e.g. in the Members table) regardless of whether they have borrowed any books (e.g. in the Loans table). A loan needs a member but a member doesn't need a loan.


    However, the VBA code you have added will add corresponding records to the dependent tables (assuming there are no non-null fields in the dependent tables).


Advertisement