Hi.
a couple of questions about creating an access (2007) table with a
multi-column unique constraint.
I know how do the above using an SQL statement within the access program.
i.e.
CREATE TABLE testTBL
(
myID int NOT NULL
myLastName varchar(25) NOT NULL
myOtherField1 int
myOtherField2 int
CONSTRAINT my2FieldsUniqueConstraint UNIQUE (myID, myLastName)
)

question 1... using access 2007 and when in table DESIGN VIEW mode... can
you create the same type table as above (without using an SQL statement)?

question 2... after creating the table above successfully, how can you tell
that it has the two field unique constraint on the table. I checked the
fields properties info section of the two constrained fields and I don't see
anything that would tell me about the Unique two field constraint. Of
course if you try and enter the same data in these two fields on two
different rows you get an error (as expected) but is there some way of
showing the table schema which will tell me this info in case I forgot that
the table was originally created with the unique constraint across the two
fields?

TIA
Ron

Ron wrote:
Index information is not a property of a field. An index
does have a Fields collection, so you were looking at a 1 to
Many type structure the wrong way around. Look at the
table's Indexes window to see the indexes you set using the
table's design UI. The Indexes window does not display the
indexes that were automatically created via enforced
referential intrigrity in the Relationships window.

Using DAO, you can get to the TableDef object's Indexes
property that is the table's collection of indexes
(Constraints). Then you can check each index's Fields
collection to see which fields and their properties make up
the index. (I've never tried it, but I suppose there is a
way to do something similar using ADOX.) It is a good idea
to keep a routine that does that around so you can check if
you have duplicate indexes as you approch the limit on the
number of constraints.

--
Marsh

On 01/08/2010 20:34:39, Marshall Barton wrote:
I may be missing the point but you can have an index called say IDName
consisting of the 2 fields MyID and MyLastName and make that unique. So you
can have 1 Smith 2 Smith
but not a secon 1 smith

Phil

Thanks Marsh... got it, missed that Index dialog box that u pointed out.

"Marshall Barton" <marshbarton (AT) wowway (DOT) com> wrote

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Thanks Phil for your response...
my table works like you say and that is the way it was designed, just
wanted to know how to look at the table say 6 months from now and if I
forgot that it had that double field unique index I wanted to know a way to
RE-DISCOVER that fact from within the Access program... Marsh pointed the
way for me....