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

    Unanswered: I Need 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

    End Sub

  2. #2
    Join Date
    Nov 2004
    Posts
    108
    try creating all the fields first and then create the PK index and add the two fields to it
    or use SQL code
    to err is human ; to really mess things up requires a computer

  3. #3
    Join Date
    Nov 2004
    Posts
    108

    sql code

    Code:
    "CREATE TABLE tableName (field1Name dataType NOT NULL, field2Name dataType NOT NULL, " _
         & "field3Name dataType NOT NULL) ON PRIMARY; GO; ALTER TABLE tableName WITH NOCHECK ADD " _
         & "CONSTRAINT PK_tableName PRIMARY KEY  CLUSTERED (field2Name, field3Name) ON PRIMARY; GO"
    to err is human ; to really mess things up requires a computer

Posting Permissions

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