Get the name of the primary key of a table

[diegomarino]

hi, i want to find the primary key for some table in my db


this is a piece of code that i found, but i cannot understand how to get the name of the field that is primary key, neither if the second for each loops thru the fields in every tabledef

   For Each Tbf In db.TableDefs                   On Error Resume Next
      


                   For Each Idx In Tbf.Indexes
                        If Idx.Primary Then


                           MsgBox (Idx.Name)
                        Else
                           MsgBox (Idx.Name)
                        End If
         Next
      Else
         Rst.MoveNext
      End If
   
   Next

[Welshgasman]

Get rid of the else construct and add the table name to the msgbox?

[Gicu]

Could you please try this:

For Each Tbf In db.TableDefs
On Error Resume Next
   For Each Idx In Tbf.Indexes
      If Idx.Primary = True Then MsgBox (Idx.Fields)                    
   Next idx
Next tdf

Cheers,

[Welshgasman]

Also this https://www.techrepublic.com/blog/mi...-access-table/

[diegomarino]

this is the one! this "fields" gave me a bad impression cause was plural, i didn't think it was the nameof the field

however i have a strange result, the name is correct but there is a "+" before, why?

thanks everyone

Could you please try this:

For Each Tbf In db.TableDefs
On Error Resume Next
   For Each Idx In Tbf.Indexes
      If Idx.Primary = True Then MsgBox (Idx.Fields)                    
   Next idx
Next tdf

Cheers,

[Gicu]

A composite primary key index can\has more than one field so maybe that would explain the plural. Not sure why the + is there but her is what I do to remove it

Dim sPrimary as String
For Each Tbf In db.TableDefs
On Error Resume Next
   For Each Idx In Tbf.Indexes
      If Idx.Primary = True Then
           sPrimary=Idx.Fields
          sPrimary=Replace(sPrimary,"+","")
         MsgBox (sPrimary)                    
   Next idx
Next tdf

Cheers,

[diegomarino]

thanks a lot
however i just used the name of the field as text variable in a query and it works fine

[Micron]

Is this easier than just using the database documenter?

[diegomarino]

sorry, i can't understand what you said

Is this easier than just using the database documenter?

[Gicu]

Depends what you want to do with it. In my case I was analyzing the tables in an Access back-end to present users information such as primary key, indexes, number of records in preparation for upsizing the back-end to SQL server. And based on this analysis next step was to dynamically create primary keys in VBA for the tables that did not have one, so I needed it to be done in VBA.

Cheers,

[Micron]

sorry, i can't understand what you said

Ribbon>Database Tools>Analyse>Database Documenter

If you don't choose options after choosing what to analyze you'll probably get a lot more info than what you need.

[diegomarino]

oh well, that's a good way but i need to dinamycally find indexes cause i use those indexes to find the right column of an excel file in which i search the id value to individuate the right row to insert datas. A little tricky but it's important for me and very satisfying

[diegomarino]

Sorry to ask more, is there a way to know the column number of those primary keys?

[Gicu]

Are you talking about the table column, which would be the field index i like in tdf.fld(i); you could add another loop to go through the fields collection of the current tabledef and compare the field name to the primary key name (would only work for simple PK not multi-field composite ones):

Dim sPrimary as String
Dim i as integer,fld as DAO.Field


For Each Tbf In db.TableDefs
On Error Resume Next
   For Each Idx In Tbf.Indexes
      If Idx.Primary = True Then
           sPrimary=Idx.Fields
          sPrimary=Replace(sPrimary,"+","")
	For i=0 to tdf.Fields.COunt-1
		If fld(i).Name=sPrimary
		   MsgBox (sPrimary & " Column number: " & i)
		   Goto NEXT_TABLE
		End If


	Next i
         MsgBox (sPrimary)                    
   Next idx
NEXT_TABLE:
Next tdf

Or are you talking about Excel column numbers; in that case you would need to implement a similar loop through those columns and check the names.

Cheers,

[diegomarino]

hi, i meant the first solution, but the second one is better now that i looked better to my needs. however i've already tried without success to loop through the excel column, i don't know how to define the range of the loop that should be the number of field in access table, but even if i count that, if fields are 20 in excel the column name is "T", i can't build a code that goes to "a" to "t", where "t" is the column number 20

however i tried this code

For Each Idx In Tbf.Indexes ' cerca tra i campi della tabella quali sono i primary key               i = i + 1
               If Idx.Primary Then
               
                  Fld = Idx.Fields
                  FldName = Idx.Fields 'ritorna il nome del campo primary key
                  MsgBox (Idx.Fields)
                  MsgBox (i)
                  Set Rst = db.OpenRecordset("SELECT " & TbEx & ".[Data/ora creazione], " & TbEx & ".[Data/ora modifica], " & TbEx & ".Autore, " & TbEx & ".[Modificato da] " & vbCrLf & _
                                             "FROM " & TbEx & " INNER JOIN " & Tbf.Name & " ON " & TbEx & "." & FldName & " = " & Tbf.Name & " & FldName & " & vbCrLf & _
                                             "WHERE (((" & TbEx & ".[Data/ora modifica])<[" & Tbf.Name & "].[Data/ora modifica]));") 'controllo se nella tabella che sto considerando al momento alcuni record hanno la data di modifica più recente nel database che nel file excel/tabella collegata, per aggiornare i dati sorgente di powerapps
                  If Rst.RecordCount > 0 Then 'se ci sono record allora faccio l'update del
                     If Rst.BOF Or Rst.EOF Then
                        Rst.MoveFirst
'                        Do Until Rst.EOF
'
'                        Loop
                     End If
                  End If
              End If
         Next

i set i + 1 for every field loop, so if the second field is the primary should give 2, is it correct or it's a bad code?