primary key

Discussion in 'Microsoft Access VBA Modules' started by Bill Linares, Sep 6, 2003.

  1. Bill Linares

    Bill Linares Guest

    How do you define a field as primary key in VBA?
    Is sounds trivial, but I have not been able to find answer in on-line help
     
    Bill Linares, Sep 6, 2003
    #1
    1. Advertisements

  2. Bill Linares

    Allen Browne Guest

    Using DAO, create an Index, and set its Primary property to Yes:

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim ind As DAO.Index

    'Initialize
    Set db = CurrentDb()
    Set tdf = db.TableDefs("MyTable")

    'Create a new index. The name us usual, but not crucial.
    Set ind = tdf.CreateIndex("PrimaryKey")
    'Specify the field(s) in the index
    With ind
    .Fields.Append .CreateField("MyField")
    .Primary = True 'This makes it the primary key
    End With
    'Add this new index to the TableDef's Indexes collection.
    tdf.Indexes.Append ind

    'Clean up
    Set ind = Nothing
    Set tdf = Nothing
    Set db = Nothing

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html

    "Bill Linares" <> wrote in message
    news:...
    > How do you define a field as primary key in VBA?
    > Is sounds trivial, but I have not been able to find answer in on-line help
    >
    >
     
    Allen Browne, Sep 6, 2003
    #2
    1. Advertisements

  3. Bill Linares

    Bill Linares Guest

    Why is it that I cannot append the table to the data-base?: Following line

    db.TableDefs.Append tdf

    produces error 3264, no field defined

    Thanks for your troubles.
     
    Bill Linares, Sep 6, 2003
    #3
  4. Bill Linares

    Allen Browne Guest

    First you CreateTableDef().
    Then CreateField(), and append it to the TableDef.
    Finally, Append the TableDef to the database's TableDefs.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html

    "Bill Linares" <> wrote in message
    news:3f59cab9$...
    > Why is it that I cannot append the table to the data-base?: Following line
    >
    > db.TableDefs.Append tdf
    >
    > produces error 3264, no field defined
    >
    > Thanks for your troubles.
     
    Allen Browne, Sep 6, 2003
    #4
  5. Bill Linares

    TC Guest

    You have to create the fields in the *table* before you add them to the
    index.

    TC


    "Bill Linares" <> wrote in message
    news:3f5af000$...
    > Isn't that what I'm doing?:
    >
    > Public Sub Test_AutoKey()
    > Dim db As DAO.Database
    > Dim tdf As DAO.TableDef
    > Dim ind As DAO.Index
    >
    > 'Initialize
    > Set db = CurrentDb()
    > Set tdf = db.CreateTableDef("MyTable")
    >
    > 'Create a new index. The name us usual, but not crucial.
    > Set ind = tdf.CreateIndex("PrimaryKey")
    > 'Specify the field(s) in the index
    > With ind
    > .Fields.Append .CreateField("MyField")
    > .Primary = True 'This makes it the primary key
    > End With
    > 'Add this new index to the TableDef's Indexes collection.
    > tdf.Indexes.Append ind
    > db.TableDefs.Append tdf
    >
    > 'Clean up
    > Set ind = Nothing
    > Set tdf = Nothing
    > Set db = Nothing
    > End Sub
    >
    >
     
    TC, Sep 7, 2003
    #5
  6. Bill Linares

    Bill Linares Guest

    Isn't that what I'm doing?:

    Public Sub Test_AutoKey()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim ind As DAO.Index

    'Initialize
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("MyTable")

    'Create a new index. The name us usual, but not crucial.
    Set ind = tdf.CreateIndex("PrimaryKey")
    'Specify the field(s) in the index
    With ind
    .Fields.Append .CreateField("MyField")
    .Primary = True 'This makes it the primary key
    End With
    'Add this new index to the TableDef's Indexes collection.
    tdf.Indexes.Append ind
    db.TableDefs.Append tdf

    'Clean up
    Set ind = Nothing
    Set tdf = Nothing
    Set db = Nothing
    End Sub
     
    Bill Linares, Sep 7, 2003
    #6
  7. Bill Linares

    Allen Browne Guest

    You have created a table, but the table has no fields.
    Try this sequence:
    1. CreateTableDef
    2. CreateField
    3. Append field to TableDef
    4. Repeat steps 2 and 3 for other fields.
    5. Append TableDef to TableDefs.

    6. Create the index
    7. CreateField in index (doesn't create a field, just a reference to
    it).
    8. Append field to Index's Fields
    9. Append Index to TableDef's Indexes.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html

    "Bill Linares" <> wrote in message
    news:3f5af000$...
    > Isn't that what I'm doing?:
    >
    > Public Sub Test_AutoKey()
    > Dim db As DAO.Database
    > Dim tdf As DAO.TableDef
    > Dim ind As DAO.Index
    >
    > 'Initialize
    > Set db = CurrentDb()
    > Set tdf = db.CreateTableDef("MyTable")
    >
    > 'Create a new index. The name us usual, but not crucial.
    > Set ind = tdf.CreateIndex("PrimaryKey")
    > 'Specify the field(s) in the index
    > With ind
    > .Fields.Append .CreateField("MyField")
    > .Primary = True 'This makes it the primary key
    > End With
    > 'Add this new index to the TableDef's Indexes collection.
    > tdf.Indexes.Append ind
    > db.TableDefs.Append tdf
    >
    > 'Clean up
    > Set ind = Nothing
    > Set tdf = Nothing
    > Set db = Nothing
    > End Sub
    >
    >
     
    Allen Browne, Sep 7, 2003
    #7
  8. Bill Linares

    Bill Linares Guest

    Thanks mate
     
    Bill Linares, Sep 7, 2003
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. kd

    Help with code to generate primary key

    kd, Jul 11, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    930
    Tim Ferguson
    Jul 11, 2003
  2. Harry

    create primary key via VBA

    Harry, Aug 24, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    5,457
    JTRockville
    Aug 24, 2003
  3. tonyck

    How to refrence primary key and forgine key

    tonyck, Jul 7, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    289
    tonyck
    Jul 7, 2005
  4. tonyck
    Replies:
    0
    Views:
    426
    tonyck
    Jul 18, 2005
  5. Allen Browne
    Replies:
    0
    Views:
    418
    Allen Browne
    Jul 18, 2005
Loading...

Share This Page