Access Linked Table - Key is out of Sequence. (1 Viewer)

Rhino999

Registered User.
Local time
Today, 08:18
Joined
Feb 8, 2010
Messages
62
I’m not sure if I’m posting this in the right place.

I Migrated my Access 2016 Application to SQL Server Express using Microsoft SQL Server Migration Assistant (SSMA) for Access, Version 8.16. for the first time.
It migrated my successfully. No errors.
I linked the tables and the Application functions.

The one weird thing that I noticed, is that the Records or Rows of the Linked Customer Table, tblCustomer, with a CustomerID Key and Data Type int, Indexable are out of sequence.

1611190021702.png



The Original Table, which is now labeled SSMA$tblCustomer$Local is NOT out of Sequence.

1611190066529.png



In my Access application, I have never seen this before until I Migrated the Application to SQL.

In both the SSMA (SQL Server Migration Assistant) seen here
1611190145570.png

and in SSMS (SQL Server Management Studio) v18.8 , seen here, the Record sequences are correct.
1611190169454.png


Additionally, my Customer Form displays the records out of sequence as well.

If someone can please point me to where or what the problem could be, I would greatly appreciate it.

So far, I have not been able to find any information on this problem.

Thanks in advance for your help!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:18
Joined
Aug 30, 2003
Messages
36,118
It's not really a problem, rather a matter of perception. A table should be thought of as a bucket of data, with no inherent sort. You sort it when you pull it out in forms/reports. That said, if you sort it in the linked table, it should hold the sort.
 

Rhino999

Registered User.
Local time
Today, 08:18
Joined
Feb 8, 2010
Messages
62
Thanks so much for your reply.

I understand your answer with regard to a “bucket of data”.

So, that means that every time a record is added by the Customer Form, I’ll need to sort the Table before the records are displayed by the form again.

Would you suggest Sorting the table with VBA Code in the Onload Event of the Form, or is there another way that I can do, like using the “Ordered By” in the Property Sheet of the Form?

Thanks so much for helping me!
 

GinaWhipp

AWF VIP
Local time
Today, 08:18
Joined
Jun 21, 2011
Messages
5,901
Hmm, use a Query as the Recordsource and then you can sort on the Customer ID and never have to think about it again.
 

Rhino999

Registered User.
Local time
Today, 08:18
Joined
Feb 8, 2010
Messages
62
Thank you GinaWhipp for your response, much appreciated.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Sep 12, 2006
Messages
15,613
Yes, I second @GinaWhipp. A query is generally better than a table for a record source. You can include related information from other tables, especially where the table includes (numeric) pointers to the linked data, so the user sees the data, rather than the possibly uninformative numerical index. You can also sort and filter the query. It's quite rare you actually want to see an entire table.

Note also that it is better in my opinion not to have the lookup built into the table design. It might be a personal thing, but I get very irritated seeing a combo box in a table, when I really do want to see the actual stored data.
 

GinaWhipp

AWF VIP
Local time
Today, 08:18
Joined
Jun 21, 2011
Messages
5,901
And I second @gemma-the-husky when it comes to combo boxes in tables. No End User should have any interaction with the table so no need to put combo boxes at that level.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:18
Joined
Aug 30, 2003
Messages
36,118
I don't see a lookup field or a mention of one. Can you even have one in SQL Server?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Sep 12, 2006
Messages
15,613
I missed it as well. It was more to do with using queries instead of tables as recordsources, and the issues that can be caused by assuming you always want to see the referenced value.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:18
Joined
Jan 20, 2009
Messages
12,849
I don't see a lookup field or a mention of one. Can you even have one in SQL Server?
Yes, inside Access. Even though Access tells you that the design of a linked table cannot be modified, it still allows you to change several properties including those in the Lookup even on an ODBC linked table. They then display in Access with the lookup.
 

Isaac

Lifelong Learner
Local time
Today, 05:18
Joined
Mar 14, 2017
Messages
8,738
You're confusing Forms with Tables and Queries. Tables have no sort, nor should you expect them to, nor would you sort them!
A form is designed, presumably by you or someone else at your firm, and YOU design the form with the recordsource (including a sort) that you wish.
It is not normal to try to 'sort tables' - that's why you found no information on it.
 

Rhino999

Registered User.
Local time
Today, 08:18
Joined
Feb 8, 2010
Messages
62
I would like to thank everyone who took time to help me find a solution to my Linked SQL Table being out of sequence.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 19, 2002
Messages
42,970
Welcome to the world of relational databases:) Access tends to fool people because whenever you compact the database, Access sorts all the tables in primary key sequence as it rewrites them so people get the impression that it is normal to open a table in ds view and have it appear in PK sequence. It is NOT normal. It is just Access being friendly. Tables and queries are unordered sets and sequence can NEVER be relied on unless you use a query and specifically sort the data on a unique value. If you sort on a non-unique value, you might see rowA sometimes occur after rowB and sometimes before it.
 

Rhino999

Registered User.
Local time
Today, 08:18
Joined
Feb 8, 2010
Messages
62
Mr. Hartman,
sorry for the delay in my response.
I like to thank you for your explanation of PK's sequencing in relational databases.
I'll make the necessary changes in my Application to accommodate for PK sequencing.

Thanks again for you time, help and insight!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 19, 2002
Messages
42,970
You're welcome. Glad to clear up the mystery.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Sep 12, 2006
Messages
15,613
Note that you will see the same if you import a spreadsheet tab to an access table. The rows might be in the same order as the spreadsheet, but there is no guarantee, so don't rely on it. If you need to sort the access table into the same order as the spreadsheet then you need a numerical sort order column in the spreadsheet, so you can sort the table into that order.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:18
Joined
Feb 28, 2001
Messages
26,996
Rhino - Pat Hartman rarely corrects people about this, but Pat is a Ms., not a Mr.
Just FYI.
 

Users who are viewing this thread

Top Bottom