By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

How Access reads data stored in SQL Server

P: 19
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:
Expand|Select|Wrap|Line Numbers
  1. ID      Serial#       Date
  2. ---      -------        -------
  3. 1        A123        08/21/09
  4. 2        S901        08/24/09
  5. 3        F789        08/23/09
  6. 4        D456        08/22/09
  7.  
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.
Aug 25 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Aug 26 '09 #2

P: 19
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.
Aug 26 '09 #3

ck9663
Expert 2.5K+
P: 2,878
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
Aug 26 '09 #4

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

Thanks for the info.
Aug 26 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.