vba for seeing if a field in a table is indexed

[dixie]

I have a table called "tblParticipants" with a field called "ID", which must
be indexed with No duplicates allowed. I am looking for a way of doing in
vba a small if ..then ..else .. routine that looks at that field in that
table and if it is not indexed, indexes it, and if it is indexed, jumps to a
label. I have a query called "qryAddIndex" that will index the field, what
I really need is the vba to test if the field is indexed or not.

dixie

[Lyle Fairfield]

"dixie" <di****@dogmail.com> wrote in
news:oZ****************@nnrp1.ozemail.com.au:

I have a table called "tblParticipants" with a field called "ID", which
must be indexed with No duplicates allowed. I am looking for a way of
doing in vba a small if ..then ..else .. routine that looks at that
field in that table and if it is not indexed, indexes it, and if it is
indexed, jumps to a label. I have a query called "qryAddIndex" that
will index the field, what I really need is the vba to test if the field
is indexed or not.

dixie

Assuming >= AC2K

you could try

Public Function IsColumnUniquelyIndexed(ByVal TableName As String, ByVal
ColumnName As String) As Boolean
IsColumnUniquelyIndexed = Not CurrentProject.Connection.OpenSchema
(adSchemaConstraintColumnUsage, Array(Empty, Empty, TableName,
ColumnName)).BOF
End Function

Sub TestIsColumnUniquelyIndexed()
MsgBox IsColumnUniquelyIndexed("tbl2002Transactions", "fldDate")
' returns true
End Sub
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Mike Storr]

Unless I'm mis-understanding you, indexing the field is done in table
design, and stays that way unless you change it.
It's not a per record type of property.

Mike Storr
www.veraccess.com
"dixie" <di****@dogmail.com> wrote in message
news:oZ****************@nnrp1.ozemail.com.au...

I have a table called "tblParticipants" with a field called "ID", which must be indexed with No duplicates allowed. I am looking for a way of doing in
vba a small if ..then ..else .. routine that looks at that field in that
table and if it is not indexed, indexes it, and if it is indexed, jumps to a label. I have a query called "qryAddIndex" that will index the field, what I really need is the vba to test if the field is indexed or not.

dixie

[Lyle Fairfield]

Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:

"dixie" <di****@dogmail.com> wrote in
news:oZ****************@nnrp1.ozemail.com.au:

I have a table called "tblParticipants" with a field called "ID", which
must be indexed with No duplicates allowed. I am looking for a way of
doing in vba a small if ..then ..else .. routine that looks at that
field in that table and if it is not indexed, indexes it, and if it is
indexed, jumps to a label. I have a query called "qryAddIndex" that
will index the field, what I really need is the vba to test if the field
is indexed or not.

dixie

Assuming >= AC2K

you could try

Public Function IsColumnUniquelyIndexed(ByVal TableName As String, ByVal
ColumnName As String) As Boolean
IsColumnUniquelyIndexed = Not CurrentProject.Connection.OpenSchema
(adSchemaConstraintColumnUsage, Array(Empty, Empty, TableName,
ColumnName)).BOF
End Function

Sub TestIsColumnUniquelyIndexed()
MsgBox IsColumnUniquelyIndexed("tbl2002Transactions", "fldDate")
' returns true
End Sub

Well ... I suppose some might find the preceding obscure:

so you might use:

Public Function IsColumnUniquelyIndexed2(ByVal TableName As String, ByVal
ColumnName As String) As Boolean
WizHook.Key = 51488399
IsColumnUniquelyIndexed2 = WizHook.TableFieldHasUniqueIndex(TableName,
ColumnName)
End Function

Sub temp2()
MsgBox IsColumnUniquelyIndexed2("tbl2002Transactions",
"fldTransactionID")
' shows true
End Sub

Of course, be careful with line breaks.

The nice thing about the first is that it returns true for any column which
is part of a unique index, while the second does not, dealing with only
single column indexes, it SEEMS. One would think one could send more than
one column name to the second, but I have not found a way to do this.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[dixie]

Hi Mike, I am actually exporting a recordset using Docd.TransferDatabase
into another database and when it arrives at the other database, the primary
key and indexes have disappeared. The application needs the indexing on the
ID field to be there. The version is A2k.

dixie

"Mike Storr" <st******@sympatico.ca> wrote in message
news:dE******************@news20.bellglobal.com...

Unless I'm mis-understanding you, indexing the field is done in table
design, and stays that way unless you change it.
It's not a per record type of property.

Mike Storr
www.veraccess.com
"dixie" <di****@dogmail.com> wrote in message
news:oZ****************@nnrp1.ozemail.com.au...

I have a table called "tblParticipants" with a field called "ID", which must

be indexed with No duplicates allowed. I am looking for a way of doing in vba a small if ..then ..else .. routine that looks at that field in that
table and if it is not indexed, indexes it, and if it is indexed, jumps

to a

label. I have a query called "qryAddIndex" that will index the field,

what

I really need is the vba to test if the field is indexed or not.

dixie