Access Linked Table - Key is out of Sequence.

[Rhino999]

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.

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

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

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

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]

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]

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]

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]

Thank you GinaWhipp for your response, much appreciated.

 

[gemma-the-husky]

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]

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]

I don't see a lookup field or a mention of one. Can you even have one in SQL Server?

 

[GinaWhipp]

@pbaldy Hmm, so not that I know of... missed that completely.

 

[gemma-the-husky]

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]

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]

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]

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]

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]

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]

You're welcome. Glad to clear up the mystery.

 

[gemma-the-husky]

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]

Rhino - Pat Hartman rarely corrects people about this, but Pat is a Ms., not a Mr.
Just FYI.