Change Indexed propety of field 
Author Message
 Change Indexed propety of field

How do I change the 'Indexed' property of a field in code; for example,
tblOrders has a field called Order_Number, it's Indexed property is set to
'Yes No Duplicates', now, I want to write code that if there is a duplicated
order number entered then if the first order was entered more than 3 years
ago then it should set the 'Indexed' property to 'No', but right after that
it should set it back to ''Yes No Duplicates'.

If Err.Number = 3022 Then
    if (Date - rstOrders!Date_Entered) > 1095 then
        'change the Indexed property to 'No',
        'then set it back to 'Yes No Duplicates'.

Thanks

Scott



Sat, 19 Nov 2005 01:22:16 GMT  
 Change Indexed propety of field


Quote:
> How do I change the 'Indexed' property of a field in code; for
> example, tblOrders has a field called Order_Number, it's Indexed
> property is set to 'Yes No Duplicates', now, I want to write code that
> if there is a duplicated order number entered then if the first order
> was entered more than 3 years ago then it should set the 'Indexed'
> property to 'No', but right after that it should set it back to ''Yes
> No Duplicates'.

Well, if you really had to do this, you'd be looking through the Tabledef's
Indexes collection.

But I have to admit that it sounds like a Bad Thing To Do. Unique indexes
are an important part of the schema design and messing about with them is
in the same league as creating and deleting fields "on the fly" -- probably
worse, because Uniqeness is a fundamental data integrity check. It's one of
the weaknesses of Access's design that makes Unique indexes look like a
slightly special form of Index -- think more like Load Bearing Wall and
Garden Fence. One is a vital part of the structure, the other just makes it
work a bit better.

It seems to me that perhaps your OrderNumbers are repeatable, in which case
you may as well just drop the Unique constraint and stop messing about. If
these are other people's orders coming in, then I guess that (CustomerID,
OrderNumber) would be unique as a combination.

If the scenario is that OrderNumbers do need to be unique, though, (and I
would guess that to be true if they are your orders) and if you are having
duplicates then it may be that the ordering system is sick. If you have a
new ordering system, what about just using a flag for "OldOrderSystem" for
the previous ones and again using a compound index for the constraint? Or
just moving the old orders out into their own table for archival purposes?

Hope that helps

Tim F



Sat, 19 Nov 2005 03:02:26 GMT  
 Change Indexed propety of field

Quote:
> How do I change the 'Indexed' property of a field in code; for
> example, tblOrders has a field called Order_Number, it's Indexed
> property is set to 'Yes No Duplicates', now, I want to write code
> that if there is a duplicated order number entered then if the first
> order was entered more than 3 years ago then it should set the
> 'Indexed' property to 'No', but right after that it should set it
> back to ''Yes No Duplicates'.

> If Err.Number = 3022 Then
>     if (Date - rstOrders!Date_Entered) > 1095 then
>         'change the Indexed property to 'No',
>         'then set it back to 'Yes No Duplicates'.

I agree very strongly with Tim Ferguson.  You seem to be thinking that
the Unique property of an index is primarily a data-entry validation.
It's not;  it's a fundamental characteristic of the index.  Although you
could conceivably change the index from unique to non-unique so as to
allow the entry of a duplicate key, you could not then change the index
back to unique, because that would result in a duplicate key in a unique
index.  The database engine won't allow such a thing.  Even if it would,
the index would then have to be considered "corrupt" and unusable.

What you want in this case is to define the index on Order_Number as
non-unique, but either create a compound index that would be unique, or
put code in your form's BeforeUpdate event to check (for new records) if
the order number is already on file, and reject it if it is, unless the
older order is more than 3 years old.  Your table will still need a
unique index for its primary key.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



Sat, 19 Nov 2005 03:52:48 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Table object index propety (VB 3)

2. remove index from field or delete a indexed field

3. Adding Field to Access db - need to make indexed property of field No Duplicates

4. Numeric Index of RS.field from field name?

5. Too many indexes - Wierd hidden indexes preventing index add

6. Changing the Default duration field to a custom duration field

7. getting propety interface mapping

8. Recordset.LastUpdated propety

9. HOW TO change field size and delete field

10. Name field displayed on ID field change

11. help!! new at vb (propety procedures)

12. how to change field size and delete field MS ACCESS/VB

 

 
Powered by phpBB® Forum Software