Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Make table query that adds a Primary Key

    Does anyone know how to do this? It seems so simple but I can't seem to get it to work.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    1)
    Create table a
    (
    col1 Integer,
    col2 Integer,
    primary key(col, col2)
    )

    2)
    Create table a
    (
    col1 Integer primary key,
    col2 Integer
    )
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    Could someone clarify what this means and where I would put it? It seems very simple and I would love to be able to use it.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Queries => Design View => SQL View (Right Click)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    Ok, this is just a small sample of my make table query.

    SELECT Leases.LEASE_NO, Leases.BRANCH_NO, Leases.REMIT_TO, [Leases/Customer].CO_ADDR INTO New
    FROM Leases INNER JOIN [Leases/Customer] ON Leases.LEASE_NO = [Leases/Customer].LEASE_NO;


    Can you show me where to put

    1)
    Create table a
    (
    col1 Integer,
    col2 Integer,
    primary key(col, col2)
    )

    I need the Leases.LEASE_NO to be the primary key. Thanks

  6. #6
    Join Date
    Mar 2004
    Posts
    361
    I also have this code but I'm not sure where to add it.

    Dim db As DAO.Database
    Set db = CurrentDb
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "yourMakeTableQueryName"
    DoCmd.SetWarnings True
    db.Execute "CREATE INDEX row-id ON yourNewTableName (row-id) WITH PRIMARY;"
    db.Close

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Alter table Leases
    add Primary Key (LEASE_NO)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Mar 2004
    Posts
    361
    Ok but where to I put that. Sorry if I am missing the obvious but I'm just not sure how to finish up this task. Thanks.

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You have 2 options.

    1) Design View
    Queries => Design View => SQL View (Right Click)

    2) VBA
    DoCmd.RunSQL("Alter table Leases add Primary Key (LEASE_NO)")
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Mar 2004
    Posts
    361
    Here is what I have. I chose to put a button on a form to update the table. Here is the code in that button.

    Option Compare Database

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Dim stDocName As String

    stDocName = "Build New Table"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

    Now the table that is being built is called tbl main. I get the column LEASE_NO from the leases table. So where do I add this code into the above code? Thanks.

    DoCmd.RunSQL("Alter table tbl main add Primary Key (LEASE_NO)")

  11. #11
    Join Date
    Mar 2004
    Posts
    361
    It seems like it should go after the table is built but I'm not sure.

Posting Permissions

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