Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Add Primary Key To Existing Field - DAO

    Hi all,

    Using Excel/Access 2007

    I am trying to add a Primary Key to an existing Field in an Existing Table. There are currently no PK's in the Table.

    I tried:
    Code:
    Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim prp As DAO.Property
        Dim ind As DAO.Index
        
        Set db = OpenDatabase(strDBPath & strDB)
        Set tdf = db.TableDefs(strDBTable)
        Set ind = tdf.CreateIndex("PrimaryKey")
        
        With ind
            .Fields.Append ("Account ID")
            .Primary = True
        End With
        tdf.Indexes.Append ind
    But is coming back with Type mismatch on the Account ID Field
    How can I fix this?

    thx
    w

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the name of the column if it has a space in it it should be enclosed by [] eg:-
    .Fields.Append ("[Account ID]")

    mind you I would expect this sort of change to be done user 'alter table'
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks healdem,

    Agreed, using ADO with SQL ALTER appears much easier.
    I am receiving a duplicate message when I try toi append a csv file to the table as I would expect.

    How can I quash the error message and simply append the non-duplicate records?

    I exported my table to csv
    I then deleted 12 records from the table
    When I attempt to append the csv back to the table, I would like the 12 records to be appended and the reamining records to be discarded.

    Here's my revised code
    Code:
    'Add Primary Key
        strSQL = vbNullString
        strSQL = "ALTER TABLE " & strDBTable & " ADD PRIMARY KEY ([Account ID])"
        Conn.Execute (strSQL)
            
        'Load to Access
        strSQL = vbNullString
        strSQL = "INSERT INTO " & strDBTable & " SELECT * FROM [tmpf$] IN '" _
        & wbTmp.FullName & "' 'Excel 12.0;'"
        
        Conn.Execute (strSQL)
        
        'Drop Primary Key
        strSQL = vbNullString
        strSQL = "ALTER TABLE " & strDBTable & " DROP CONSTRAINT ([Account ID])"
        Conn.Execute (strSQL)
    thx
    w

Posting Permissions

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