Results 1 to 13 of 13

Copy Table and rename to "newTable"

  1. #1
    MadTom is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    28

    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 online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,524
    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"
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  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
    2,772
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,274
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  5. #5
    MadTom is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    28
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,274
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  7. #7
    MadTom is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    28
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,274
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  9. #9
    MadTom is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    28
    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 online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,524
    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.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  11. #11
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,274
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  12. #12
    JoeM is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,524
    But if you look at this link on the MSDN site, no space shown! https://msdn.microsoft.com/en-us/vba...-method-access
    Interesting...
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  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
    2,772
    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, 07:00 AM
  2. Replies: 1
    Last Post: 07-23-2015, 09:18 PM
  3. Replies: 3
    Last Post: 02-16-2015, 12:04 PM
  4. Replies: 0
    Last Post: 01-11-2012, 11:34 AM
  5. Using "Include" or "Copy" in VBA
    By EddieN1 in forum Programming
    Replies: 3
    Last Post: 01-03-2012, 05: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
  •  
Tech Forums: Microsoft Office Forums