Create Index in Visual Basic

[Gerry]

Ho do I create an index on a table in visual basic?
I have searched the help files but I am missing something.
thanks

 

[Dirk Goldgar]

Ho do I create an index on a table in visual basic?
I have searched the help files but I am missing something.

You can do it via DAO, manipulating TableDef, Index, and Field objects, but
it's probably easier just to execute a SQL CREATE INDEX statement. For
example,

Dim db As DAO.Database

Set db = CurrentDb ' or open a database object via OpenDatabase

db.Execute _
"CREATE INDEX ixMyField ON MyTable (MyField)", _
dbFailOnError

 

[Tony Toews [MVP]]

Ho do I create an index on a table in visual basic?
I have searched the help files but I am missing something.
thanks

Dirk has one method but I prefer to use the various collections. This code creates
a field and then a unique index on that field.

Set dbsupdate = wrkDefault.OpenDatabase(strDatabasePathandName, True)

' Update the Equipment table
Set tdfUpdate = dbsupdate.TableDefs("Equipment")
With tdfUpdate
Set tdfField = .CreateField("eUnitNbr", dbText, 6)
.Fields.Append tdfField
Set tdfField = .Fields("eUnitNbr")
Set prp = tdfField.CreateProperty("Caption", dbText, "Unit #")
tdfField.Properties.Append prp

Set idxUpdate = .CreateIndex("eUnitNBR")
idxUpdate.Fields.Append idxUpdate.CreateField("eUnitNBR")
idxUpdate.Unique = True
.Indexes.Append idxUpdate
End With

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/