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.


    "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.


    "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.


    "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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. kd

    Help with code to generate primary key

    kd, Jul 11, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    1,083
    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:
    11,577
    JTRockville
    Aug 24, 2003
  3. Andrea

    Identifying a table's primary key fields with code

    Andrea, Nov 1, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    8
    Views:
    505
    Dirk Goldgar
    Nov 4, 2003
  4. TC

    Determine primary key fields in VBA

    TC, Nov 25, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    439
    Tik Sin
    Nov 25, 2003
  5. Ann

    primary key violation and custom message

    Ann, Dec 3, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    626
    Tim Ferguson
    Dec 3, 2003
  6. tonyck

    How to refrence primary key and forgine key

    tonyck, Jul 7, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    530
    tonyck
    Jul 7, 2005
  7. tonyck
    Replies:
    0
    Views:
    667
    tonyck
    Jul 18, 2005
  8. Allen Browne
    Replies:
    0
    Views:
    663
    Allen Browne
    Jul 18, 2005
Loading...