Problems Enforcing Referential Integrity using Autonumber field 
Author Message
 Problems Enforcing Referential Integrity using Autonumber field

I'm posting this question here because it was the most relevant group
of all the non-relevant groups I could find for this question.  I would
welcome some ideas on how to enforce referential integrity including
cascade update and delete options for an existing database consisting
of 2 tables.  These have already been created so I have to work with
what already exists.  Using Access 2.0, I have a master table which has
a field called ID and is an "counter field".  The child table is linked
to the master by this ID field, only in the child table, it is of type
Integer.
Naturally  I can't enforce referential integrity unless the 2 ID fields
in
each table are the same type.  I'm stuck....I can't change the existing
child's ID field to "counter" type since the field already contains
data,
and Access won't allow that. I can't create a new counter field in the
child
table because then it fills in numbers starting with 1, and the links to
the
master table would be rendered invalid,  but I do need to keep the
counter field
in the master table.  What can someone suggest?  Is it possible (or
feasible)
to link 2 tables together using a counter field?

Thanks for you input,
Van Cox



Sat, 15 Jul 2000 03:00:00 GMT  
 Problems Enforcing Referential Integrity using Autonumber field

Access 2.0 seems a long time ago but I thought, (maybe it's another version),  
you could enable relationships and referential integrity by changing your
child field type to Long? That would maintain your present values, I believe.
--
Lyle Fairfield


Quote:

>I'm posting this question here because it was the most relevant group
>of all the non-relevant groups I could find for this question.  I would
>welcome some ideas on how to enforce referential integrity including
>cascade update and delete options for an existing database consisting
>of 2 tables.  These have already been created so I have to work with
>what already exists.  Using Access 2.0, I have a master table which has
>a field called ID and is an "counter field".  The child table is linked
>to the master by this ID field, only in the child table, it is of type
>Integer.
>Naturally  I can't enforce referential integrity unless the 2 ID fields
>in
>each table are the same type.  I'm stuck....I can't change the existing
>child's ID field to "counter" type since the field already contains
>data,
>and Access won't allow that. I can't create a new counter field in the
>child
>table because then it fills in numbers starting with 1, and the links to
>the
>master table would be rendered invalid,  but I do need to keep the
>counter field
>in the master table.  What can someone suggest?  Is it possible (or
>feasible)
>to link 2 tables together using a counter field?

>Thanks for you input,
>Van Cox



Sat, 15 Jul 2000 03:00:00 GMT  
 Problems Enforcing Referential Integrity using Autonumber field

Apparently some of what was said below is not correct.  Access will
allow 2 different field types to be linked together.  Specifically, it
like linking a counter, or autonumber field to a Long Integer, but it
is prejudice against just a plain vanilla Integer, which apparently
isn't classy enough.

Thankme

Quote:

> I'm posting this question here because it was the most relevant group
> of all the non-relevant groups I could find for this question.  I
> would
> welcome some ideas on how to enforce referential integrity including
> cascade update and delete options for an existing database consisting
> of 2 tables.  These have already been created so I have to work with
> what already exists.  Using Access 2.0, I have a master table which
> has
> a field called ID and is an "counter field".  The child table is
> linked
> to the master by this ID field, only in the child table, it is of type

> Integer.
> Naturally  I can't enforce referential integrity unless the 2 ID
> fields
> in
> each table are the same type.  I'm stuck....I can't change the
> existing
> child's ID field to "counter" type since the field already contains
> data,
> and Access won't allow that. I can't create a new counter field in the

> child
> table because then it fills in numbers starting with 1, and the links
> to
> the
> master table would be rendered invalid,  but I do need to keep the
> counter field
> in the master table.  What can someone suggest?  Is it possible (or
> feasible)
> to link 2 tables together using a counter field?

> Thanks for you input,
> Van Cox



Sat, 15 Jul 2000 03:00:00 GMT  
 Problems Enforcing Referential Integrity using Autonumber field

Why don't you just change the data type of the child field to long integer,
you can then link from the Counter field in the Parent table.

BTW, you cannot link from a Counter field onto a counter field, you always
link from a Counter field onto a Long Integer.

Quote:

>Apparently some of what was said below is not correct.  Access will
>allow 2 different field types to be linked together.  Specifically, it
>like linking a counter, or autonumber field to a Long Integer, but it
>is prejudice against just a plain vanilla Integer, which apparently
>isn't classy enough.

>Thankme


>> I'm posting this question here because it was the most relevant group
>> of all the non-relevant groups I could find for this question.  I
>> would
>> welcome some ideas on how to enforce referential integrity including
>> cascade update and delete options for an existing database consisting
>> of 2 tables.  These have already been created so I have to work with
>> what already exists.  Using Access 2.0, I have a master table which
>> has
>> a field called ID and is an "counter field".  The child table is
>> linked
>> to the master by this ID field, only in the child table, it is of type

>> Integer.
>> Naturally  I can't enforce referential integrity unless the 2 ID
>> fields
>> in
>> each table are the same type.  I'm stuck....I can't change the
>> existing
>> child's ID field to "counter" type since the field already contains
>> data,
>> and Access won't allow that. I can't create a new counter field in the

>> child
>> table because then it fills in numbers starting with 1, and the links
>> to
>> the
>> master table would be rendered invalid,  but I do need to keep the
>> counter field
>> in the master table.  What can someone suggest?  Is it possible (or
>> feasible)
>> to link 2 tables together using a counter field?

>> Thanks for you input,
>> Van Cox



Sat, 15 Jul 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Enforce Referential Integrity

2. Enforce referential integrity in a disconnected recordset?

3. Enforcing Referential Integrity

4. Create relationship without enforcing Referential Integrity

5. Transaction w Referential Integrity Problem

6. VB3 referential integrity problem

7. Help: Access/VB problem with referential integrity

8. Enforce Referencial Integrity by code

9. Nulls in Referential Integrity

10. Referential Integrity

11. Referential Integrity

12. Referential Integrity violations being allowed: BUG????

 

 
Powered by phpBB® Forum Software