Unexpected Delete

  • Thread starter Thread starter oldblindpew
  • Start date Start date
O

oldblindpew

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
 
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.
 
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
 
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...
 
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
 
Would this argue for using shorter field names?
Click to expand...

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, .[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] = ...
 
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 said:
Would this argue for using shorter field names?
Click to expand...

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, .[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] = ...
Click to expand...
 
Back
Top