Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Order lost by compacting (2003)

    Hope someone can help, this is driving me mad.
    My code runs a very simple make-table query which creates a table ORDERED BY five fields.
    Once the code has run the table is sorted correctly. However, the db has grown considerably so I compact it.
    Once the compact is complete & the db re-opens, the sort order of the table has gone wrong.
    Is this something anyone else has come across?
    Thanks
    Irene

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Order lost by compacting (2003)

    Records aren't stored in any particular order in a table. If you want the records to be sorted by default, you should set the primary key of the table to the appropriate combination of fields.

    Unfortunately, a make-table query doesn't let you specify the primary key. A workaround is to take the table (created by the make-table query or manually) and set the primary key.
    From then on, don't run the make-table query any more, but first run a delete query to move all existing records, then run an append query to repopulate the table. Both the delete query and the append query will leave the primary key (and any other indexes) intact.

  3. #3
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Order lost by compacting (2003)

    Thanks Hans, didn't realise that sort order wasn't saved with the table, what a nuisance :-)
    Unfortunately, using a static table structure and appending to it isn't an option. The new table is created dynamically each month from an underlying crosstab query which uses a "month" field to create the new table's field headings (sorry if this sounds confusing) e.g. this month the new table's fields are DataType, MasterAgentName, AgentAge, SalesStatus, 200710, 200711 .......... 200808,200809; when the process is re-run next month the first four fields will be the same followed by 200711, 200712 ...... 200809, 200810 and so on. I want the table to be sorted DataType, MasterAgentName, AgentAge, SalesStatus.
    Is there any way of programatically creating a primary key made up of multiple fields?
    Appreciate you taking time to answer.
    Regards
    Irene

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Order lost by compacting (2003)

    The following code needs a reference (in Tools | References... in the Visual Basic Editor) to the Microsoft DAO 3.6 Object Library.

    Change the table name and field names as needed. The order in which the fields are appended to the index in the code is the order in which they will occur in the primary key, and this determines how the table will be sorted.


    Sub CreatePK()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Table1")
    Set idx = tdf.CreateIndex("Primary Key")
    With idx
    .Fields.Append .CreateField("Field1")
    .Fields.Append .CreateField("Field2")
    .Fields.Append .CreateField("Field3")
    .Fields.Append .CreateField("Field4")
    .Fields.Append .CreateField("Field5")
    .Primary = True
    End With
    tdf.Indexes.Append idx
    Set idx = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    End Sub

  5. #5
    New Lounger
    Join Date
    Sep 2008
    Location
    Liverpool, Merseyside, United Kingdom
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Order lost by compacting (2003)

    That's absolutely great Hans, works brilliantly.
    Thank you so much.
    Irene

Posting Permissions

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