Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    17

    Unanswered: Transferdatabase method and automatic indicies

    In VB I am trying to make a copy of a table from one external database to another (all the databases are in MS Access).

    I can do this by using the TransferDatabase method, first importing from my source database (structure only) then exporting to the desired destination.

    This works fine except that the "AutoIndex on Import/Create" option (which is set to the default "ID;key;code;num") create additional new indicies in my table imported/exported table which cause problems later on in terms of speed.
    Basically there are so many new indicies the the speed of accessing the table is compromised.

    To my mind there are two possible solutions

    1. Clear the "AutoIndex on Import/Create" field
      I cannot find the correct method to do this in VB
    2. Remove the newly created indicies
      I can programmatically remove indicies from the table, but have idea which are new and which are existing indicies
    3. Copy the index structure
      First remove ALL the indicies from the imported table, then rebuild the index structure using a linked copy of the original table (a linked table does not have AutoIndicies)

    Option 3. seems the most thorough to me but is a bit cumbersome. Ideally I would like to use option 1) before/after importing.


    My question is how can I achieve option 1) in VB OR does anyone have any better ideas?

    I am using MS Access 2000/XP/2003 and Windows XP


    Thanks in advance
    ------
    Sean

    "If at first you don't succeed, go to sleep"

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I posted some code some time ago that will generate create table ddl for a given table. I also have a function lying around that allows you to create simple indexes failry easily. This would of course take you away from the TransferDatabase route of course.

    Let me know if this is of interest.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2004
    Posts
    17
    Thanks for your reply "pootle flump" and yes I would be interested in any code you think might me helpful
    ------
    Sean

    "If at first you don't succeed, go to sleep"

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Instead of importing the table(s) from your source database, why don't you link to them instead? Then you won't be bothered by such things.

    Sam

  5. #5
    Join Date
    Jun 2004
    Posts
    17
    I can't link them because the goal is to copy a table from one remote database to another.
    ------
    Sean

    "If at first you don't succeed, go to sleep"

Posting Permissions

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