Results 1 to 14 of 14

Thread: Copying tables

  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: Copying tables

    I have a table and I want to make a copy of it.
    I used following code :

    Code:
    DoCmd.CopyObject "C:\Personal.mdb", "tblTARGET", acTable, "tblSOURCE"
    This works fine.
    I was just wondering if it's possible to only copy the structure and not the content of the table.


    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71

    Try This:

    DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Personal.mdb", acTable, "tblTARGET", "tblSOURCE", True, False

  3. #3
    Join Date
    Sep 2003
    Location
    toronto
    Posts
    6

    Re: Try This:

    is there a way to say "use the current database" rather than give the actual disk location? or can that info be retrieved into a variable and used in the command?

  4. #4
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    i think if you just leave it blank it will use the current database

  5. #5
    Join Date
    Sep 2003
    Location
    toronto
    Posts
    6
    does not seem to work and the help panel for transferdatabase implies you must enter the full path name. no mention of a default.

  6. #6
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    then try using:
    application.CurrentDb.Name

  7. #7
    Join Date
    Sep 2003
    Location
    toronto
    Posts
    6
    thanks. works fine.

    the reason i am doing this is that i have a large table that has multiple entries for a "part number" and i just want the last ones. so, i am making this copy of the table, then set the primary key for the "part number" , sort the table descending by date and then copy it is. is there a better way to do this?

  8. #8
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    upgrade to SQL server!!!!
    seriously, i don't really understand what you mean.

  9. #9
    Join Date
    Sep 2003
    Location
    toronto
    Posts
    6
    lets say i have the following records

    12345 03-02-2001
    12345 03-02-2000

    lots of other fields. i just want the latest (03-02-2001) in the table. i make the new table with the same structure and the field with 12345 as primary key. then have an append query sort the original table to the new one. since i cant have duplicate primary keys, all but the first are discarded. just wonder if there is a better way of doing this. found the procedure on a microsoft q&a site.

  10. #10
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    can't you use a combined key of part number and date. that way you can have as many records for each part number as you want

  11. #11
    Join Date
    Sep 2003
    Location
    toronto
    Posts
    6
    i only want the most recent record for each part number.

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT TOP(1) FROM <Parts Table> WHERE (PartNumber="XXXXXX") ORDER BY LastDateField DESC;

  13. #13
    Join Date
    Sep 2003
    Location
    toronto
    Posts
    6
    i don't know the partnumber. there are thousands of them

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Randy,

    You didn't say that you're wanting the lastmost in a batch mode ... In which case something like:

    SELECT DISTINCT PartNumber FROM <PartTable> ORDER BY LastDateField DESC;

Posting Permissions

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