Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2004
    Posts
    41

    Unanswered: Primary Key in Make Table query

    How do I set the Primary Key when using a Make Table query?

  2. #2
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    You can do it using VBA. Is that an option for you?
    ...rtk

  3. #3
    Join Date
    Mar 2004
    Posts
    41
    Absolutely! Any hints before you just hand out the solution? It's a slow day at the office...

  4. #4
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    OK...
    Look under the CREATE INDEX Statement in the Access Help.
    ...rtk

  5. #5
    Join Date
    Mar 2004
    Posts
    41


    [/vb]
    Sub MkTblPK()

    Dim db281_503 As Database
    Set db281_503 = OpenDatabase("M:\503_281.mdb")
    db281_503.Execute "CREATE UNIQUE INDEX PackNo ON tbl281tmp (PackNo) WITH PRIMARY"
    db281_503.Close

    End Sub
    [\vb]

    The goal is to avoid adding a field, so I've used a pre-existing field called PackNo hoping that I'm not going to overwrite it with the dreaded sequential integer set.
    Next.. How do I tell Access to run the Sub? Maybe the "On Open" property?
    <<Brain/typing disconnect - this is a query, ignore the On Open comment>>
    Last edited by kcary; 03-03-04 at 12:01.

  6. #6
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    How are you executing the Make table query? If it's on a button click event it should follow after the make table query.
    ...rtk

  7. #7
    Join Date
    Mar 2004
    Posts
    41
    Switchboard button...to a Macro.

  8. #8
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    OK. You're 99% there... In your macro after running the make table query insert a RunCode action and point it to your new Function (I believe it has to be defined as a function) MkTblPK. That should do it...
    rtk

  9. #9
    Join Date
    Mar 2004
    Posts
    41


    I can't add the module in Expression Builder. There's no thing in the Value column. I went back to VB Editor, and it won't compile. I must have missed something important in the Help file, maybe? Where it defines "Database", maybe?

  10. #10
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    Not to worry... I only have access to A97(DAO) here at the office but something similar should work for you. Click on the modules tab and click the new button. Paste in this:

    Function MkTblPK()
    Dim db281_503 As DAO.Database

    Set db281_503 = CurrentDb()

    db281_503.Execute "CREATE UNIQUE INDEX PackNo ON tbl281tmp (PackNo) WITH PRIMARY"

    Set db281_503 = Nothing

    End Function

    If you are using a new version of Access you can go to to Tools/References and pick Microsoft DAO (some version) to make this code work.

    Save the module and Compile... it should this time. Then your macro should work.

    ...rtk

  11. #11
    Join Date
    Mar 2004
    Posts
    41
    Got it. Forgot my Reference to DAO objects. Back to "How to I run the code?"

  12. #12
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    OK. The RunCode action should do the trick.
    ...rtk

  13. #13
    Join Date
    Mar 2004
    Posts
    41
    We must have posted the DAO solution at the same time....
    I went back and used your code instead. Still needed the DAO reference, but it worked.
    Unfortunately, my Make Table Query is creating duplicate values to start with. <ARGH!>

  14. #14
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    Well you could always create the table first (with the primary key), delete the records, append the records and ignore the errors....
    or use a DISTINCT in your query if it's producing duplicate records... with duplicate keys you might have to do a little extra work to clean things up.
    good luck
    ...rtk

  15. #15
    Join Date
    Mar 2004
    Posts
    41
    A shortcut for the copy/delete approach... I usually use copy/paste to create a template by choosing "Structure Only". You can add/remove keys, change field types, etc. depnding on your needs. Then I copy the template table to a new name and append the records without having to delete the records repeatedly during design.

    Thanks for the help.

Posting Permissions

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