If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Assign Index key to field programmatically
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).Va lue) 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("Descri ption", 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 |
#2
|
|||
|
|||
Assign Index key to field programmatically
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" wrote in message ... 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).Va lue) 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("Descri ption", 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 |
#3
|
|||
|
|||
Assign Index key to field programmatically
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 procedu 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).Va lue) 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("Descri ption", 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 |
#4
|
|||
|
|||
Assign Index key to field programmatically
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 procedu 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).Va lue) 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("Descri ption", 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 |
#5
|
|||
|
|||
Assign Index key to field programmatically
"BmlKidd" schrieb im Newsbeitrag ... 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).Va lue) 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("Descri ption", 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 |
Thread Tools | |
Display Modes | |
|
|