Automatically Sort Table when Table is Opened in Project

Hi all,

We recently upsized two Microsoft Access Databases to SQL. We're
using an ADP (2002) as the front end.

All the conversion issues have been resolved, except for one:
Whenever we insert a record into a table, the table isn't sorted by
primary key like I would expect. Instead, the record can be found at
the end of the table. This makes finding a particular record
(especially as time goes on) very difficult.

I've tried eliminating all indexes except for the primary key, and
also writing AFTER INSERT triggers, but the table still does not sort
correctly.

Any suggestions would be greatly appreciated!

MattThis is a client-side problem since a table in SQL has no inherent logical
order. I don't think Access will resort the table unless you requery it. To
get around this you will probably need to create your own form for data
entry and display - a good idea anyway if you are doing a lot of data entry.

--
David Portas
SQL Server MVP
--|||Yes adding to David's post, you can define the sort order for the adp form
(you can make the form look virtually identical to a datasheet/table if
you'd like). It will be a string like an Order by clause. I believe you
need the sort string and you need to set the OrderByOn property to true. You
can add code to react to a new record or whatever. Good luck.
~ck

"Matt" <mhen001@.yahoo.com> wrote in message
news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> Hi all,
> We recently upsized two Microsoft Access Databases to SQL. We're
> using an ADP (2002) as the front end.
> All the conversion issues have been resolved, except for one:
> Whenever we insert a record into a table, the table isn't sorted by
> primary key like I would expect. Instead, the record can be found at
> the end of the table. This makes finding a particular record
> (especially as time goes on) very difficult.
> I've tried eliminating all indexes except for the primary key, and
> also writing AFTER INSERT triggers, but the table still does not sort
> correctly.
> Any suggestions would be greatly appreciated!
> Matt|||oops in a project you use these properties.
ServerFilter Property

You can use the ServerFilter property to specify a subset of records to be
displayed when a server filter is applied to a form or report within a
Microsoft Access project (.adp) or a data access page in a Microsoft Access
project (.adp) or database (.mdb).

Setting

The ServerFilter property is a string expression consisting of a WHERE
clause without the WHERE keyword. For example, the following Visual Basic
code defines and applies a filter to show only customers from the USA:

Me.ServerFilter = "Country = 'USA'"
Me.ServerFilterByForm = TrueThe easiest way to set this property is by using
a form or report's property sheet. You can also set this property on a form
or report by using Visual Basic.

CK

"Matt" <mhen001@.yahoo.com> wrote in message
news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> Hi all,
> We recently upsized two Microsoft Access Databases to SQL. We're
> using an ADP (2002) as the front end.
> All the conversion issues have been resolved, except for one:
> Whenever we insert a record into a table, the table isn't sorted by
> primary key like I would expect. Instead, the record can be found at
> the end of the table. This makes finding a particular record
> (especially as time goes on) very difficult.
> I've tried eliminating all indexes except for the primary key, and
> also writing AFTER INSERT triggers, but the table still does not sort
> correctly.
> Any suggestions would be greatly appreciated!
> Matt|||Thanks for the replies. It looks like I'll have to go the form route.

The strange thing is: if you open up the properties of the table and
click the "Data" tab, you can specify the "ORDER BY" property. Help
defines this property as:

"Shows client-side sorting criteria sorting criteria applied after
the result set is returned from the database. This property is a
string expression that is the name of the field or fields on which you
want to sort records. When you use more than one field name, separate
the names with a comma (,). If you want to sort records in descending
order, type DESC at the end of the string expression."

Why would Microsoft create this property yet in practice not allow you
to sort tables on the client-side?

Matt

"ck" <c_kettenbach@.hotmail.com> wrote in message news:<5CMBc.3267$6F6.1885@.newssvr25.news.prodigy.com>...
> oops in a project you use these properties.
> ServerFilter Property
>
> You can use the ServerFilter property to specify a subset of records to be
> displayed when a server filter is applied to a form or report within a
> Microsoft Access project (.adp) or a data access page in a Microsoft Access
> project (.adp) or database (.mdb).
> Setting
> The ServerFilter property is a string expression consisting of a WHERE
> clause without the WHERE keyword. For example, the following Visual Basic
> code defines and applies a filter to show only customers from the USA:
> Me.ServerFilter = "Country = 'USA'"
> Me.ServerFilterByForm = TrueThe easiest way to set this property is by using
> a form or report's property sheet. You can also set this property on a form
> or report by using Visual Basic.
> CK
> "Matt" <mhen001@.yahoo.com> wrote in message
> news:ea450280.0406211322.7b5e60e2@.posting.google.c om...
> > Hi all,
> > We recently upsized two Microsoft Access Databases to SQL. We're
> > using an ADP (2002) as the front end.
> > All the conversion issues have been resolved, except for one:
> > Whenever we insert a record into a table, the table isn't sorted by
> > primary key like I would expect. Instead, the record can be found at
> > the end of the table. This makes finding a particular record
> > (especially as time goes on) very difficult.
> > I've tried eliminating all indexes except for the primary key, and
> > also writing AFTER INSERT triggers, but the table still does not sort
> > correctly.
> > Any suggestions would be greatly appreciated!
> > Matt