A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Unexpected Delete



 
 
Thread Tools Display Modes
  #1  
Old February 15th, 2010, 03:54 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Unexpected Delete

I have a table with many existing records, and it uses an autonumber key. I
recently added another table for additional information that only applies to
some, not all, of the records in the first table. These tables have a
one-to-one relationship, and the second table's primary key is supposed to
match the first table's primary key.

I add records to the second table by selecting from the first table via a
combo box.

My question is: What could cause Access to delete the record from the first
table just because I delete the record in the second table?

Directional: I have heard the term "directional" applied to relationships,
but none of my reference books breathe a mumbling word on this topic.

Referential Integrity: At first I had NOT enforced referential integrity, so
in desperation I tried enforcing it, but it made no difference.

Thanks,
OldBlindPew
  #2  
Old February 15th, 2010, 05:22 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Unexpected Delete

Do you have the tables joined in the Relationships Window? If so do you have
Referential Integrity enabled? If that answer is Yes, look just below the RI
check box and there are Cascade Update and ((shudder)) Cascade Delete. If
Cascade Delete is enabled and you delete a record, all related records are
also toast.

However it seems by your description that Cascade Delete wasn't on as you
didn't have RI enabled at first.

Are you sure that the record is actually missing? If you are joining the two
tables in a query with an inner join, a record won't be returned unless there
is a record in both tables. Also you are using autonumbers. It's possible to
'burn' an autonumber very easily. If you are just seeing gaps in the
autonumber sequence, that means nothing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

I have a table with many existing records, and it uses an autonumber key. I
recently added another table for additional information that only applies to
some, not all, of the records in the first table. These tables have a
one-to-one relationship, and the second table's primary key is supposed to
match the first table's primary key.

I add records to the second table by selecting from the first table via a
combo box.

My question is: What could cause Access to delete the record from the first
table just because I delete the record in the second table?

Directional: I have heard the term "directional" applied to relationships,
but none of my reference books breathe a mumbling word on this topic.

Referential Integrity: At first I had NOT enforced referential integrity, so
in desperation I tried enforcing it, but it made no difference.

Thanks,
OldBlindPew

  #3  
Old February 15th, 2010, 06:53 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Unexpected Delete

Thanks, Jerry.

Per prior post, RI was not set at first.
I tried setting RI because I couldn't think of anything else to try.
Cascading updates or deletes have never been enabled.
The record in the main table definitely gets deleted. I have to manally
re-add it.
I have compacted and repaired.
This is a split database, but that shouldn't make any difference.

There is another wierd thing going on: when I try to add a new record to the
second table, using a subform, I get "Field Cannot Be Updated". When I click
Okay, then close and reopen the form, the new record is there.

At this point all I can think do is to scrap the two-table approach and just
put the additional fields in the first table.

Thanks,
Pew

"Jerry Whittle" wrote:

Do you have the tables joined in the Relationships Window? If so do you have
Referential Integrity enabled? If that answer is Yes, look just below the RI
check box and there are Cascade Update and ((shudder)) Cascade Delete. If
Cascade Delete is enabled and you delete a record, all related records are
also toast.

However it seems by your description that Cascade Delete wasn't on as you
didn't have RI enabled at first.

Are you sure that the record is actually missing? If you are joining the two
tables in a query with an inner join, a record won't be returned unless there
is a record in both tables. Also you are using autonumbers. It's possible to
'burn' an autonumber very easily. If you are just seeing gaps in the
autonumber sequence, that means nothing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

I have a table with many existing records, and it uses an autonumber key. I
recently added another table for additional information that only applies to
some, not all, of the records in the first table. These tables have a
one-to-one relationship, and the second table's primary key is supposed to
match the first table's primary key.

I add records to the second table by selecting from the first table via a
combo box.

My question is: What could cause Access to delete the record from the first
table just because I delete the record in the second table?

Directional: I have heard the term "directional" applied to relationships,
but none of my reference books breathe a mumbling word on this topic.

Referential Integrity: At first I had NOT enforced referential integrity, so
in desperation I tried enforcing it, but it made no difference.

Thanks,
OldBlindPew

  #4  
Old February 15th, 2010, 07:15 PM posted to microsoft.public.access.tablesdbdesign
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Unexpected Delete

Pew -

One possibility - does the second form possibly have the bound key value
from the original table instead of the new table? Check the record source
for this form and make sure you don't have the incorrect field...

--
Daryl S


"oldblindpew" wrote:

Thanks, Jerry.

Per prior post, RI was not set at first.
I tried setting RI because I couldn't think of anything else to try.
Cascading updates or deletes have never been enabled.
The record in the main table definitely gets deleted. I have to manally
re-add it.
I have compacted and repaired.
This is a split database, but that shouldn't make any difference.

There is another wierd thing going on: when I try to add a new record to the
second table, using a subform, I get "Field Cannot Be Updated". When I click
Okay, then close and reopen the form, the new record is there.

At this point all I can think do is to scrap the two-table approach and just
put the additional fields in the first table.

Thanks,
Pew

"Jerry Whittle" wrote:

Do you have the tables joined in the Relationships Window? If so do you have
Referential Integrity enabled? If that answer is Yes, look just below the RI
check box and there are Cascade Update and ((shudder)) Cascade Delete. If
Cascade Delete is enabled and you delete a record, all related records are
also toast.

However it seems by your description that Cascade Delete wasn't on as you
didn't have RI enabled at first.

Are you sure that the record is actually missing? If you are joining the two
tables in a query with an inner join, a record won't be returned unless there
is a record in both tables. Also you are using autonumbers. It's possible to
'burn' an autonumber very easily. If you are just seeing gaps in the
autonumber sequence, that means nothing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

I have a table with many existing records, and it uses an autonumber key. I
recently added another table for additional information that only applies to
some, not all, of the records in the first table. These tables have a
one-to-one relationship, and the second table's primary key is supposed to
match the first table's primary key.

I add records to the second table by selecting from the first table via a
combo box.

My question is: What could cause Access to delete the record from the first
table just because I delete the record in the second table?

Directional: I have heard the term "directional" applied to relationships,
but none of my reference books breathe a mumbling word on this topic.

Referential Integrity: At first I had NOT enforced referential integrity, so
in desperation I tried enforcing it, but it made no difference.

Thanks,
OldBlindPew

  #5  
Old February 15th, 2010, 09:13 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Unexpected Delete

Thanks Daryl S,

I must have some wires crossed here somehow. I'm trying to add records to
the second table by using a combo box to find the key value in the first
table and place it in the second table.

Meanwhile, in a backup copy of the database, I abandoned the second table
and brought its fields into the first table, with promising results. I'm not
all that pleased with this approach, but may have to settle for a crude
solution that works rather than an elegant one that doesn't.

When creating my row source in the query builder I apparently ran out of
space! I was forced to specify "all fields" in the first table rather than
just the ones I needed. I guess I ran into a limit on string length for the
query. It's not very many fields and it seems a shame to fetch the whole
table when just a portion would do. Is there a performance gain or loss at
stake? Would this argue for using shorter field names?

Thanks,
Pew

"Daryl S" wrote:

Pew -

One possibility - does the second form possibly have the bound key value
from the original table instead of the new table? Check the record source
for this form and make sure you don't have the incorrect field...

--
Daryl S


"oldblindpew" wrote:

Thanks, Jerry.

Per prior post, RI was not set at first.
I tried setting RI because I couldn't think of anything else to try.
Cascading updates or deletes have never been enabled.
The record in the main table definitely gets deleted. I have to manally
re-add it.
I have compacted and repaired.
This is a split database, but that shouldn't make any difference.

There is another wierd thing going on: when I try to add a new record to the
second table, using a subform, I get "Field Cannot Be Updated". When I click
Okay, then close and reopen the form, the new record is there.

At this point all I can think do is to scrap the two-table approach and just
put the additional fields in the first table.

Thanks,
Pew

"Jerry Whittle" wrote:

Do you have the tables joined in the Relationships Window? If so do you have
Referential Integrity enabled? If that answer is Yes, look just below the RI
check box and there are Cascade Update and ((shudder)) Cascade Delete. If
Cascade Delete is enabled and you delete a record, all related records are
also toast.

However it seems by your description that Cascade Delete wasn't on as you
didn't have RI enabled at first.

Are you sure that the record is actually missing? If you are joining the two
tables in a query with an inner join, a record won't be returned unless there
is a record in both tables. Also you are using autonumbers. It's possible to
'burn' an autonumber very easily. If you are just seeing gaps in the
autonumber sequence, that means nothing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

I have a table with many existing records, and it uses an autonumber key. I
recently added another table for additional information that only applies to
some, not all, of the records in the first table. These tables have a
one-to-one relationship, and the second table's primary key is supposed to
match the first table's primary key.

I add records to the second table by selecting from the first table via a
combo box.

My question is: What could cause Access to delete the record from the first
table just because I delete the record in the second table?

Directional: I have heard the term "directional" applied to relationships,
but none of my reference books breathe a mumbling word on this topic.

Referential Integrity: At first I had NOT enforced referential integrity, so
in desperation I tried enforcing it, but it made no difference.

Thanks,
OldBlindPew

  #6  
Old February 15th, 2010, 11:03 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Unexpected Delete

On Mon, 15 Feb 2010 12:13:01 -0800, oldblindpew
wrote:

Would this argue for using shorter field names?


That, or alias the tablenames and fieldnames: rather than

SELECT [LongTableNameA].[ThisIsABigFieldName],
[LongTableNameB].[AnotherBigFieldName]
FROM [LongTableNameA] INNER JOIN [LongTableNameB]
ORDER BY [LongTableNameA].[ThisIsABigFieldName]

you can use

SELECT [A].[ThisIsABigFieldName] AS BigA, [b].[AnotherBigFieldName] AS BigB
FROM [LongTableNameA] AS A INNER JOIN [LongTableNameB] AS B
ORDER BY BigA;

In a large many-field query with long fieldnames this can save you a whole lot
of characters.

Also, if a fieldname is unambiguous you don't need to qualify it with the
tablename; i.e. instead of

WHERE [LongTableNameA].[SomeUniqueField] = criteria

just use

WHERE [SomeUniqueField] = ...
--

John W. Vinson [MVP]
  #7  
Old February 16th, 2010, 05:55 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Unexpected Delete

Thanks, John.

I still don't know whether it is truly important to narrow the selection of
fields. If it is (as I would think), and since field names tend to be
somewhat lengthy despite our best efforts, then it seems odd being limited by
the length of the string for a query. One would think by now there would be
some sort of behind-the-scenes mechanism to circumvent this limitation.

Thanks Again,
Pew

"John W. Vinson" wrote:

On Mon, 15 Feb 2010 12:13:01 -0800, oldblindpew
wrote:

Would this argue for using shorter field names?


That, or alias the tablenames and fieldnames: rather than

SELECT [LongTableNameA].[ThisIsABigFieldName],
[LongTableNameB].[AnotherBigFieldName]
FROM [LongTableNameA] INNER JOIN [LongTableNameB]
ORDER BY [LongTableNameA].[ThisIsABigFieldName]

you can use

SELECT [A].[ThisIsABigFieldName] AS BigA, [b].[AnotherBigFieldName] AS BigB
FROM [LongTableNameA] AS A INNER JOIN [LongTableNameB] AS B
ORDER BY BigA;

In a large many-field query with long fieldnames this can save you a whole lot
of characters.

Also, if a fieldname is unambiguous you don't need to qualify it with the
tablename; i.e. instead of

WHERE [LongTableNameA].[SomeUniqueField] = criteria

just use

WHERE [SomeUniqueField] = ...
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.