Order of records differs

[Manaswi]

In my Delphi 5 application I have used TADOQuery through ADODB.

Following are the statements

Query1.SQL.Add('Select Id, Attr From ABC');
Query1.Prepared := True;
Query1.DisableControls;
Query1.Open;

But it is seen that, the sequece of the records fetched by the above SQL
statement is different than the sequence of the records available through
MS-Access Application. I need the field 'Attr' in the order those are stored
in the database.

After that I added 'Order by Id' at the end of the SQL Statement, but the
result is the same.

Could any body aware of this situation when the requirement is to retrive
the records from a table in the order the records are stored?

-Manaswi

 

[Allen Browne]

According to relational theory, there is no order of records in the table.
That means that if you want them in a particular order, you must use some
field that specifies the order.

In practice, Access offers the records from its own tables in primary key
order by default. But you must always provide a way to specify the desired
order, and especially so when connecting disparate products.

 

[Manaswi]

Hi Allen,

I think you have interpreted it differently. I want to get the records in
the order the records are inserted in the database. Could you provide the
actual SQL statement? I thinks RowNo or RowId like oracle may be useful. But
don't have any idea in MsAccess.

Thanks in advance.

-Manaswi

 

[Woo Mun Foong]

Hi,
Add a new autoincrement column to table ABC.
If you create your table using SQL, the syntax is as follows :-
dbs.Execute "CREATE TABLE [ABC] (" + _
"[ID] TEXT(20)," + _
"[Attr] TEXT(50)," + _
"[cntID] COUNTER)"

With cntID in, for every new record you enter, cntID will auto increment by
itself.
Then you can do SQL like this :-

Query1.SQL.Add('Select Id, Attr From ABC ORDER BY cntID ASC');
Query1.Prepared := True;
Query1.DisableControls;
Query1.Open;

and you will get records in the order the records are inserted in the
database.

 

[Manaswi]

But I'm not allowed to alter the table. Only I have the read access to the
database.

Hi,
Add a new autoincrement column to table ABC.
If you create your table using SQL, the syntax is as follows :-
dbs.Execute "CREATE TABLE [ABC] (" + _
"[ID] TEXT(20)," + _
"[Attr] TEXT(50)," + _
"[cntID] COUNTER)"

With cntID in, for every new record you enter, cntID will auto increment by
itself.
Then you can do SQL like this :-

Query1.SQL.Add('Select Id, Attr From ABC ORDER BY cntID ASC');
Query1.Prepared := True;
Query1.DisableControls;
Query1.Open;

and you will get records in the order the records are inserted in the
database.

Hi Allen,

I think you have interpreted it differently. I want to get the records in
the order the records are inserted in the database. Could you provide the
actual SQL statement? I thinks RowNo or RowId like oracle may be useful. But
don't have any idea in MsAccess.

Thanks in advance.

-Manaswi

 

[Allen Browne]

Manaswi, there is no such thing recorded as "the order they are entered into
the database", unless you provide some mechanism for recording that.

Could you provide a date/time field that records when the record was
entered?