Trying to update a field in a access db where the property "Indexed Yes (NO Duplicates)" was set initially. Must be changed to Indexed Yes (Duplicates OK)

m2maths
m2maths used Ask the Experts™
on
I am trying to update a field in a access db where the property "Indexed Yes (NO Duplicates)" was set initially. I would like to change it  to Indexed Yes (Duplicates OK) with existing data.

Any suggestions would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you... can you not just open up the database, open the table in design view and change the field property?
GrahamSkanRetired
Top Expert 2012

Commented:
You need to change the Unique Property of the index object from True to False.
GrahamSkanRetired
Top Expert 2012

Commented:
Using ADOX (ADO DDL)

    Dim catMine As New ADOX.Catalog
    Dim tblMine As New ADOX.Table
    Dim idxMine As New ADOX.Index
   
    ' Connect the catalog
    catMine.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "data source=c:\Program Files\" & _
        "Microsoft Office\Office\Samples\Northwind.mdb;"
    Set tmlMine = catMine.Tables("MyTable")
    Set idxMine = tblMine.Indexes("MyIndex")
    idxMine.Properties("Unique") = False
    Set catMine = Nothing
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Retired
Top Expert 2012
Commented:
Now with some of the typos corrected:

'Using ADOX: Set Reference to Miscrosoft Ext. ?.? for DDL & Security

   Dim catMine As New ADOX.Catalog
   Dim tblMine ADOX.Table
   Dim idxMine As ADOX.Index
 
    ' Connect the catalog
   catMine.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "data source= "c:\mypath\MYDB.mdb;"
   Set tblMine = catMine.Tables("MyTable")
   Set idxMine = tblMine.Indexes("MyIndex")
   idxMine.Properties("Unique") = False
   Set catMine = Nothing

Commented:
m2maths,

   At first you may consider just changing the property in Design mode in Access only to find that, your data does not meet the new constraint.

   Access will be left with trying to figure out what to do with the new records that may indeed not be unique on that given field constraint you set.

   I suggest create a table identical with exception of the Uniqueness constraint, and set the NEW table to the property you want, then use the Access Import/Export Wizard, or ideally DTS Wizard from SQL Server if you have it (MSDE is free, and the DTS Object Library does come with it to my understanding, although no GUI Wizards regarding DTS come with it) to migrate your old table to your new table.


   Access can be a real nuisance with contraints as it misleadingly draws the user of Access into understanding nothing of the concept of 'transasctions', and 'contraints'.    

Hope this idea helps you and also gets you thinking about 'contraints' regarding relational database.

Tim
GrahamSkanRetired
Top Expert 2012

Commented:
tfmiltz,

That info is a good general observation, though it this case m2maths actually wants to remove a constraint.

m2maths,

If this is a one-time requirement, either MrRobin's or tfmiltz's solutions are best, but if you posted this in the VB section because you need it to be repeated automatically, you'll need some sort of code.

Commented:
How you're getting on with this?

Author

Commented:
tfmitz,

GrahamSkan is correct, i need to change the field programatically as this is an update for 80 existing customers, and it is not possible or convenient to re-call all their dbs and make the change manually.

thanks GrakamSkan for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial