How Do I Get My Changed Table Relationships To Be Updated in thelinked Front end?

[nouveauricheinvestments]

Hi,

I have a table where I changed the relationship in the back end to
Delete Cascade Records and it is not reflected in the front end. How
do I get this change in the relationship to be reflected in my front
end? I have tried importing the tables that need the relationship
modifications to the front end and modifying the relationships there
and then recreating the linked tables and it is still not reflected in
the front end. How do I change this relationship in my front end?

 

[Dorian]

Relationships should be defined ONLY in the back-end. Try relinking your
tabled from front to back end. It should work. Are you sure you defined the
relationships right?

-Dorian

 

[John W. Vinson]

Hi,

I have a table where I changed the relationship in the back end to
Delete Cascade Records and it is not reflected in the front end. How
do I get this change in the relationship to be reflected in my front
end? I have tried importing the tables that need the relationship
modifications to the front end and modifying the relationships there
and then recreating the linked tables and it is still not reflected in
the front end. How do I change this relationship in my front end?

The relationships DO NOT EXIST in your front end, only in the backend, where
the tables are. What you see in the relationships window is just advisory, and
is not (and cannot be) enforced.

You'll need to delete the tables that you imported - you now have two copies
of each such table, one in the frontend, one in the backend!!! Back up your
database (or make sure that you still have the backup you hopefully made
before you started importing tables and use it). After you do so, compact the
database; delete all your table links in the frontend; compact to get rid of
Access' memory of them; and use File... Get External Data... Link to relink to
the tables in the backend. The relationships window in the frontend should now
reflect the actual relationships defined in the backend, but if it doesn't,
don't worry about it; the cascades etc. will still be obeyed.

 

[nouveauricheinvestments]

The relationships DO NOT EXIST in your front end, only in the backend, where
the tables are. What you see in the relationships window is just advisory, and
is not (and cannot be) enforced.

You'll need to delete the tables that you imported - you now have two copies
of each such table, one in the frontend, one in the backend!!! Back up your
database (or make sure that you still have the backup you hopefully made
before you started importing tables and use it). After you do so, compact the
database; delete all your table links in the frontend; compact to get rid of
Access' memory of them; and use File... Get External Data... Link to relink to
the tables in the backend. The relationships window in the frontend should now
reflect the actual relationships defined in the backend, but if it doesn't,
don't worry about it; the cascades etc. will still be obeyed.

Yes I did delete the tables in my front end. I also deleted all of
the linked tables and then I did compact and repair. I understand the
relationships I see in the front end are inconsequential, but I can't
see what else my problem would be. To give you a thorough
understanding of what is going on, I have detailed below.

I have a form that is bound to my parent table [Pending Tickets] and I
have a subform which is bound to my child table [Order_Details]. When
a user hits cancel, the following code is run:

Public Sub DelUnneededRecords(MyID As Integer)

Dim DelMyOrders As String
Dim DelMyTicket As String
Dim DelNullRec As String

DelMyOrders = "DELETE Order_Details.* FROM Order_Details WHERE
Order_Details.PTID=" & MyID & ";"
DelNullRec = "DELETE Order_Details.* FROM Order_Details WHERE
Order_Details.PTID Is Null;"
DelMyTicket = "DELETE [Pending Tickets].* FROM [Pending Tickets] WHERE
[Pending Tickets].ID=" & MyID & ";"



If TicketNumbers = True Then
DoCmd.RunSQL DelMyOrders
End If
DoCmd.RunSQL DelMyTicket
DoCmd.RunSQL DelNullRec
DoCmd.SetWarnings True


End Sub

If the user enters data in the parent form without entering anything
in the subform, they will get an error message stating:

You cannot add or change a record because a related record is required
in table 'Pending Tickets'.

If the user enters data in both the parent form and the child form, no
error message.

Two other odd facts about this:

1. When I enter a record manually in the [Pending Tickets] table and
a related record in the child table Order_Details, making sure to
relate the record with the foreign key, I then can run the above
procedure from the immediate window and I don't get an error
message.

2. When I comment out the Docmd.DelMyTicket, I do not get the error
message either, but it does not delete the record - i.e. the sql
statement seems to be deleting the record from my parent table,
despite the error message.

 

[nouveauricheinvestments]

The relationships DO NOT EXIST in your front end, only in the backend, where
the tables are. What you see in the relationships window is just advisory, and
is not (and cannot be) enforced.

You'll need to delete the tables that you imported - you now have two copies
of each such table, one in the frontend, one in the backend!!! Back up your
database (or make sure that you still have the backup you hopefully made
before you started importing tables and use it). After you do so, compact the
database; delete all your table links in the frontend; compact to get rid of
Access' memory of them; and use File... Get External Data... Link to relink to
the tables in the backend. The relationships window in the frontend should now
reflect the actual relationships defined in the backend, but if it doesn't,
don't worry about it; the cascades etc. will still be obeyed.
--

John W. Vinson [MVP]

Yes I did delete the tables in my front end. I also deleted all of
the linked tables and then I did compact and repair. I understand the
relationships I see in the front end are inconsequential, but I can't
see what else my problem would be. To give you a thorough
understanding of what is going on, I have detailed below.

I have a form that is bound to my parent table [Pending Tickets] and I
have a subform which is bound to my child table [Order_Details]. When
a user hits cancel, the following code is run:

Public Sub DelUnneededRecords(MyID As Integer)

Dim DelMyOrders As String
Dim DelMyTicket As String
Dim DelNullRec As String

DelMyOrders = "DELETE Order_Details.* FROM Order_Details WHERE
Order_Details.PTID=" & MyID & ";"
DelNullRec = "DELETE Order_Details.* FROM Order_Details WHERE
Order_Details.PTID Is Null;"
DelMyTicket = "DELETE [Pending Tickets].* FROM [Pending Tickets] WHERE
[Pending Tickets].ID=" & MyID & ";"

If TicketNumbers = True Then
DoCmd.RunSQL DelMyOrders
End If
DoCmd.RunSQL DelMyTicket
DoCmd.RunSQL DelNullRec
DoCmd.SetWarnings True

End Sub

If the user enters data in the parent form without entering anything
in the subform, they will get an error message stating:

You cannot add or change a record because a related record is required
in table 'Pending Tickets'.

If the user enters data in both the parent form and the child form, no
error message.

Two other odd facts about this:

1. When I enter a record manually in the [Pending Tickets] table and
a related record in the child table Order_Details, making sure to
relate the record with the foreign key, I then can run the above
procedure from the immediate window and I don't get an error
message.

2. When I comment out the Docmd.DelMyTicket, I do not get the error
message either, but it does not delete the record - i.e. the sql
statement seems to be deleting the record from my parent table,
despite the error message.

By the way, the 'If TicketNumbers = True statement refers to the
following public function

Public Function TicketNumbers() As Boolean
Dim MyForm As Form
Set MyForm = Forms("OpenTicket")
If IsNull((Forms![OpenTicket]![Order_Details subform]![TicketNumber]))
Then
TicketNumbers = False
Else
TicketNumbers = True
End If
If IsNull((Forms![OpenTicket]![Order_Details subform]![PairID])) Then
TicketNumbers = False
Else
TicketNumbers = True
End If
If IsNull((Forms![OpenTicket]![Order_Details subform]![OrderTypeID]))
Then
TicketNumbers = False
Else
TicketNumbers = True
End If
End Function


Also, I didn't specify that after I recreated my linked tables, the
relationships in the front end were not reflective of what is in the
back end. Why would that be? It seems weird.

 

[nouveauricheinvestments]

Yes I did delete the tables in my front end. I also deleted all of
the linked tables and then I did compact and repair. I understand the
relationships I see in the front end are inconsequential, but I can't
see what else my problem would be. To give you a thorough
understanding of what is going on, I have detailed below.

I have a form that is bound to my parent table [Pending Tickets] and I
have a subform which is bound to my child table [Order_Details]. When
a user hits cancel, the following code is run:

Public Sub DelUnneededRecords(MyID As Integer)

Dim DelMyOrders As String
Dim DelMyTicket As String
Dim DelNullRec As String

DelMyOrders = "DELETE Order_Details.* FROM Order_Details WHERE
Order_Details.PTID=" & MyID & ";"
DelNullRec = "DELETE Order_Details.* FROM Order_Details WHERE
Order_Details.PTID Is Null;"
DelMyTicket = "DELETE [Pending Tickets].* FROM [Pending Tickets] WHERE
[Pending Tickets].ID=" & MyID & ";"

If TicketNumbers = True Then
DoCmd.RunSQL DelMyOrders
End If
DoCmd.RunSQL DelMyTicket
DoCmd.RunSQL DelNullRec
DoCmd.SetWarnings True

End Sub

If the user enters data in the parent form without entering anything
in the subform, they will get an error message stating:

You cannot add or change a record because a related record is required
in table 'Pending Tickets'.

If the user enters data in both the parent form and the child form, no
error message.

Two other odd facts about this:

1. When I enter a record manually in the [Pending Tickets] table and
a related record in the child table Order_Details, making sure to
relate the record with the foreign key, I then can run the above
procedure from the immediate window and I don't get an error
message.

2. When I comment out the Docmd.DelMyTicket, I do not get the error
message either, but it does not delete the record - i.e. the sql
statement seems to be deleting the record from my parent table,
despite the error message.

By the way, the 'If TicketNumbers = True statement refers to the
following public function

Public Function TicketNumbers() As Boolean
Dim MyForm As Form
Set MyForm = Forms("OpenTicket")
If IsNull((Forms![OpenTicket]![Order_Details subform]![TicketNumber]))
Then
TicketNumbers = False
Else
TicketNumbers = True
End If
If IsNull((Forms![OpenTicket]![Order_Details subform]![PairID])) Then
TicketNumbers = False
Else
TicketNumbers = True
End If
If IsNull((Forms![OpenTicket]![Order_Details subform]![OrderTypeID]))
Then
TicketNumbers = False
Else
TicketNumbers = True
End If
End Function

Also, I didn't specify that after I recreated my linked tables, the
relationships in the front end were not reflective of what is in the
back end. Why would that be? It seems weird.

I think I found a workaround. At least for now. I changed my event
code in the Cancel_Click to the following

Private Sub Cancel_Click()
Dim MySQL As String
MySQL = "DELETE FROM Order_Details WHERE Order_Details.PTID Is Null;"
If (Me.Dirty = True) Then
Me.Undo
End If
If TicketNumbers = True Then
DelUnneededRecords Me!ID
Else
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
End If

DoCmd.Close acForm, Me.Name
End Sub