Results 1 to 8 of 8
  1. #1
    notebinder is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    3

    How to Copy a Row from Table to Table

    I have 2 simple tables with 3 columns and an autonumber primary key/ID column. One table is a TEMPLATE table from which I copy data into another NOTE table to create a new record. During this copy, I rename the template name field to create the new entry name. In MySql, this was very simple SQL:

    INSERT INTO notes (name, note, data_size)


    SELECT 'newname' AS name, note, data_size FROM templates WHERE id = 99

    The new row in the NOTES table is now named newname. This SQL does not work in Access and I can't see a way around it. Anyone have any ideas?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If that is the actual code you are using you may be having trouble because "name" is a reserve word in MS Access.
    http://www.allenbrowne.com/AppIssueBadWord.html#N

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    notebinder is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    3
    Quote Originally Posted by RuralGuy View Post
    If that is the actual code you are using you may be having trouble because "name" is a reserve word in MS Access.
    http://www.allenbrowne.com/AppIssueBadWord.html#N
    It fails even if I use [name]

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It fails even if I use [name]
    what error message do you get?

  6. #6
    notebinder is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    3
    So I played with this based on the feedback on reserved words. It now works if I use[] for all fields (not sure of the logic there, but .....):

    INSERT INTO notes ([name], [note], [data_size]) SELECT 'newname' AS [name], [note], [data_size] FROM templates WHERE id = 99

    Thanks for all the responses.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Glad to hear you got it working. Thanks for the feedback.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Note is also a reserved word per Allen's list.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 10
    Last Post: 08-31-2018, 08:04 AM
  2. Replies: 1
    Last Post: 08-29-2018, 10:26 AM
  3. Replies: 18
    Last Post: 08-09-2018, 06:45 AM
  4. Replies: 8
    Last Post: 06-07-2017, 01:27 PM
  5. Replies: 1
    Last Post: 09-03-2014, 10:48 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