Find out if field is Primary key or no

[aqif]

Hi

I am trying to create a code by which I can append the field properties in a seperate table to get database report. I have been able to get Tables and field names along with thier descriptions and other properties. I can't seem to find any way to find out that whether the field is primary key or indexed or no. Lets look at my sample simplified code

'---------------------------------
On Error Resume Next
Dim db As DAO.Database
Dim Rst As DAO.Recordset
Dim tblName As TableDef
Dim fldName As DAO.Field

Set db = CurrentDb


strTableName = "TblTables"

For Each fldName In db.TableDefs(strTableName).Fields
MsgBox (fldName.Name)
Msgbox (fldName.Properties("Description")
Msgbox (fldName.Properties("Required")

Next
'----------------------------------------

What I want to do is to get something like

Msgbox (fldName.Properties("Primary") or
Msgbox (fldName.Properties("Index") etc

Any suggestions?

Cheers!
ÙÇãá

 

[Pat Hartman]

You need to reference the indexes collection to find the indexes for a table. Here's a code fragment from my documentation db.

    For Each tblLoop In db.TableDefs
        On Error GoTo ErrorHandler
        For Each idxLoop In tblLoop.Indexes
            If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 1) = "z" Or Left(tblLoop.Name, 1) = "~" Then
            Else
                Position = 1
                For Each fldLoop In idxLoop.Fields
                    TempSet1.AddNew
                    TempSet1!TableName = tblLoop.Name
                    TempSet1!IndexName = idxLoop.Name
                    TempSet1!Unique = idxLoop.Unique
                    TempSet1!OrdinalPosition = Position
                    TempSet1!FieldName = fldLoop.Name
                    TempSet1.Update
                    Position = Position + 1
                Next fldLoop
            End If
        Next idxLoop
    Next tblLoop

 

[aqif]

Code not working

Hi,

I just tried ur solution and its giving me error of

"Object variable or with block variable not set"

I know I am doing a small mistake, would you be kind enough to see my actual code and point out mistakes. I have already created the table according to code.

Dim DB As DAO.Database
Dim idxLoop As DAO.Index
Dim fldloop As DAO.Field
Dim tblloop As DAO.TableDef

For Each tblloop In DB.TableDefs
On Error Resume Next

For Each idxLoop In tblloop.Indexes
If Left(tblloop.Name, 4) = "MSys" Or Left(tblloop.Name, 1) = "z" Or Left(tblloop.Name, 1) = "~" Then
Else
Position = 1
For Each fldloop In idxLoop.Fields
TempSet1.AddNew
TempSet1!TableName = tblloop.Name
TempSet1!IndexName = idxLoop.Name
TempSet1!Unique = idxLoop.Unique
TempSet1!OrdinalPosition = Position
TempSet1!Fieldname = fldloop.Name
TempSet1.Update
Position = Position + 1
Next fldloop
End If
Next idxLoop
Next tblloop

Thanks for your help

Aqif

 

[Pat Hartman]

I did say the code was a fragment. The code I posted is incomplete and was used as part of a sub that appends rows to a table. I left all that stuff in so you could see the properties that are available. You need to change the code to do whatever you want it to do.

The name of a primary key is always PrimaryKey. So, if you check the index name for that value, you can determine which index is the primary key.

 

[Helix]

9522 posts? Isn't that overdoing it a bit, Pat?

 

[Pat Hartman]

Maybe I'll stop at 10,000 and retire.

 

[markw707]

Hey, seven years later, here it is!

Sub ShowTablesWithoutPrimaryKey()
'This procedure populates the table called tblListOfTablesWithoutPrimaryKey with the
'names of tables that do not have a Primary key. Please note that ODBC linked tables
'are NOT checked by this procedure. For ODBC tables, this procedure should be copied
'into the respective back-end database and run there to check for tables without
'primary keys.
 
    On Error GoTo ErrorHandler
 
    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Dim idx As DAO.Index
    Dim CountOfIndexesCheckedSoFar As Integer
    Dim strSQL As String
    Dim CountMe As Integer
 
    Set db = CurrentDb
    CountMe = 0
 
    'Clear the table that holds the names of tables without Primary keys
    strSQL = "DELETE * FROM tblListOfTablesWithoutPrimaryKey"
 
    DoCmd.RunSQL (strSQL)
 
    'Loop thru all tables in the database
    For Each tb In db.TableDefs
 
 'if table is not an ODBC linked table, proceed.
        If Left(tb.Connect, 4) <> "ODBC" Then 
            CountOfIndexesCheckedSoFar = 0
 
            'Loop thru all indexes in this table
            For Each idx In tb.Indexes
 
                If Left(tb.Name, 4) = "MSys" Or Left(tb.Name, 1) = "~" Then
                    'nothing
                Else
                    If idx.Name = "PrimaryKey" Then
                        GoTo LeaveThisTable
                    Else
                        CountOfIndexesCheckedSoFar = CountOfIndexesCheckedSoFar + 1
                        If CountOfIndexesCheckedSoFar = tb.Indexes.Count Then
                            'populate the temp table that holds the names of tables without Primary keys
                            strSQL = "INSERT INTO tblListOfTablesWithoutPrimaryKey(TableName)"
                            strSQL = strSQL & " VALUES('" & tb.Name & "')"
 
                            DoCmd.RunSQL (strSQL)
                            CountMe = CountMe + 1
 
                        End If
 
                    End If
 
                End If
 
            Next idx
 
        End If
 
LeaveThisTable:
 
    Next tb
 
    Set db = Nothing
 
    MsgBox "The program has finished. " & CountMe & " tables were found that did not have Primary keys."
 
    GoTo Endo
 
ErrorHandler:
    MsgBox "ERROR: " & Err.Number & ". " & Err.Description
Endo:
 
End Sub