Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    10

    Unanswered: Setting PK in Access using VB code?

    Does anyone know how I can set the PK on a table in Access using VB code?

    Cheers!

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You can use ADO to create and edit table definitions. You will need to read the Help file for the full syntax as it is a big area, but try searching for recordset and fields as a starter

    Justin

  3. #3
    Join Date
    Jun 2004
    Posts
    10
    I have been trying that using DAO. I've come up with the following but it comes up with an error telling me the field already exists. I know it already exists I just want to turn it into the primary key! Any ideas?

    Thanks

    Dim tdfTempTable As TableDef
    Dim inPK As Index
    Dim dbChecker As Database
    Dim fid1 As Field

    Set dbChecker = CurrentDb

    Set tdfTempTable = dbChecker.TableDefs("tempTable")
    Set inPK = tdfTempTable.CreateIndex("PDVID")
    inPK.Primary = True
    Set fid1 = tdfTempTable.Fields("PDVID")
    inPK.Fields.Append fid1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CREATE INDEX idxCustomerID
    ON tblCustomers (CustomerID)
    WITH PRIMARY

    http://msdn.microsoft.com/library/de.../acfundsql.asp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You can also use the data definiton language to create a query that alters the table. The syntax would be something like (use Help for the full syntax):

    ALTER TABLE MyTableName
    CONSTRAINT MyPrimaryKeyName PRIMARY KEY (MyColumnName)

    You can run this as an action query from VBA (DoCmd.RunSQL command). Make sure the primary key has no null values and is unique
    Justin

Posting Permissions

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