Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: Adding a primary index to a remote table

    I have a table with fields Date, PCP, Demand.

    I want to add a primary index to it on Date and PCP - i.e. each date-pcp combination is unique.

    This is part of a large-ish application, and is all being run with VBA, so I can't just go into design view.

    Anyway, my SQL to do this is
    CREATE UNIQUE INDEX myIndex ON [PCPD] ([Date], [PCP]) WITH PRIMARY;

    Now, here's the catch:
    my table that I'm wanting to index (PCPD) is in a remote Database. I mean, it's in the same folder as my front end, but is not in my front end database where I've got my code.

    When I make that table I do it by saying SELECT [blah blah] INTO PCPD IN TempDB.mdb FROM [blah blah];

    Can anyone tell me a way that I can use something like the "IN TempDB.mdb" trick above to alter the table I'm putting the data into.

    Cheers,
    B.

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Dim db As DAO.Database
    Set db = DbEngine.OpenDatabase("C:\Path\File.mdb")
    db.Execute "CREATE UNIQUE INDEX myIndex ON [PCPD] ([Date], [PCP]) WITH PRIMARY;"
    Set db = Nothing

    The above code works with DAO. For ADO, change DAO.Database with ADODB.Connection and you're home free.

    Good luck

    Dan

Posting Permissions

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