setting a primary key with vba (1 Viewer)

[schniggeldorf]

Hi:

I'm trying to import several spreadsheets from Excel into Access, then perform some "clean up" on the data. I have been able to import the spreadsheets into access tables, and my first task thereafter is to establish a primary key for each of the new tables.

I found this code on the web and modified it to fit my situation. However, whenever I get to the .indexes.append line, I get an "object doesn't support this property or method" error. I've looked all over the web trying to find an answer, but haven't been able to do so. Can anybody help me figure out where I've gone wrong?

Public Sub setPrimaryKey(strTblName As String, strFieldName As String)
    
On Error GoTo err_Handler
 
    Dim tdf As TableDef
    Dim bTableFound As Boolean
    Dim myField As Field
    Dim bFieldFound As Boolean
    Dim idxLoop As Index
    Dim idxNew As Index
    
    For Each tdf In CurrentDb.TableDefs
      If tdf.Name = strTblName Then
        bTableFound = True
        Exit For
      End If
    Next tdf
 
    If Not bTableFound Then
        MsgBox "Table not found"
        Exit Sub
    End If

    For Each myField In tdf.Fields
        If myField.Name = strFieldName Then
            bFieldFound = True
            Exit For
        End If
    Next myField
 
    If Not bFieldFound Then
        MsgBox "Field not found"
        Exit Sub
    End If
 
    'Delete the current primary indexes if it exists
    For Each idxLoop In tdf.Indexes
        If idxLoop.Primary Then
            'Existing primary index found, delete it
            tdf.Indexes.Delete idxLoop.Name
            Exit For
        End If
    Next
 
'    Add new index
    With tdf
        Set idxNew = .CreateIndex("myPrimary")
        With idxNew
            .Fields.Append .CreateField(strFieldName)
            .Primary = True
        End With
        .Indexes.Append idxNew
    End With
 
exit_Sub:
    Set myField = Nothing
    Set idxNew = Nothing
    Set tdf = Nothing
    Exit Sub
 
err_Handler:
    MsgBox "Error [" & Err.Number & "] occured." & vbNewLine & Err.desscription
    GoTo exit_Sub
End Sub

 

[JHB]

... .indexes.append line,

Where is that code line?

 

[Galaxiom]

Check the Locals windows at the break to see if tdf is what you expect.

 

[Cronk]

Reverse the order ie

   .Primary = True
      .Fields.Append .CreateField(strFieldName)

The Primary property becomes read only after it's appended