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

PrzemoF · 24-03-2017 09:11AM #1

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 :-/

PrzemoF · 27-03-2017 04:13PM

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 :-/

PrzemoF · 28-03-2017 09:30AM

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

Seth Brundle · 30-03-2017 09:29AM

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).

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

Comments