423,485 Members | 1,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,485 IT Pros & Developers. It's quick & easy.

Check for primary key

PhilOfWalton
Expert 100+
P: 1,353
I am trying to check if a field in a table is the Primary Key
I am using this code adapted from Allen Brown.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Function DescribeIndexField(TableName As String, strField As String) As String
  5.     ' Purpose:   Indicate if the field is part of a primary key or unique index.
  6.     ' Return:    String containing "P" if primary key, "U" if uniuqe index,
  7.     ' "I" if non-unique index.
  8.     ' Lower case letters if secondary field in index. Can have multiple indexes.
  9.     ' Arguments: TableName = the Table the field belongs to.
  10.     '           strField = name of the field to search the Indexes for.
  11.  
  12.     Dim ind As DAO.Index        'Each index of this table.
  13.     Dim fld As DAO.Field        'Each field of the index
  14.     Dim iCount As Integer
  15.     Dim strReturn As String     'Return string
  16.     Dim Tdf As TableDef
  17.  
  18.     Set Tdf = CurrentDb.CreateTableDef(TableName)
  19.  
  20.     For Each ind In Tdf.Indexes
  21.         iCount = 0
  22.         For Each fld In ind.Fields
  23.             If fld.Name = strField Then
  24.                 If ind.Primary Then
  25.                     strReturn = strReturn & IIf(iCount = 0, "P", "p")
  26.                 ElseIf ind.Unique Then
  27.                     strReturn = strReturn & IIf(iCount = 0, "U", "u")
  28.                 Else
  29.                     strReturn = strReturn & IIf(iCount = 0, "I", "i")
  30.                 End If
  31.             End If
  32.             iCount = iCount + 1
  33.         Next
  34.     Next
  35.  
  36.     Set Tdf = Nothing
  37.  
  38.     DescribeIndexField = strReturn
  39.  
  40. End Function
It appears to run OK, but gives no result

If I type
Expand|Select|Wrap|Line Numbers
  1. ?tdf.Indexes(1).count
in the immediate window, I get 0 indexes. There are in fact 4.

What am I doing wrong?

Phil
1 Week Ago #1

✓ answered by NeoPa

Would I be correct if I guessed that the original code from AB had a different version of line #18?
Expand|Select|Wrap|Line Numbers
  1. 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.

Share this Question
Share on Google+
7 Replies


PhilOfWalton
Expert 100+
P: 1,353
Whoops, the Immediate window should have said
Expand|Select|Wrap|Line Numbers
  1. ?tdf.Indexes.Count
The "(1)" wasn't there.

Phil
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 31,034
Would I be correct if I guessed that the original code from AB had a different version of line #18?
Expand|Select|Wrap|Line Numbers
  1. 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.
6 Days Ago #3

twinnyfo
Expert Mod 2.5K+
P: 2,605
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?
6 Days Ago #4

PhilOfWalton
Expert 100+
P: 1,353
Hi guys Thanks for coming back.

This is the original code
Expand|Select|Wrap|Line Numbers
  1. Private Function DescribeIndexField(tdf As DAO.TableDef, strField As String) 
  2. As String
  3.     'Purpose:   Indicate if the field is part of a primary key or unique 
  4. index.
  5.     'Return:    String containing "P" if primary key, "U" if uniuqe index, 
  6. "I" if non-unique index.
  7.     '           Lower case letters if secondary field in index. Can have 
  8. multiple indexes.
  9.     'Arguments: tdf = the TableDef the field belongs to.
  10.     '           strField = name of the field to search the Indexes for.
  11.     Dim ind As DAO.Index        'Each index of this table.
  12.     Dim fld As DAO.Field        'Each field of the index
  13.     Dim iCount As Integer
  14.     Dim strReturn As String     'Return string
  15.  
  16.     For Each ind In tdf.Indexes
  17.         iCount = 0
  18.         For Each fld In ind.Fields
  19.             If fld.Name = strField Then
  20.                 If ind.Primary Then
  21.                     strReturn = strReturn & IIf(iCount = 0, "P", "p")
  22.                 ElseIf ind.Unique Then
  23.                     strReturn = strReturn & IIf(iCount = 0, "U", "u")
  24.                 Else
  25.                     strReturn = strReturn & IIf(iCount = 0, "I", "i")
  26.                 End If
  27.             End If
  28.             iCount = iCount + 1
  29.         Next
  30.     Next
  31.  
  32.     DescribeIndexField = strReturn
  33. 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
6 Days Ago #5

Rabbit
Expert Mod 10K+
P: 12,266
I think NeoPa's answer is correct, you want to access an existing table def, not create one.
6 Days Ago #6

PhilOfWalton
Expert 100+
P: 1,353
Thanks for your input, Neopa was on the right track

The first attempt which doesn't work is:-
Expand|Select|Wrap|Line Numbers
  1. For Each ind In Current.TableDefs(TableName).Indexes
This gives error 3240 - Object invalid or no longer set.

What does work is
Expand|Select|Wrap|Line Numbers
  1.     Dim MyDb as Database
  2.     Set MyDb = CurrentDb
  3.  
  4.     For Each ind In MyDb.TableDefs(TableName).Indexes
  5.  
Apparently, until I had a look at another article by Neopa, CurrentDb is transient.

Thanks again. Problem solved.

Phil
6 Days Ago #7

NeoPa
Expert Mod 15k+
P: 31,034
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.
3 Days Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.