How Access reads data stored in SQL Server

[dbrother]

I guess my real question is how is data stored in SQL Server... I have a linked table in Access that I'm using ADO Recordsets in VBA to move to the last record. The code works fine, but I'm not getting the "correct" results.

Sample table:

ID      Serial#       Date
---      -------        -------
1        A123        08/21/09
2        S901        08/24/09
3        F789        08/23/09
4        D456        08/22/09

No matter how I "sort" the data in Access through the linked table in SQL Server, sample record #2 is always the last record and I believe it's sorting by the serial# text (since S would be the greatest).

Is there a way I can fix this in SQL Server to sort by the ID instead of the serial number? Using Select MAXID or Dlookup functions will not help me since they cannot accept EOF or BOF arguments.

Any help appreciated.
Thanks in advance.

[ck9663]

If you mean the last record inserted, you're going to need a field that sort your record incrementally ie, IDENTITY column or an INSERTED date column. You can do a max on either of those fields.

Happy Coding!!!

--- CK

[dbrother]

I have the ID column which is the unique identifier for each record. What I mean to say is how do I get SQL Server to actually store the data by the ID field?

No matter how I sort the data in the linked table in Access, every time I run the query and halt the code at the first position (last record), sample record with ID # 2 is always the last record. This is obviously due to the fact that SQL server is "storing" or "sorting" the data by the serial number.

My question is why does SQL Server do this or how can I fix it? I would imagine that an ID field would be the default storing method and would consider the last record to be the MAX ID.

Any other insight appreciated.

[ck9663]

Create a PRIMARY KEY constraint on your ID field. During the retrieve do an ORDER BY. By default, sql server will return your table sorted based on your primary key or clustered index. Also, a PRIMARY KEY ensures uniqueness and sorting but the last record on the result set does not mean it's the last record inserted.

Good luck!!

--- CK

[dbrother]

Ahh, that may be the problem then... I have an index set on that serial number field for faster retrieval.

Thanks for the info.