Results 1 to 3 of 3
  1. #1
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150

    MS Access - Copy specific fields + format to new table - structure only VBA

    Hi,



    I was wondering if there is a simplistic way of copying the structure of one table into a NEW table - but only specific fields.

    docmd.transfer database doesn't seem to allow passing SQL string as an object, unless i have a mistake:
    Code:
    DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name, acTable, "SELECT ContactID, Company, Name, Phone, Email FROM Contacts;", "Temp_VendorContacts", True
    The only other option is using a MAKE TABLE query such as...

    Code:
    sql = "SELECT ContactID, Company, Name, Phone, Email INTO Temp_VendorContacts FROM Contacts;"
    However, this will copy the data as well, which is not desired. I can get around this by putting in a WHERE clause for an extremely unprobable value to have a return of 0 records:

    Code:
    sql = "SELECT ContactID, Company, Name, Phone, Email INTO Temp_VendorContacts FROM Contacts WHERE ContactID = '" & "$#$#$#" & "';"
    The issue with this approach is that it is running a WHERE query, thus still using computational resources for something that really doesn't need it.

    So, my question is, is there a simpler/better way? I could write a function to do this task but I would rather not.....


    Thanks

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I think a workaround is unavoidable.
    I would use
    Code:
    SELECT Field1, field2, field3....
    INTO NewTable
    FROM OldTable
    WHERE 1=0;
    To copy the entire table (with data), you can also use
    Code:
    DoCmd.CopyObject ,"NewTable", acTable, "OldTable"
    followed by
    Code:
    DELETE * FROM NewTable
    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

  3. #3
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    150
    isladogs,

    I figured a quick solution was not available. These methods will work.

    Thanks

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

Similar Threads

  1. Replies: 12
    Last Post: 05-08-2019, 07:11 PM
  2. Copy structure of existing table to a new table
    By DavidWrightSr in forum Access
    Replies: 1
    Last Post: 05-04-2017, 04:46 PM
  3. Replies: 5
    Last Post: 12-01-2016, 11:23 AM
  4. Replies: 4
    Last Post: 11-04-2013, 02:24 PM
  5. Copy Table Structure (only) Problem
    By homerj56 in forum Access
    Replies: 1
    Last Post: 07-16-2010, 10:36 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