Sort Order changed in Access 2003 Back End database

I have a strange problem with a split Access 2003 database that is now
sorting the records on an ID number, where it wasn't previously.
Until now, when the users viewed the data that they keyed, it was
displayed in the order in which it was keyed. In other words, each
new recorded was appended at the end (which is how they preferred
it). A couple of weeks ago, I made some minor changes to the program.
The most significant change was adding a new field (a 1 character
boolean flag) to one of the tables. When I installed the changes on
the user's LAN, I imported 2 of their permanent tables into my new
version. When the users started using the program, they noticed that
the records they keyed in, were being sorted when they went to display
the records that they had keyed. I have checked the program and the
subform that I use to display the records uses a simple (Select * from
Renewals). I cannot find any place where the program is determining
it should sort the records being keyed (when it wasn't doing it
before).

Any ideas?

TIA.

Stuart

Re: Sort Order changed in Access 2003 Back End database

Hi Stuart,

Try using temp tables in the front end program. This method assumes
that each user has an individual copy of the front end. If it is a
shared front end - I would unshare it very quickly.

Pull data from the back end into these temp tables are work off these
tables and then write the results back to the back end. This is how it
is done in an enterprise system (.Net with sql server back end).

This will increase I/O but only locally on each individual's workstation
and not on the backend. You want to keep I/O on the backend to a
minimum. I will guess that too much I/O and being connected directly to
the source data is contributing to your problem.

The Access mdb is pretty good at mimicking disconnected recordsets like
in .Net except that in Access you have I/O since your temp tables will
reside on the disk where in ADO.Net it all happens in memory (thus
reducing I/O by almost 90% maybe more).


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Sort Order changed in Access 2003 Back End database

"stuart" wrote in message
news:1194382297.522240.91990@d55g2000hsg.googlegroups.com...
> I have a strange problem with a split Access 2003 database that is now
> sorting the records on an ID number, where it wasn't previously.
> Until now, when the users viewed the data that they keyed, it was
> displayed in the order in which it was keyed. In other words, each
> new recorded was appended at the end (which is how they preferred
> it). A couple of weeks ago, I made some minor changes to the program.
> The most significant change was adding a new field (a 1 character
> boolean flag) to one of the tables. When I installed the changes on
> the user's LAN, I imported 2 of their permanent tables into my new
> version. When the users started using the program, they noticed that
> the records they keyed in, were being sorted when they went to display
> the records that they had keyed. I have checked the program and the
> subform that I use to display the records uses a simple (Select * from
> Renewals). I cannot find any place where the program is determining
> it should sort the records being keyed (when it wasn't doing it
> before).
>
> Any ideas?
>
> TIA.
>
> Stuart
>

The sort order is controlled by your forms and reports, tables don't have a
sort order. The default sort of a form can be changed by sorting on a column
and then saving the form. This can easily happen while editing a form to add
a new control for the new field.

Re: Sort Order changed in Access 2003 Back End database

stuart wrote in news:1194382297.522240.91990
@d55g2000hsg.googlegroups.com:

> I have a strange problem with a split Access 2003 database that is now
> sorting the records on an ID number, where it wasn't previously.
> Until now, when the users viewed the data that they keyed, it was
> displayed in the order in which it was keyed. In other words, each
> new recorded was appended at the end (which is how they preferred
> it). A couple of weeks ago, I made some minor changes to the program.
> The most significant change was adding a new field (a 1 character
> boolean flag) to one of the tables. When I installed the changes on
> the user's LAN, I imported 2 of their permanent tables into my new
> version. When the users started using the program, they noticed that
> the records they keyed in, were being sorted when they went to display
> the records that they had keyed. I have checked the program and the
> subform that I use to display the records uses a simple (Select * from
> Renewals). I cannot find any place where the program is determining
> it should sort the records being keyed (when it wasn't doing it
> before).

I copy the Employees Table from Northwind to a new mdb. I remove all its
indexes.
I open the table. The records appear in the order of the autonumber ID key,
which is likely to be the order entered.
I create an unique non-null index on LastName. As there is no index called
"PrimaryKey" this index becomes the primary key, that it is the first
(alphabetical order) unique non-null index of the table.
(Of course, I could have called this index "PrimaryKey" in which case it
would have been moved to first place in the order of unique non-null
indexes.)
I open the table. It appears in the order of LastName.

Perhaps, an index was added to Renewals.

--
lyle fairfield

Re: Sort Order changed in Access 2003 Back End database

Rich P wrote in news:4730e241$0$497$815e3792
@news.qwest.net:

> Hi Stuart,
>
> Try using temp tables in the front end program. This method assumes
> that each user has an individual copy of the front end. If it is a
> shared front end - I would unshare it very quickly.
>
> Pull data from the back end into these temp tables are work off these
> tables and then write the results back to the back end. This is how it
> is done in an enterprise system (.Net with sql server back end).
>
> This will increase I/O but only locally on each individual's workstation
> and not on the backend. You want to keep I/O on the backend to a
> minimum. I will guess that too much I/O and being connected directly to
> the source data is contributing to your problem.
>
> The Access mdb is pretty good at mimicking disconnected recordsets like
> in .Net except that in Access you have I/O since your temp tables will
> reside on the disk where in ADO.Net it all happens in memory (thus
> reducing I/O by almost 90% maybe more).
>
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***

If you and I both load Employees table from Northwind to a temporary table
in our own separate frontends at approximately the same time, and you
change Laura Callahan's address to 8 Bloor St, Toronto ON, and I correct
Laura Calahan's address from 4726 - 11th Ave. N.E. to 4726 - 11th Ave.
N.W. and we both save our changes back to the back-end, how is this
reconciled. If I save mine after you save yours, will yours be lost? Is
that a good thing?



--
lyle fairfield

Re: Sort Order changed in Access 2003 Back End database

On Nov 6, 4:58 pm, "paii, Ron" wrote:

> The sort order is controlled by your forms and reports, tables don't have a
> sort order.

What about tables with a primary index?

Re: Sort Order changed in Access 2003 Back End database

"lyle" wrote in message
news:1194406857.079390.209920@o80g2000hse.googlegroups.com.. .
> On Nov 6, 4:58 pm, "paii, Ron" wrote:
>
> > The sort order is controlled by your forms and reports, tables don't
have a
> > sort order.
>
> What about tables with a primary index?
>

Indexes can be added and removed, they don't change the data in the table.
The data may limit what you can do with or to an index.

Re: Sort Order changed in Access 2003 Back End database

"paii, Ron" wrote

> The sort order is controlled by your forms and reports,
> tables don't have a sort order. The default sort of a
> form can be changed by sorting on a column and then
> saving the form. This can easily happen while editing
> a form to add a new control for the new field.

Sort order can be set in Queries, by making a choice in the Sort line of the
Grid, or in SQL by using an ORDER BY clause. The sort order of a Query that
is Record Source for a Form will affect the order in which you see the
Records. The sort order of a Query that is Record Source for a Report will
have no effect on the order -- you must set it in the Sorting and Grouping
properties.

Tables, as you say, by definition, have no sort order, but, hey, Access has
got to show them in _some_ order. If there is a Primary Key, that is
normally (but not necessarily) what is used. And, the order in which Records
are displayed in Datasheet View of Tables or Queries or in Forms is (1)
possibly affected by apparently unrelated items and (2) not always what you
think it should be.

If you wish Records to be in a particular order, you need to retrieve them
with a Query that specifies the order, or have set the Sorting and Grouping
properties of the Report you are viewing. The nearest I can think of to
"assuring" the records are viewed in entry order would be to use a
(non-random) Autonumber as Primary Key, or specify a Date/Time field with a
default of Now() as the Primary Key or the first field of a multifield
Primary Key.

Larry Linson
Microsoft Access MVP

Re: Sort Order changed in Access 2003 Back End database

I work in a place where we have multiple data entry people. All the
data entry has to pass through one table on the server (a portal of data
entry table). This was originally happening through an Access ADP front
end (when it was just a single user operation). But the ADP failed
miserably when more than one person was entering data because all the
data gets entered into the same table which a stored procedure would
then distribute throughout the system. So I migrated the app to .Net.

The stored procedure would check to see if various records in the system
had already been updated. If yes, then don't update again. But we had
lots of problems with the ADP. In the .Net program I can check for
updates first. So when a user posts the updates (if the .Net program
will allow the posting because conditions have been met), all the data
goes through this one portal of entry table and then gets distributed.
This method (methods) also solves issues with Record Locking / dead
locking because the users have to take turns posting their data.

So your scenariou has already been addressed. Plus, I made the system
relational, so it won't allow updates if a primary table does (or does
not) already contain some basic information. If a key violation error
occurs I have several custom RaiseError messages from the sql server
specifying what the problem is. The .Net app can read all these
messages. I had problems reading most of these error messages from the
ADP.

My suggestion, I suppose, pertains to a .Net solution in an enterprise
system (ADO.Net handle Record/Dead locking very nicely). If you post an
update before I do in .Net, the .Net app can notify the 2nd poster of
this update instantaneously (if it is programmed to do so) before the
data of the 2nd poster ever makes it to the server.

It sounds like you are suggesting that for a micro RDBMS like Access,
this kind of functionality may be a little more challenging (performance
wise if anything) to achieve. I just thought it might be worth a try,
especially for reducing record locking. The hard part, it sounds like,
would be checking for updates from the first poster before the 2nd
person posts his updates.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Sort Order changed in Access 2003 Back End database

On Nov 7, 11:32 am, "Larry Linson" wrote:
> "paii, Ron" wrote
>
> > The sort order is controlled by your forms and reports,
> > tables don't have a sort order. The default sort of a
> > form can be changed by sorting on a column and then
> > saving the form. This can easily happen while editing
> > a form to add a new control for the new field.
>
> Sort order can be set in Queries, by making a choice in the Sort line of the
> Grid, or in SQL by using an ORDER BY clause. The sort order of a Query that
> is Record Source for a Form will affect the order in which you see the
> Records. The sort order of a Query that is Record Source for a Report will
> have no effect on the order -- you must set it in the Sorting and Grouping
> properties.
>
> Tables, as you say, by definition, have no sort order, but, hey, Access has
> got to show them in _some_ order. If there is a Primary Key, that is
> normally (but not necessarily) what is used. And, the order in which Records
> are displayed in Datasheet View of Tables or Queries or in Forms is (1)
> possibly affected by apparently unrelated items and (2) not always what you
> think it should be.
>
> If you wish Records to be in a particular order, you need to retrieve them
> with a Query that specifies the order, or have set the Sorting and Grouping
> properties of the Report you are viewing. The nearest I can think of to
> "assuring" the records are viewed in entry order would be to use a
> (non-random) Autonumber as Primary Key, or specify a Date/Time field with a
> default of Now() as the Primary Key or the first field of a multifield
> Primary Key.
>
> Larry Linson
> Microsoft Access MVP


The Renewals table I initially addressed has no primary key. The
query I use to populate the subform has no "Order by" clause or any
type of sorting. I just wanted the records returned by the order in
which they were keyed into the system. There are 3 indexes set on the
table "Fee code", "NCID Number", "Update Code" (in that order). It
seems like it ignores the first index, other times, it is using the
2nd index, other times, it returns the records in the exact order in
which they were keyed. Not sure what is happening here...

Re: Sort Order changed in Access 2003 Back End database

stuart wrote in
news:1194563621.748172.260030@e9g2000prf.googlegroups.com:

> The Renewals table I initially addressed has no primary key. The
> query I use to populate the subform has no "Order by" clause or any
> type of sorting. I just wanted the records returned by the order in
> which they were keyed into the system. There are 3 indexes set on the
> table "Fee code", "NCID Number", "Update Code" (in that order). It
> seems like it ignores the first index, other times, it is using the
> 2nd index, other times, it returns the records in the exact order in
> which they were keyed. Not sure what is happening here...

When a table does not have an index called "PrimaryKey" and does have one
or more unique non-null indexes it does have a primary key; that primary
key is the first (in alphabetical order by index name) of those unique non-
null indexes. If the indexes you name are all the indexes of the table, and
if they are all unique and non-null, then Fee Code will be the table's
primary key, and records will be returned in the order of that key, unless
someother order is specified. If Fee Code is not (unique and non-null) then
NCI number will be the primary key, if it meets the criteria.

Access and JET writers often try to maintain this myth about "Primary Key".
Primary Key means "First" key. When we name an eligible index PrimaryKey,
JET responds by making it the first index. And it uses the first index.
When we don't name an eligible index, "PrimaryKey", Jet uses the first, in
alphabetical order, index it comes to as the first index: strange eh: the
first index is the first index.
And it orders records it this way.
There is nothing magical about"PrimaryKey" except how the term has confused
and is misunderstood by 95% of the Access community.

Some maintain that the Primary Key is truly clustered in JET, that is that
records are physically ordered by primary key. If that were true, it would
probably explain why you might get order by ID even if the index is
removed. But is it true in fact as well as theory? After a lot of
examination with a hex editor I can't confirm that.
IMO it's usually good form to have an index or indexes and specify an ORDER
BY when retrieving records. Why let some technology control what you can
control yourself?

--
lyle fairfield

Re: Sort Order changed in Access 2003 Back End database

On Nov 8, 7:13 pm, lyle fairfield wrote:
> stuart wrote innews:1194563621.748172.260030@e9g2000prf.googlegroups.com:
>
> > The Renewals table I initially addressed has no primary key. The
> > query I use to populate the subform has no "Order by" clause or any
> > type of sorting. I just wanted the records returned by the order in
> > which they were keyed into the system. There are 3 indexes set on the
> > table "Fee code", "NCID Number", "Update Code" (in that order). It
> > seems like it ignores the first index, other times, it is using the
> > 2nd index, other times, it returns the records in the exact order in
> > which they were keyed. Not sure what is happening here...
>
> When a table does not have an index called "PrimaryKey" and does have one
> or more unique non-null indexes it does have a primary key; that primary
> key is the first (in alphabetical order by index name) of those unique non-
> null indexes. If the indexes you name are all the indexes of the table, and
> if they are all unique and non-null, then Fee Code will be the table's
> primary key, and records will be returned in the order of that key, unless
> someother order is specified. If Fee Code is not (unique and non-null) then
> NCI number will be the primary key, if it meets the criteria.
>
> Access and JET writers often try to maintain this myth about "Primary Key".
> Primary Key means "First" key. When we name an eligible index PrimaryKey,
> JET responds by making it the first index. And it uses the first index.
> When we don't name an eligible index, "PrimaryKey", Jet uses the first, in
> alphabetical order, index it comes to as the first index: strange eh: the
> first index is the first index.
> And it orders records it this way.
> There is nothing magical about"PrimaryKey" except how the term has confused
> and is misunderstood by 95% of the Access community.
>
> Some maintain that the Primary Key is truly clustered in JET, that is that
> records are physically ordered by primary key. If that were true, it would
> probably explain why you might get order by ID even if the index is
> removed. But is it true in fact as well as theory? After a lot of
> examination with a hex editor I can't confirm that.
> IMO it's usually good form to have an index or indexes and specify an ORDER
> BY when retrieving records. Why let some technology control what you can
> control yourself?
>
> --
> lyle fairfield

Thanks for your answer. I do have a followup question. If I view the
records in the back end database vs. viewing the records through the
application, shows a different order. The back end is sorted how I
would expect, based on your answer, but the application seems to
display the records in no particular order. Granted, I don't have an
order by clause defined on the query to populate the subform, so how
does Access decide the order in which to return the records? It
appears it is in the order in which they are keyed, but does Access
store information about the order in which the records were added to a
table?

Re: Sort Order changed in Access 2003 Back End database

stuart wrote in
news:1194964623.317387.63770@o80g2000hse.googlegroups.com:

> On Nov 8, 7:13 pm, lyle fairfield wrote:
>> stuart wrote
>> innews:1194563621.748172.260030@e9g2000prf.googlegroups.com:
>>
>> > The Renewals table I initially addressed has no primary key. The
>> > query I use to populate the subform has no "Order by" clause or
>> > any type of sorting. I just wanted the records returned by the
>> > order in which they were keyed into the system. There are 3
>> > indexes set on the table "Fee code", "NCID Number", "Update Code"
>> > (in that order). It seems like it ignores the first index, other
>> > times, it is using the 2nd index, other times, it returns the
>> > records in the exact order in which they were keyed. Not sure what
>> > is happening here...
>>
>> When a table does not have an index called "PrimaryKey" and does have
>> one or more unique non-null indexes it does have a primary key; that
>> primary key is the first (in alphabetical order by index name) of
>> those unique non- null indexes. If the indexes you name are all the
>> indexes of the table, and if they are all unique and non-null, then
>> Fee Code will be the table's primary key, and records will be
>> returned in the order of that key, unless someother order is
>> specified. If Fee Code is not (unique and non-null) then NCI number
>> will be the primary key, if it meets the criteria.
>>
>> Access and JET writers often try to maintain this myth about "Primary
>> Key". Primary Key means "First" key. When we name an eligible index
>> PrimaryKey, JET responds by making it the first index. And it uses
>> the first index. When we don't name an eligible index, "PrimaryKey",
>> Jet uses the first, in alphabetical order, index it comes to as the
>> first index: strange eh: the first index is the first index.
>> And it orders records it this way.
>> There is nothing magical about"PrimaryKey" except how the term has
>> confused and is misunderstood by 95% of the Access community.
>>
>> Some maintain that the Primary Key is truly clustered in JET, that is
>> that records are physically ordered by primary key. If that were
>> true, it would probably explain why you might get order by ID even if
>> the index is removed. But is it true in fact as well as theory? After
>> a lot of examination with a hex editor I can't confirm that.
>> IMO it's usually good form to have an index or indexes and specify an
>> ORDER BY when retrieving records. Why let some technology control
>> what you can control yourself?
>>
>> --
>> lyle fairfield
>
> Thanks for your answer. I do have a followup question. If I view the
> records in the back end database vs. viewing the records through the
> application, shows a different order. The back end is sorted how I
> would expect, based on your answer, but the application seems to
> display the records in no particular order. Granted, I don't have an
> order by clause defined on the query to populate the subform, so how
> does Access decide the order in which to return the records? It
> appears it is in the order in which they are keyed, but does Access
> store information about the order in which the records were added to a
> table?

Upon further review I have concluded that I need to do more testing about
this issue and that my position on it could be wrong, even very wrong.

So I think I should just shut up until I do.

I apologise if I have misled you or anyone else.

--
lyle fairfield

Re: Sort Order changed in Access 2003 Back End database

On Nov 13, 10:27 am, lyle fairfield wrote:
> stuart wrote innews:1194964623.317387.63770@o80g2000hse.googlegroups.com:
>
>
>
>
>
> > On Nov 8, 7:13 pm, lyle fairfield wrote:
> >> stuart wrote
> >> innews:1194563621.748172.260030@e9g2000prf.googlegroups.com:
>
> >> > The Renewals table I initially addressed has no primary key. The
> >> > query I use to populate the subform has no "Order by" clause or
> >> > any type of sorting. I just wanted the records returned by the
> >> > order in which they were keyed into the system. There are 3
> >> > indexes set on the table "Fee code", "NCID Number", "Update Code"
> >> > (in that order). It seems like it ignores the first index, other
> >> > times, it is using the 2nd index, other times, it returns the
> >> > records in the exact order in which they were keyed. Not sure what
> >> > is happening here...
>
> >> When a table does not have an index called "PrimaryKey" and does have
> >> one or more unique non-null indexes it does have a primary key; that
> >> primary key is the first (in alphabetical order by index name) of
> >> those unique non- null indexes. If the indexes you name are all the
> >> indexes of the table, and if they are all unique and non-null, then
> >> Fee Code will be the table's primary key, and records will be
> >> returned in the order of that key, unless someother order is
> >> specified. If Fee Code is not (unique and non-null) then NCI number
> >> will be the primary key, if it meets the criteria.
>
> >> Access and JET writers often try to maintain this myth about "Primary
> >> Key". Primary Key means "First" key. When we name an eligible index
> >> PrimaryKey, JET responds by making it the first index. And it uses
> >> the first index. When we don't name an eligible index, "PrimaryKey",
> >> Jet uses the first, in alphabetical order, index it comes to as the
> >> first index: strange eh: the first index is the first index.
> >> And it orders records it this way.
> >> There is nothing magical about"PrimaryKey" except how the term has
> >> confused and is misunderstood by 95% of the Access community.
>
> >> Some maintain that the Primary Key is truly clustered in JET, that is
> >> that records are physically ordered by primary key. If that were
> >> true, it would probably explain why you might get order by ID even if
> >> the index is removed. But is it true in fact as well as theory? After
> >> a lot of examination with a hex editor I can't confirm that.
> >> IMO it's usually good form to have an index or indexes and specify an
> >> ORDER BY when retrieving records. Why let some technology control
> >> what you can control yourself?
>
> >> --
> >> lyle fairfield
>
> > Thanks for your answer. I do have a followup question. If I view the
> > records in the back end database vs. viewing the records through the
> > application, shows a different order. The back end is sorted how I
> > would expect, based on your answer, but the application seems to
> > display the records in no particular order. Granted, I don't have an
> > order by clause defined on the query to populate the subform, so how
> > does Access decide the order in which to return the records? It
> > appears it is in the order in which they are keyed, but does Access
> > store information about the order in which the records were added to a
> > table?
>
> Upon further review I have concluded that I need to do more testing about
> this issue and that my position on it could be wrong, even very wrong.
>
> So I think I should just shut up until I do.
>
> I apologise if I have misled you or anyone else.
>
> --
> lyle fairfield- Hide quoted text -
>
> - Show quoted text -

No need to apologize!!! I appreciate the time to answer the question.