Need VBA to update INDEXED property in table(s)

[skydivetom]

Good morning:



Does anyone know how to use VBA which will update the INDEXED property in a table(s)?

Background:
- Attached DB contains 2 tables ([tbl_MISHAP] and [tbl_PERSON])
- For both tables, the INDEXED property is currently set to "No"

What I Would Like to Achieve:
- In the For Loop (below), insert a VBA command (after "Then") to modify the INDEXED property
- Upon execution, the INDEXED property should then equal = "Yes (No Duplicates)"

'Update INDEXED property
Dim t As TableDef
For Each t In CurrentDb.TableDefs
     If t.Name Like "tbl_*" Then UPDATE INDEXED = "Yes (No Duplicates)"
Next

Any thoughts on what the VBA might look like for updating the INDEX property?

[orange]

Tom,

The SQL required to add a Primary key is

ALTER TABLE tbl_PERSON
ADD PRIMARY KEY (ID);


So you would need to know the field name to be used as PK in each table.

if all of your tables use ID, then you could loo with that. But if you have different field names that you'll be indexing as PK, thn you'll need more logic/different approach.

I looked at your sample tables, there is no PK -so maybe I'm missing a general index question rather than PK?????

If you just want an Index and NOT PK, let me know.

Found an example:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

[skydivetom]

Orange:

thank you for the response... I appreciate it. I don't want to create an AUTONUMBER pk. Instead, please see attached DB that contains the following:

- 3 tables. However, given that only 2 tables have table name prefix = "tbl_", I want to update ONLY [tbl_MISHAP] and [tbl_PERSON] based on the ForLoop.
- Form "F01_MainMenu" includes the ForLoop (command button).

Process:
1. Insert the required code that would update any tables (i.e., 2 tables in this example) with prefix "tbl_".
2. All tables starting with prefix "tbl_" contain field [MSHP_LEGACY_REPORT_NUMBER].
3. Instead of adding/changing the autonumber field, I merely would like to change the INDEX property from "No" to "Yes (No Duplicates)" for [MSHP_LEGACY_REPORT_NUMBER].
4. If necessary, this field can be declared inside/outside the ForLoop.

I hope this makes sense.

Cheers,
Tom

[skydivetom]

Please see last thread #3 first.

Attached is updated DB with VBA... both lines are in color 'red'. Not sure if that's due to incorrect syntax.

[orange]

Try this

 Option Compare Database
Option Explicit

Private Sub cmdUpdateProductTables_Click()
10        On Error GoTo cmdUpdateProductTables_Click_Error
          Dim fieldForIndex As String
20        fieldForIndex = "MSHP_LEGACY_REPORT_NUMBER"
          Dim sSQL1 As String, sSQL2 As String, sqlFinal As String
          Dim indexName As String
          Dim i As Integer
30        sSQL1 = "CREATE UNIQUE INDEX "
40        sSQL2 = " ON  "
          'Declare variables
          Dim tdf As DAO.TableDef

          'Perform count of the product tables
50        For Each tdf In CurrentDb.TableDefs
60            If Left(tdf.Name, 4) = "tbl_" Then
70                i = i + 1
80                sqlFinal = sSQL1 & tdf.Name & i & sSQL2 & tdf.Name & "(" & fieldForIndex & ");"
                 
90                Debug.Print sqlFinal
100              CurrentDb.Execute sqlFinal, dbFailOnError
                  'Commented out next line since I want to alter **all** tables that have table name prefix = "tbl_"
                  'ALTER TABLE tbl_PERSON
                  '    Alter TABLE tdf
                  
                  
                  'I don't want to add a primary key (e.g., autonumber).
                  'Instead, all tables with prefix "tbl_" will contain field [MSHP_LEGACY_REPORT_NUMBER].
                  'ADD PRIMARY KEY (ID);
                  '... for example SET [MSHP_LEGACY_REPORT_NUMBER].INDEX = "Yes (No Duplicates)"
                  
                  
                  '   CREATE UNIQUE INDEX index_name ON tdf (MSHP_LEGACY_REPORT_NUMBER);
                  
                  
                  
110           End If
120       Next tdf
          
          'Throw message box
130       MsgBox "If VBA is correct, then INDEXED property should have been updated.", vbInformation, "Status Message"

          
140       On Error GoTo 0
cmdUpdateProductTables_Click_Exit:
150       Exit Sub

cmdUpdateProductTables_Click_Error:
160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdateProductTables_Click, line " & Erl & "."
170       GoTo cmdUpdateProductTables_Click_Exit
End Sub

[skydivetom]

Orange:

Wow... your solution is BRILLIANT!!!! It does *exactly* what I hoped to accomplish. Many, many thanks!!

Cheers,
Tom

[orange]

Tom,
You're welcome.

Another way using vba - based on Allen Browne DAO examples.

I added another button to your form, created 2 new tables jtblA, jtblB.
Each has a field "JMSHP_LEGACY_REPORT_NUMBER" which I wanted to be Unique Index.

Note that the field name in each table is the same (similar to your example).
But the index name is the same in each table. I could have made them different, but because they are indexs on different tables, the names could be the same.

Private Sub Command233_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim ind As DAO.Index
    'Initialize
    Set db = CurrentDb()
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 2) = "jt" Then
            Set ind = tdf.CreateIndex("JMSHP_LEGACY_REPORT_NUMBER")
            With ind
                .Fields.Append ind.CreateField("JMSHP_LEGACY_REPORT_NUMBER")
                .Unique = True
                tdf.Indexes.Append ind
            End With
        End If
    Next tdf
End Sub

[skydivetom]

Cool!!! This function has fewer lines to maintain... I like it!!

Thanks again for your help... 'very much appreciated.