Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Insert records (Access 2000)

    1. I need to re-insert some accidentally deleted records into a table but the Add Record option only appends a higher record number (the key field is an autonumber). How can I do this?

    2. Is there a simple way to copy and paste another record from the same table?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert records (Access 2000)

    Use and append query with the value you want in the record number field.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert records (Access 2000)

    2. You select an entire record by clicking on the record selector on the left hand side of the table. Copy it to the clipboard, then click the record selector of the blank new record at the bottom of the table, and paste.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Insert records (Access 2000)

    Thanks for your reply. I'm not familiar with an append query. Will this enable me to insert a blank row where the deleted record belongs so I can type the data back into the table?

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Insert records (Access 2000)

    Thanks for your reply, that works fine, except that the record number is automatically incremented, but I need to specify a lower record number to re-insert a deleted record.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert records (Access 2000)

    Easiest way is to
    1. Click the table you want to put the data into.
    2. Copy it
    3. Paste it and select Structure only.
    4. give it a name
    5. click ok.
    6. Modify the attributes so the 'Record Number' is now 'Number' instead of ato number. (Do not change any of the field names.)
    7. open the table and fill in the record data. Put the record number you want in the 'Record Number ' .
    8. create a query selecting all the data attributes in your new temp table.
    9. change it to and append by clicking on the query type down arrow and selecting the Append query option.
    10 select the name of you table you want the data to go into.
    Run the query.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert records (Access 2000)

    That's what Don Lansing's reply told you: use an append query. Say that you want to copy a record with ID 5 to a new record with ID 3. The screenshot below shows how to do this. Note that ID occurs twice: the first time as a dummy field whose value is specified explicitly; this is used to fill the ID field in the new record, and the second time only to specify criteria; the Append to line for this second occurrence is blank.

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Insert records (Access 2000)

    Thanks for your reply. I tried this but got stuck because the table is linked from an SQL database and I could only copy the link, not the table itself; and I can't modify the properties of the linked table. Is there another way of just pasting a record or inserting a blank one into a deleted key's space? (I only need to insert two or three records, and can re-enter the data manually if need be.)

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert records (Access 2000)

    Open the linked table in design mode.
    Copy all the attributes
    Create a new table and in design mode paste the attributes.
    Save the table
    Fill in your data
    and run the append query.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

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