Assign Index key to field programmatically

B

BmlKidd

I scanned a table to create new tables via:

Private Sub createtables()
Dim NewTableName As TableDef
Dim NewTableProp
Dim PartPrefixDB
Dim i
Dim KeepMe
Set PartPrefixDB = CurrentDb.OpenRecordset("Parts Prefixes")
With PartPrefixDB
.OpenRecordset.MoveFirst
For i = 1 To .RecordCount
'Create New Table
Set NewTableName =
CurrentDb.CreateTableDef(PartPrefixDB.Fields(0).Value)
With NewTableName
KeepMe = NewTableName.Name
'Insert required fields
.Fields.Append .CreateIndex("seDOCNUM")
.Fields.Append .CreateField("seTITLE", dbText, 40)
.Fields.Append .CreateField("seNOTES", dbMemo)
.Fields.Append .CreateField("seUM", dbText, 3)
.Fields.Append .CreateField("seUSER", dbText, 10)
.Fields.Append .CreateField("DATE", dbDate)
'Append new Table
CurrentDb.TableDefs.Append NewTableName
'Create,populate and append new Description field
Set NewTableProp =
CurrentDb.TableDefs(KeepMe).CreateProperty("Description", dbText,
PartPrefixDB.Fields(1).Value)
CurrentDb.TableDefs(KeepMe).Properties.Append NewTableProp
End With
.MoveNext
Next
End With
End Sub

How can I now edit .Field(0) in each table to be Indexed - "Yes, No
Duplicates" ?

The tables are all still empty so if it easier to do during creation, I can
start over.

Thanks,

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-a4a8-24c0b5411a50&dg=microsoft.public.access
 
D

Douglas J. Steele

Look up the CreateIndex method in the Help file. You'll need something like

Dim idxNew As DAO.Index

With NewTableName
Set idxNew = .CreateIndex("MyIndex")
With idxNew
.Fields.Append .CreateField(NewTableName.Fields(0))
End With
idxNew.Unique = True
.Indexes.Append idxNew
End With


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BmlKidd said:
I scanned a table to create new tables via:

Private Sub createtables()
Dim NewTableName As TableDef
Dim NewTableProp
Dim PartPrefixDB
Dim i
Dim KeepMe
Set PartPrefixDB = CurrentDb.OpenRecordset("Parts Prefixes")
With PartPrefixDB
.OpenRecordset.MoveFirst
For i = 1 To .RecordCount
'Create New Table
Set NewTableName =
CurrentDb.CreateTableDef(PartPrefixDB.Fields(0).Value)
With NewTableName
KeepMe = NewTableName.Name
'Insert required fields
.Fields.Append .CreateIndex("seDOCNUM")
.Fields.Append .CreateField("seTITLE", dbText, 40)
.Fields.Append .CreateField("seNOTES", dbMemo)
.Fields.Append .CreateField("seUM", dbText, 3)
.Fields.Append .CreateField("seUSER", dbText, 10)
.Fields.Append .CreateField("DATE", dbDate)
'Append new Table
CurrentDb.TableDefs.Append NewTableName
'Create,populate and append new Description field
Set NewTableProp =
CurrentDb.TableDefs(KeepMe).CreateProperty("Description", dbText,
PartPrefixDB.Fields(1).Value)
CurrentDb.TableDefs(KeepMe).Properties.Append NewTableProp
End With
.MoveNext
Next
End With
End Sub

How can I now edit .Field(0) in each table to be Indexed - "Yes, No
Duplicates" ?

The tables are all still empty so if it easier to do during creation, I
can
start over.

Thanks,
Click to expand...
..com/office/community/en-us/default.mspx?mid=e7b1ce26-e916-47d6-a4a8-24c0b5411a50&dg=microsoft.public.access
 
K

Ken Sheridan

You can do it after creating the tables provided the column in question in
each table does not yet contain any duplicate values. Add the following
procedure to a module:

Public Sub AddUniqueIndex(strTable As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(0)
Set idx = tdf.CreateIndex(fld.Name)

With idx
.Fields.Append .CreateField(fld.Name)
.Unique = True
End With

tdf.Indexes.Append idx

End Sub

This will create a unique index of the same name as the field on the first
field in each table's Fields collection.

Then, firstly making sure none of the tables are open, loop through a Parts
Prefixes recordset as you did before and, instead of creating the table, call
the procedure at each iteration of the loop, passing the name into the above
procedure:

AddUniqueIndex PartPrefixDB.Fields(0)

Ken Sheridan
Stafford, England
 
B

BmlKidd

Worked perfectly, Ken, thanks!

Ken Sheridan said:
You can do it after creating the tables provided the column in question in
each table does not yet contain any duplicate values. Add the following
procedure to a module:

Public Sub AddUniqueIndex(strTable As String)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)
Set fld = tdf.Fields(0)
Set idx = tdf.CreateIndex(fld.Name)

With idx
.Fields.Append .CreateField(fld.Name)
.Unique = True
End With

tdf.Indexes.Append idx

End Sub

This will create a unique index of the same name as the field on the first
field in each table's Fields collection.

Then, firstly making sure none of the tables are open, loop through a Parts
Prefixes recordset as you did before and, instead of creating the table, call
the procedure at each iteration of the loop, passing the name into the above
procedure:

AddUniqueIndex PartPrefixDB.Fields(0)

Ken Sheridan
Stafford, England
Click to expand...
 
M

Manfred Melikewitz

BmlKidd said:
I scanned a table to create new tables via:

Private Sub createtables()
Dim NewTableName As TableDef
Dim NewTableProp
Dim PartPrefixDB
Dim i
Dim KeepMe
Set PartPrefixDB = CurrentDb.OpenRecordset("Parts Prefixes")
With PartPrefixDB
.OpenRecordset.MoveFirst
For i = 1 To .RecordCount
'Create New Table
Set NewTableName =
CurrentDb.CreateTableDef(PartPrefixDB.Fields(0).Value)
With NewTableName
KeepMe = NewTableName.Name
'Insert required fields
.Fields.Append .CreateIndex("seDOCNUM")
.Fields.Append .CreateField("seTITLE", dbText, 40)
.Fields.Append .CreateField("seNOTES", dbMemo)
.Fields.Append .CreateField("seUM", dbText, 3)
.Fields.Append .CreateField("seUSER", dbText, 10)
.Fields.Append .CreateField("DATE", dbDate)
'Append new Table
CurrentDb.TableDefs.Append NewTableName
'Create,populate and append new Description field
Set NewTableProp =
CurrentDb.TableDefs(KeepMe).CreateProperty("Description", dbText,
PartPrefixDB.Fields(1).Value)
CurrentDb.TableDefs(KeepMe).Properties.Append NewTableProp
End With
.MoveNext
Next
End With
End Sub

How can I now edit .Field(0) in each table to be Indexed - "Yes, No
Duplicates" ?

The tables are all still empty so if it easier to do during creation, I
can
start over.

Thanks,

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-a4a8-24c0b5411a50&dg=microsoft.public.access
Click to expand...
 
Top