Results 1 to 6 of 6
  1. #1
    Jerimiah33 is offline Novice
    Windows XP Access 2000
    Join Date
    Nov 2005
    Posts
    5

    Best Way to Return a Newly Created Index?

    Hey everyone!

    I'm wondering about the best way to return a primary key for a record that i just programmatically created.

    what i mean is...

    i am writing a 'copy' function for a database interface. my function first appends a new record that duplicates the current records' data. Next, i need to go and programmatically change the foreign keys of the sub records that are attached to the original.

    for instance: i'm copying A, but attached 1<->many to A are records B. First i copy A into a new record C, now i need to associate records B with C instead of A.



    Basically, this would be very easy to do with an update query if i could find a good way to return the primary key for the record that i just appended to the table.

    any ideas?

    thanks,
    mike

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    How is the primary key field defined, AutoNumber or something else?

  3. #3
    Join Date
    Sep 2006
    Location
    Manchester, England
    Posts
    13
    The only decent way to do this within access for autonumbers is to use a recordset like so :

    Code:
    rs.addnew
    rs("name") = "dan"
    rs.update
    lngID = rs("Id")

  4. #4
    rayc is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Dec 2005
    Location
    Montreal, Canada
    Posts
    19
    What I've done in the past is, immediately after INSERTing a record, I run a query similar to:
    SELECT Max(MyPrimaryField) FROM MyTable

    But this only works if my primary key is an AutoNumber field, since Access increments these fields and does not repeat numbers.

    Hope this helps.

  5. #5
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Quote Originally Posted by rayc
    But this only works if my primary key is an AutoNumber field, since Access increments these fields and does not repeat numbers.
    And, of course, that the table definition did not have the AutoNumbered field set to random :wink:

  6. #6
    Join Date
    Sep 2006
    Location
    Manchester, England
    Posts
    13
    That's generally a bad thing to do because you could run into concurrency problems and you're not gauranteeing you are getting the correct key.

    If you must do it this way which I have had to do in the past due to reasons beyond my control you should lookup on another value as well.
    Typically I would insert a short random say 8 characters at the same time then use this to get the correct ID.....

    Code:
    SELECT Max(PrimaryKeyField )
    FROM myTable
    WHERE RandomKey = @InsertedRandomKey
    hope that makes sence!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. stored procedure return value to access form
    By rbw940 in forum Programming
    Replies: 0
    Last Post: 10-13-2008, 01:31 PM
  2. Return Record # In Query
    By redwinger354 in forum Access
    Replies: 1
    Last Post: 09-15-2007, 01:08 PM
  3. Email created from Access is in the wrong format
    By Stick in forum Programming
    Replies: 0
    Last Post: 09-25-2006, 12:48 PM
  4. Security icon created on desktop !!!
    By wasim_sono in forum Security
    Replies: 1
    Last Post: 02-07-2006, 09:42 AM
  5. Importing a table created in word to Access
    By anthony_f_justin in forum Access
    Replies: 4
    Last Post: 01-04-2006, 09:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums