Changing field indexed property from code

  • Thread starter Thread starter Christopher
  • Start date Start date
C

Christopher

This question is about changing the indexed property of a linked tabl
field from code. I'm using Access 2000 with SP1.

What ultimately needs to happen is the index property to change fro
"Indexed (Duplicates OK)" to "Indexed (No Duplicates)". I also need t
change this property from code (I don't want the users going into th
back end).

I've tried making a new property and appending it to the field wit
tabledefs("tablename").fields("fieldName").properties.append

I've tried simply setting the .properties("Indexed") = "Indexed (N
Duplicates)"

I've tried doing both of those on the front and back ends. Nothin
seems to work. Is this just not possible to do? Any help would b
greatly appreciated. Thanks in advance.

Christophe
 
I've tried doing both of those on the front and back ends. Nothing
seems to work. Is this just not possible to do?
Click to expand...

It's not that it cannot be done: it's that it is a mighty odd thing to do.
A unique index is a vital part of the data model -- the rule that says "no
two people can share the same SSN number" is one that should only be thrown
away or created as part of a major overhaul. Unique indexes are not
cosmetic and are not performance aids. Of course, we all overlook things at
times, but a basic design error will probably need more than a couple of
indexing tweaks.

That said, in order to change the unique constraint on a field, the easiest
way is with the ALTER TABLE command:

' Create the command
strSQL = "ALTER TABLE MyTable " & _
"ADD CONSTRAINT NoDupsInCol UNIQUE MyIndexedColumn"

' You have to check for errors
On Error Resume Next

' DAO won't work here AFAIK; has to be ADO
CurrentProject().Connection.Execute strsQL

' You must check for errors because the constraint won't have
' been made if there were any dups beforehand
If Err.Number <> 0 Then
MsgBox "Something useful"
' now go back and check the table and remove all duplicates
' before doing this again

Else
MsgBox "Table constraint added successfully"

End If

Hope that helps


Tim F
 
This question is about changing the indexed property of a linked table
field from code. I'm using Access 2000 with SP1.
Click to expand...

Just as a follow up, I stupidly overlooked the "linked tables" part of the
question. You'll need to create a connection to the original mdb: something
like


' I don't think this is right, but I am out of practice with
' ADO
strConn = ";DATABASE=d:\data\mybackend.mdb"

Set con = New ADODB.Connection
con.ConnectionString = strConn


' then use this object for
con.Execute strSQL ' etc...


Hope that helps


Tim F
 
Back
Top