Results 1 to 13 of 13
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Copy Table and rename to "newTable"

    I would like to Copy a Table, Rename Table and Still have the original Table. Would like to add this to a VBA, One other thing I would like to add is the NewTable name could be variable string + "NewTable". I've goggled and looked at many examples but can't seem to get it to work.



    DoCmd.Rename "Old Table", acTable, "New Table"

    DoCmd.CopyObject, "Table Copy", acTable, "Table"

    New Table = XX + "New_Table_Name"
    Thanks!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    A few minor changes.

    In Rename, the new table name comes first, not the old table name, i.e.
    Code:
    DoCmd.Rename "New Table", acTable, "Old Table"
    In CopyObject, there should be a space after the word "CopyObject" and before the comma. The comma is serving as a place holder for the first, optional argument (database):
    Code:
    DoCmd.CopyObject , "Table Copy", acTable, "Table"

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by MadTom View Post
    I would like to Copy a Table, Rename Table and Still have the original Table. Would like to add this to a VBA
    This, especially the part in red, suggests you plan to do this repeatedly...and that all of these Tables would be identical, i.e. would be composed of the same Fields...which begs the question of 'why?'

    Identical Tables is almost always a sign of an incorrect, non-normalized, data design.

    So, once again...why?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    JoeM
    Sorry to disagree but you do NOT need a space between copyobject and the comma.
    The first argument, destination database, is optional, as you said.
    Like all optional arguments it can just be omitted.
    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

  5. #5
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Good Question, but in this case I have a routine that adds info. to a table from a empty table in my database, at the end of the routine or sub routine I would like to save the table with the new info. to a new name and keep the old table with no info. I also could add a button to run this command. I hope this makes sense! Thanks
    Quote Originally Posted by Missinglinq View Post
    This, especially the part in red, suggests you plan to do this repeatedly...and that all of these Tables would be identical, i.e. would be composed of the same Fields...which begs the question of 'why?'

    Identical Tables is almost always a sign of an incorrect, non-normalized, data design.

    So, once again...why?

    Linq ;0)>

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by MadTom View Post
    Good Question, but in this case I have a routine that adds info. to a table from a empty table in my database, at the end of the routine or sub routine I would like to save the table with the new info. to a new name and keep the old table with no info. I also could add a button to run this command. I hope this makes sense! Thanks
    No it makes no sense at all. If you want a copy, create a backup.
    Otherwise store data once.
    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

  7. #7
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    So I have an empty table, my code adds info. to table, so I want to rename this table and if I run my code again I need an table with the original name. You could call it a backup with new name and then empty old table. Thanks!

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I already understood WHAT you are doing but you've not explained WHY.
    It's your database and you are of course free to do whatever you want but be aware it's considered poor practice.
    I've known people do this type of thing in order to try and prevent gaps in autonumbers. Also pointless.

    One more thing:
    Repeatedly making and deleting tables will cause database bloat leading to a drop in performance over time.
    It may also lead to instability and at some point cause Access to crash when you open your database.

    Ok rant over. Good luck with your project.
    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

  9. #9
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    ridder52, Sometimes there is a reason for doing something that may seem wrong. I have a form that will fill in Locations of storage drawers and plastic compartments, when I run the Build Table command button it installs rows, cols and comps info into the table. Now when I add more locations I run the code again and add more locations.
    OK I can rename table after I run the code and all is good.
    I'm learning as I go and wanted to add a button to save and rename table in code, no big deal, I can do what I need by hand and only need it when testing my code. I hope this answers why. Only doing this for my own personal use. Thanks for your comments.

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    JoeM
    Sorry to disagree but you do NOT need a space between copyobject and the comma.
    I am having trouble testing that part, because whenever I try to enter it that way, Access automatically inserts a space between the "DoCmd.CopyObject" and the comma.
    So I actually cannot get it to look that way.
    Maybe that is just a typo when the typed it into the post, because it doesn't even appear possible to do that.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's very odd.
    Now you mention it Access does automatically add a space but I've never noticed it before
    Just tried both of the following:
    Code:
     DoCmd.CopyObject , strDest, acTable, strSource
    
    DoCmd.CopyObject , , strDest, acTable, strSource
    Space automatically added after Object even if you include the optional external database clause (empty or otherwise)

    But if you look at this link on the MSDN site, no space shown! https://msdn.microsoft.com/en-us/vba...-method-access

    So in a way, we're both right!
    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

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    But if you look at this link on the MSDN site, no space shown! https://msdn.microsoft.com/en-us/vba...-method-access
    Interesting...

  13. #13
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ridders52 View Post

    But if you look at this link on the MSDN site, no space shown!
    You're talking about Microsoft Help!

    Surely you don't expect that information to always be correct!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 1
    Last Post: 07-23-2015, 10:18 PM
  3. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  4. Replies: 0
    Last Post: 01-11-2012, 12:34 PM
  5. Using "Include" or "Copy" in VBA
    By EddieN1 in forum Programming
    Replies: 3
    Last Post: 01-03-2012, 06:54 PM

Tags for this Thread

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