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/comm....public.access

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
http://I.Am/DougSteele
(no e-mails, please!)


"BmlKidd" <(E-Mail Removed)> wrote in message
news:E7B1CE26-E916-47D6-A4A8-(E-Mail Removed)...
>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,
..com/office/community/en-us/default.mspx?mid=e7b1ce26-e916-47d6-a4a8-24c0b5411a50&dg=microsoft.public.access

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

"BmlKidd" wrote:

> 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/comm....public.access

Worked perfectly, Ken, thanks!

"Ken Sheridan" wrote:

> 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
>
> "BmlKidd" wrote:
>
> > 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/comm....public.access
>

"BmlKidd" <(E-Mail Removed)> schrieb im Newsbeitrag
news:E7B1CE26-E916-47D6-A4A8-(E-Mail Removed)...
>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/comm....public.access