Check for primary key

[PhilOfWalton]

I am trying to check if a field in a table is the Primary Key
I am using this code adapted from Allen Brown.

Option Compare Database
Option Explicit
 
Private Function DescribeIndexField(TableName As String, strField As String) As String
    ' Purpose:   Indicate if the field is part of a primary key or unique index.
    ' Return:    String containing "P" if primary key, "U" if uniuqe index,
    ' "I" if non-unique index.
    ' Lower case letters if secondary field in index. Can have multiple indexes.
    ' Arguments: TableName = the Table the field belongs to.
    '           strField = name of the field to search the Indexes for.
 
    Dim ind As DAO.Index        'Each index of this table.
    Dim fld As DAO.Field        'Each field of the index
    Dim iCount As Integer
    Dim strReturn As String     'Return string
    Dim Tdf As TableDef
 
    Set Tdf = CurrentDb.CreateTableDef(TableName)
 
    For Each ind In Tdf.Indexes
        iCount = 0
        For Each fld In ind.Fields
            If fld.Name = strField Then
                If ind.Primary Then
                    strReturn = strReturn & IIf(iCount = 0, "P", "p")
                ElseIf ind.Unique Then
                    strReturn = strReturn & IIf(iCount = 0, "U", "u")
                Else
                    strReturn = strReturn & IIf(iCount = 0, "I", "i")
                End If
            End If
            iCount = iCount + 1
        Next
    Next
 
    Set Tdf = Nothing
 
    DescribeIndexField = strReturn
 
End Function

It appears to run OK, but gives no result

If I type

?tdf.Indexes(1).count

in the immediate window, I get 0 indexes. There are in fact 4.

What am I doing wrong?

Phil

[PhilOfWalton]

Whoops, the Immediate window should have said

?tdf.Indexes.Count

The "(1)" wasn't there.

Phil

[NeoPa]

Would I be correct if I guessed that the original code from AB had a different version of line #18?

Set Tdf = CurrentDb().TableDefs(TableName)

It seems bizarre to try to count the indices of a table that's only just been created from scratch. There should be none. If there were any I'd be worried.

[twinnyfo]

A learner’s question, here: Does one field have more than one index? It certainly appears that you are not counting the indexes of the TabelDef but of one particular field in that TabeDef. Is that correct?

[PhilOfWalton]

Hi guys Thanks for coming back.

This is the original code

Private Function DescribeIndexField(tdf As DAO.TableDef, strField As String) 
As String
    'Purpose:   Indicate if the field is part of a primary key or unique 
index.
    'Return:    String containing "P" if primary key, "U" if uniuqe index, 
"I" if non-unique index.
    '           Lower case letters if secondary field in index. Can have 
multiple indexes.
    'Arguments: tdf = the TableDef the field belongs to.
    '           strField = name of the field to search the Indexes for.
    Dim ind As DAO.Index        'Each index of this table.
    Dim fld As DAO.Field        'Each field of the index
    Dim iCount As Integer
    Dim strReturn As String     'Return string
 
    For Each ind In tdf.Indexes
        iCount = 0
        For Each fld In ind.Fields
            If fld.Name = strField Then
                If ind.Primary Then
                    strReturn = strReturn & IIf(iCount = 0, "P", "p")
                ElseIf ind.Unique Then
                    strReturn = strReturn & IIf(iCount = 0, "U", "u")
                Else
                    strReturn = strReturn & IIf(iCount = 0, "I", "i")
                End If
            End If
            iCount = iCount + 1
        Next
    Next
 
    DescribeIndexField = strReturn
End Function

To give a bit background, I am trying to import an Excel file into Access. Now a load of cells in the Excel files are repeated, so those need to go into "Lookup Type" tables in Access.

I am creating a table of cell names, the equivalent Access Table and Field that they have to be appended to, the Primary Key for that table and the matching Foreign key in the main table. So hopefully, after appending the data to the "Lookup" Table, I can append the value of the "Lookup" Table's Primary Key to the Main Table's equivalent foreign key.

Back to the question.
I wanted to adapt the original code as I know the name of the table and I know the name of the field. I just want to check that it is the correct field by confirming that it is the Table's Primary Key

Phil

[Rabbit]

I think NeoPa's answer is correct, you want to access an existing table def, not create one.

[PhilOfWalton]

Thanks for your input, Neopa was on the right track

The first attempt which doesn't work is:-

For Each ind In Current.TableDefs(TableName).Indexes

This gives error 3240 - Object invalid or no longer set.

What does work is

    Dim MyDb as Database
    Set MyDb = CurrentDb
 
    For Each ind In MyDb.TableDefs(TableName).Indexes
 

Apparently, until I had a look at another article by Neopa, CurrentDb is transient.

Thanks again. Problem solved.

Phil

[NeoPa]

CurrentDb() is indeed transient - however that's not what caused the issue. Your code was trying to check the number of indices of the table you'd only just created from scratch a line or two of code previously.

It was working perfectly of course, in telling you that none had been created at that point. A bit like building a new house then trying to look up previous occupants in the Land Registry. They wouldn't be able to give you any info other than that there has been none.