Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: COnsequence of Index Removal

    Hello again, Colleagues All,
    One of the database models I have designed contains a lot of metadata which controls forms (switching pages on/off and so forth) and visible data (this factory or that factory) - well, you get the idea. Such tables frequently contain an awful lot of fields with small items of information, such as codes, numbers and boolean flags. Their structure is frequently updated as the architecture develops. While extending a table in this way, I got a message to say that I could not add any more fields because the table had reached its quota (32) of indexes. As we know, Access is very fond of creating indexes to optimise its own operations. I duly went into the index editor (View/Indexes) and deleted a lot o rubbishy ones I did not need. However, when I next opened up the table, I found that Access had sorted all of the fields into alphabetical order. Now I had grouped my fields by function to display the data in a way that would make it easier for the users to understand what was going on. My question is; is there an internal field ID or something which I can use to resort the fields back into their original order ? Alternatively, as I do have a backup, is there a way in which I can drop the indexes without resorting the fields ?
    The moral of this story is that, when creating or editing tables, check EVERY field and make sure indexing is turned off unless you think you really need it.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Just to clear things up, when you say "field" do you mean "column"? (field isn't actually a database term, but it gets used a lot. In this case I think disambiguation is important). Do you also mean that the order of the column names has changed? Or do you mean that when you open the table, the rows are ordered by different column data?

    I think you mean the latter, but I just want to be sure.

    Strictly speaking in the relational model there is no inherent order to rows in a table - if an ORDER BY clause is not specified then the rows can be returned in any order that suits the RDBMS. However, with Access (I suspect because it is file based) if you open the table itself the rows are ALWAYS sorted in the order of the clustered index.The clustered index in JET is (TMK) always the primary key. So - I would check that the table still has a primary key - I would expect the table to be sorted by that column(s) when you open it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Clarification

    What I meant to say is that when you open up the table in design view, the list of column names is in a different order. Sorry if I was not clear before. In one sense it is no big deal, but when the primary key which one expects to see at the top of the list (or on the LHS edge of the displayed table) and it is actually now column number 51, it can be a bit disconcerting !

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is odd - I've never heard of that happening. Like there is no order to rows, there is actually no order to columns in the relational model, but I have never heard of any RDBMS actually changing column order.

    I imagine you know you can manually change the column order? It is the nuisance factor that is inconveniencing you right?

    Are you able to outline some steps that we could test to see if this repeats?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think I have seen the PK out of sequence once or twice IIRC it may have been reset after a compact and repair.

    Its so long ago, but I think i put down the positon to the order the columns were first defined, or it may be the PK was modified or defined after the colukns were defined. strictly speaking it shouldn't make any difference except it looks a bit odd at first glance having got used to the idea that the PK 'should' always be the first column
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - by convention people usually put the pk column(s) first but this is not a property of PK columns. Defining a PK column does not move it up to the top either. I've never seen Access change the column order though - that is odd.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Just a quick note to say that I repeated the operation from a backup file and it worked perfectly. I probably managed to hit a control key somewhere which initiated some kind of sort operation. Does Windows save the most-recent keystrokes, I wonder ! Anyway, thanks for the insights.

Posting Permissions

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