Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    5

    Unanswered: Desperately Seeking 2 Primary Keys

    Hello. I'm just learning VB script and have been having some problems. I'm trying to write some code that will create a new table in my Access 2000 database called "MultipleVisits" and will designate each field in the table, subjectid and visdte, as a primary key. My code is just not working - any ideas?

    Private Sub Form_Close()

    Dim dbase As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim idx As Index

    'SPECIFING CURRENT DATABASE

    Set dbase = CurrentDb()

    'DELETING ORIGINAL TABLE IF NEEDED

    dbase.TableDefs.Delete ("MultipleVisits")

    'CREATING NEW TABLE

    Set tbl = dbase.CreateTableDef("MultipleVisits")

    'CREATING FIELD SUBJECTID

    Set fld = tbl.CreateField("subjectid", dbDouble)

    fld.OrdinalPosition = 1
    fld.Required = True

    tbl.Fields.Append fld

    'CREATING FIELD VISDTE

    Set fld = tbl.CreateField("visdte", dbDate)

    fld.OrdinalPosition = 2

    tbl.Fields.Append fld
    fld.Required = True

    'SETTING SUBJECTID AND VISDTE AS PRIMARY KEYS

    Set idx = tbl.CreateIndex("PrimaryKey")

    idx.Primary = True
    idx.Required = True
    idx.Unique = False

    Set fld = idx.CreateField("subjectid")
    idx.Fields.Append fld
    tbl.Indexes.Append idx

    Set idx = tbl.CreateIndex("PK1")

    idx.Primary = True
    idx.Required = True
    idx.Unique = False


    Set fld = idx.CreateField("visdte")
    idx.Fields.Append fld
    tbl.Indexes.Append idx

    'ADDING TABLE MULTIPLE VISITS TO DATABASE

    dbase.TableDefs.Append tbl

    'REFRESHING DATABASE

    RefreshDatabaseWindow

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are allowed zero or one primary keys.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Posts
    5
    Thanks. I'll start working on another solution.

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Technically, what izy said is true that you can have either 0 or 1 primary keys, however what you described sounded like you would like to have a primary key of more than 1 field, which you can do. This is done with an index. Go into Table Design and select all of the rows that you want as part of your Primary Key (use your [Ctrl] key for multiple selections) and then click the Key icon in your toolbar. This will create an index that will now be your Primary Key. I see that you are trying to create this with code and I am not sure of the proper syntax. However, I was curious why you were initially deleting the Table and then creating another one. If you already have the table setup and you were to set the index as I previously directed, you could do a delete query to clear all of your records and then you will have a properly structured empty table for entry into or appending to.

    TD

  5. #5
    Join Date
    Nov 2004
    Posts
    5
    Ahhhhh... because I was very much stuck along one line of thinking when I was programming this last evening. I just tried what you suggested and it works brilliantly. Much better than deleting the whole table and starting from scratch.

    Thanks - C

Posting Permissions

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