Re-ordering of Tables

  • Thread starter Thread starter Damon Heron
  • Start date Start date
D

Damon Heron

A General question during development process - could the re-ordering of
tables, for instance, if I sort a table on a particular field then save it-
cause data corruption? Lately I have been having strange corruptions where
2 things happen -
1) when I look at a table and click on the column of a field, all of the
fields to the left highlight - I can't just highlite one column.
2) when I write to a table in VB, it won't go beyond the last record number,
it keeps overwriting the same record.

If I close Access, and re-open, after a compact and save, it works as
designed.
Any ideas or similar experiences?
 
Sorting the table does not affect how data is stored. All you are saving is
the requested sort order. It sounds like you have something that is
corrupting an index or the autonumber. Make sure that you have enough
system memory and disk space. Access acts rather strangely when it doesn't
have enough work space. Try to pinpoint what action precedes the
corruption.
 
Dear Pat:

There is an important exception to this.

Microsoft Access ships with MSDE. MSDE allows one clustered index per
table. This index DOES sort the rows in the table in the order of the
index.

Probably, this has nothing to do with the user's question. However, for the
sake of performance, this can (and very often does) cause an order of
magnitude increase in performance. This is an important and extremely
useful factor.

Does Access Jet also change the physical order of data according to the PK
when the database "compact and repair" is executed? It sounds like this is
the thing Damon is asking about. By the way, Damon, it is "compact and
REPAIR" which may be important to what is happening.

Tom Ellison


Pat Hartman(MVP) said:
Sorting the table does not affect how data is stored. All you are saving
is the requested sort order. It sounds like you have something that is
corrupting an index or the autonumber. Make sure that you have enough
system memory and disk space. Access acts rather strangely when it
doesn't have enough work space. Try to pinpoint what action precedes the
corruption.
Click to expand...
 
Important exception to what? When you specify sort order on a form, you are
reordering a query not the table itself and the save prompt is regards to
saving the sort sequence not the sorted data. Yes MSDE does support a
clustered index but that has nothing to do with the question at hand. And
yes, Jet does reorder each table into primary key sequence during a compact
and repair. This is effectively like a clustered index but only until data
is added to the table. Unlike MSDE, SQL Server, and all other RDBMS's that
support clustered indexes which insert rows into "sorted" order physically,
Jet just adds new rows to the end of the table space. The new rows don't
get placed into physical key order until the database is compacted.
Tom Ellison said:
Dear Pat:

There is an important exception to this.

Microsoft Access ships with MSDE. MSDE allows one clustered index per
table. This index DOES sort the rows in the table in the order of the
index.

Probably, this has nothing to do with the user's question. However, for
the sake of performance, this can (and very often does) cause an order of
magnitude increase in performance. This is an important and extremely
useful factor.

Does Access Jet also change the physical order of data according to the PK
when the database "compact and repair" is executed? It sounds like this
is the thing Damon is asking about. By the way, Damon, it is "compact and
REPAIR" which may be important to what is happening.

Tom Ellison
Click to expand...
 
Dear Pat:

The exception is to the first sentence you wrote:

"Sorting the table does not affect how data is stored."

If you mean "sorting the table in a query, form, or report" then that is
true. If you mean sorting the table by a PK and compact and repair (Jet) or
clustered index (MSDE) then that is the exception. By judicious choice of
the key column(s) for the PK or CI, this may be exactly the same order as is
required for many of the reports and forms you write.

If you create a table so that the rows are physically ordered according to a
certain key, and then reference them by that same key on a form, then the
rows will be in the order accessed and performance will improve. The time
it would take to order a table in the order it is already ordered is very
brief. There is some performance advantage in this.

In the case of a compact and repair, the rows added subsequently would not
be in physical order, as you state. However, if the majority or rows are
already sorted physically, there will still usually be a large improvement
in performance.

Tom Ellison
 
I think you're splitting hairs here and reading more into the question than
is actually there. Sorting the table does not affect how data is stored
period. Changing the primary key can change the physical row order
depending on if the table is Jet or something else and if the index on the
pk is clustered.
In any event Damon is experiencing some type of corruption which sounds like
it is being caused by his own code since a compact/repair clears the
problem.
I have a database that makes extensive use of timer events. If I forget and
modify a form while some other form with a timer event is open, the form, I
am working on becomes corrupted. However a compact/repair doesn't fix the
problem. I need to actually import a backup copy of the form.
 
Dear Pat:

If you'll check with your fellow MVPs, you'll find I generally maintain good
relations with them. I'm not one to bash.

I'm not reading anything into the question of the OP. I simply wanted to
clear the record regarding a statement you made.

I agree regarding the likely source of the problem in this thread. I do not
disagree with your attempts within that narrow context.

I don't agree that I'm splitting hairs. The affect of creating and
implementing the best PK or clustered index is extremely dramatic, and very
important.

With respect to the choice of a Primary Key (Jet) or clustered index (MSDE),
sorting the table has a very important affect on how data is stored. In all
other contexts it does not.

Tom Ellison
 
Dear Tom,
The question was not about creating primary keys or whether clustered
indexes are better than non-clustered indexes. It was about sorting via the
UI. Sorting does not physically alter how the data is stored. The poster
didn't know that. He thought that the save prompt was asking to save data
in the new order when in fact the save prompt was only asking to save the
sort parameters. I don't disagree that a clustered index can enhance
performance. It is just not what we are talking about.
 
Back
Top