You can ***not*** rely on any consistency in automatically
created names!
Be careful of the Fields property. If used by itself, it is
a somewhat unusual string. If used as a collection, it
behaves as you would expect.
I agree that it may be cumbersome, but to do what you want
for a table's Indexes, search through the Fields collection
of each Index for the specific field names. Once you've
identified the proper index, you can do other things with it
(e.g. delete method, name property, etc).
The same kind of approach can be used for relations, except
for the serious complication that when you create a
relation, Access automatically creates a hidden index for
the foreign key in the many side table (even if it
duplicates a manually created index). You can not delete
these internally created indexes unless you delete the
relation. These internal indexes are members of the table's
Indexes collection and you will stumble over them while
iterating through the indexes collection even though they do
not appear in the table's design view index window.
Although I have no proof, I think the internally created
indexes can be identified because their Foreign property is
is set to True.
Obviously, after all this discussion, it behooves you to
never again fiddle with a deployed back end database's
structure. It requires very deliberate thought and a
deployment strategy before a change should be made.
--
Marsh
MVP [MS Access]
The index & relation names are the way I am doing it now, but that assumes
that when I simply did a manual (drag-and-drop) relationship to the same
fields in two different BE's at different times, that it gave it the same
name to both.
Let me give you a simple example: I have a PK/FK relation between fields in
two tables. At some point early in development, I needed to change the field
size on one side. This, of course, required deleting the relation, changing
the field size on both sides, then re-creating the relation.
I did this manually on each of the two client BE's, because it wasn't worth
writing a patch at the time. Now I need to change that relation from
CascadeUpdate to CascadeDelete, but I am working towards issuing these
changes as patches instead of doing the work manually (I hope the reason is
obvious - this is a work in progress, and as I get more clients using this
application while it is still under development, I will constantly be needing
access to their BE's to update them manually).
I know that Access uses a standard naming convention for manually-created
(i.e. not programmatically-created) indexes & relations, but I don't want to
leave anything to inference when modifying table/relation/index structures
for my clients. Furthermore, I very often have to remove & re-create
relations within my development environment, and that is the one I would
likely use as a model for the index name & relation name when creating
patches.
If, for any reason, the relation got named differently in either live BE or
my development BE when I re-created it after the field size change, then I
will not be able to refer to the relation by name.
I am assuming, from what you said, that I can search through the relations
collection until I find one whose Table & ForeignTable properties match the
one I want, and then delete it so that I can re-create it with a standard
name.
Of course, much of the above also applies to the Indexes collection. I can
look through the list of properties to find the answer to this question, but
perhaps you already know: is there a FieldName property for an element of the
Indexes collection so that I can identify the Index if I know the field but
cannot guarantee the Index name?
Would it just be better to send out one massive patch now that loops through
the Relations and Indexes collections, deleting each one until there are no
more, and then creates them all with the names I have put into the patch?
My concern here is that as I distribute BE's to future clients while my
applications are still under development, the name of some index or relation
may end up different from the cognate one in another client's BE, and then
how do I programmatically modify a property of an index or relation via a
common patch sent to all clients?
Or, what if I indexed a field manually for one client but forgot to do it
for the other one? If I don't realize that I did it for the first one and
send out a patch to create te index, it will generate an error for the first
client because the index already exists.
I never gave a thought to all this version control stuff when I started
working with Access... sorry about the length of the post - perhaps I just
need some instruction on best practices in managing the underlying structures
in an envirnoment where I am distributing patches to multiple clients.
