473,391 Members | 1,427 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,391 software developers and data experts.

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!

Matt
Jul 20 '05 #1
4 3682
This 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
--
Jul 20 '05 #2
ck
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" <mh*****@yahoo.com> wrote in message
news:ea**************************@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

Jul 20 '05 #3
ck
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" <mh*****@yahoo.com> wrote in message
news:ea**************************@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

Jul 20 '05 #4
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_**********@hotmail.com> wrote in message news:<5C*****************@newssvr25.news.prodigy.c om>...
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" <mh*****@yahoo.com> wrote in message
news:ea**************************@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

Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Alan | last post by:
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have...
5
by: Alan | last post by:
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have...
11
by: Matt | last post by:
Hi everyone, still pretty new to MySQL. I was wondering if there is a way to automatically filter records based on a mysql userlogin name?? I have serveral databases that I want to combine in...
1
by: prime80 | last post by:
I'm building a project database to keep track of the various engineering projects ongoing in our department. These projects will be grouped by priority (=High, Medium, Low) and ranked within the...
1
by: Kalvin | last post by:
I am using a nested table to control different elements of my layout. The problem I have is that I create the outer table and set the size properties to what I want them to be, then, if I put...
2
by: gonzo | last post by:
So I have a project where I'm supposed to have a .txt input file of no more than ten first names, last names and birth years, and than in a menu I'm to give the user some options as to how the...
7
by: ^cypis^ vel. SQ9JTI | last post by:
Hi, i need your help. I have to prepare a homework, easy program, which will be sorting the values from txt file and writing the solution to another txt file. It has to be a bucket sort. Have...
1
by: bbatson | last post by:
Hello, I have two tables that are feeding a form/subform relationship. The first table, called "Tbl_Projects" lists a variety of projects taken on by a department. A second table called...
3
by: davenumber40 | last post by:
Iím creating a small database in Access 2003(XP) to track issues during software testing. As far as databases go, itís going to be a relatively small, short term project (No more than 20,000 records...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.