By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

vba for seeing if a field in a table is indexed

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"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)
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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)
Nov 12 '05 #4

P: n/a
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


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.