Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33

    Unanswered: Primary Key field

    Assuming I always create an autonumber key field and click the primary column when I create tables in the MS Access environment, then would this code reliably return my primary key field?

    Code:
    Function cPrimarykeyName(cTablename As String) As String
    Dim odb As DAO.Database
    Dim o As DAO.TableDef
    Dim oi As Index
    
    
    Set odb = CurrentDb
    
    Set o = odb.TableDefs(cTablename)
    For Each oi In o.Indexes
        If oi.Name = "PrimaryKey" Then
            cPrimarykeyName = oi.Fields(0).Name
            Exit Function
        End If
    Next
    ' Some error handling code here.
    End Function
    Is there a more straightforward (and faster) way to achieve this?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's not necessarily enough. Though it would be stupid to do so, you can create any type of index and name it "PrimaryKey". If you want to be sure it's really the primary key, you have to check for the Primary property of the index:
    Code:
    Function testpk()
    
        Dim dbs As dao.Database
        Dim tdf As dao.TableDef
        Dim idx As dao.Index
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("Tbl_Reclamations_ServerTables")
        For Each idx In tdf.Indexes
            Debug.Print idx.Name, idx.Primary
        Next
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    Thanks... I see your point and your solution is more straightforward.
    Last edited by Marc Grajower; 12-25-09 at 07:00. Reason: typo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •