Re: How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- From: nouveauricheinvestments@xxxxxxxxx
- Date: Wed, 10 Dec 2008 09:48:22 -0800 (PST)
On Dec 10, 10:19 am, John W. Vinson
<jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Wed, 10 Dec 2008 08:09:01 -0800 (PST), nouveauricheinvestme...@xxxxxxxxx
wrote:
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.
--
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.
.
- Follow-Ups:
- Re: How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- From: nouveauricheinvestments
- Re: How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- References:
- How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- From: nouveauricheinvestments
- Re: How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- From: John W . Vinson
- How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- Prev by Date: RE: Using Dlookup in form - New to Access
- Next by Date: Re: How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- Previous by thread: Re: How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- Next by thread: Re: How Do I Get My Changed Table Relationships To Be Updated in the linked Front end?
- Index(es):
Relevant Pages
|
