Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Finding primary key in VBA (2000 sp3)

    I have two databases with the same structure, some of the same records but many orphan records on both sides. I am trying to programmatically import all orphan records from one database into another. I have managed to write the code to import all the tables, appending the suffix "2" to the table names to identify the imports. My next step is to create a query for each table pair to identify the records that are in the imported tables but not the originals. I planned to use the primary key of each table to identify the orphans. I have encountered 2 problems, however. The first is that this code:

    returns the text "PrimaryKey", rather than the name of the primary key field. The second is that many tables have composite primary keys. I assume I will have to add a field to each table concatenating all the primary key fields, and use this as the field to join, but if the primary key is composite, and someone can suggest a way to extract the primary key from the table definition, will it be in a delimited format from which I will be able to extract the names of the fields making up the primary key? I've had a look to see if there is a property or attribute of the field object which specifies whether that is part of the primary key, and couldn't find one.

    I'm very much stuck with DAO, as I've never used ADO.

    Step 3 will involve how to import records in a database riddled with relationships (one of the reasons I've always been slack on including relationships in my own db's), but I will inspect the relationships again to see if there is a logical way to do it before begging help on that front.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding primary key in VBA (2000 sp3)

    The code seems to have gone missing.

    A field as item in the Fields collection of a table doesn't store info about whether it is part of an index.

    The TableDef object in DAO has an Indexes collection, and each Index has a property Primary that can be True or False.
    The Index object has a Fields collection that contains the fields that make up the index.
    So the idea is to loop through the Indexes collection (using For Each), and stop when you have found the index with Primary = True.
    Then loop through the Fields collection of the index.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Finding primary key in VBA (2000 sp3)

    Duh!

    Sorry Hans, that was exactly what I've done:

    Dim db As DAO.Database, strTabName As String, tbl As TableDef, strKeyName As String, idx As DAO.Index
    Set db = CurrentDb
    For Each tbl In db.TableDefs
    strTabName = tbl.Name
    If Right(strTabName, 1) <> 2 And Left(tbl.Name, 4) <> "MSys" Then
    For Each idx In tbl.Indexes
    If idx.Primary = True Then
    strKeyName = idx.Name
    Exit For
    End If
    Next idx
    End If
    Next

    strKeyName ends up containing the string "PrimaryKey"

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Finding primary key in VBA (2000 sp3)

    Sorry, just got to the bit about looping through the fields.

    Will do that, but still baffled by the index name issue

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding primary key in VBA (2000 sp3)

    idx.Name returns the name of the index as it is displayed in the Indexes window. If the primary key was created using the "Primary Key" button on the toolbar, it will have the default name "PrimaryKey". This is *not* the name of the primary key field. You will have to look at the Fields property of the index:

    Dim db As DAO.Database
    Dim strTabName As String
    Dim tbl As DAO.TableDef
    Dim strKeyName As String
    Dim idx As DAO.Index
    Dim fld As DAO.Field

    Set db = CurrentDb
    For Each tbl In db.TableDefs
    strTabName = tbl.Name
    If Right(strTabName, 1) <> 2 And Left(tbl.Name, 4) <> "MSys" Then
    For Each idx In tbl.Indexes
    If idx.Primary = True Then
    strKeyName = idx.Name
    For Each fld In idx.Fields
    Debug.Print fld.Name
    Next fld
    Exit For
    End If
    Next idx
    End If
    Next tbl

Posting Permissions

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