Tables not sorting by primary key after conversion to Azure SQL (1 Viewer)

[MichaelSmith]

I've been building a database which will be used on a large archaeology project to store and analyse data on shipwrecks.

I built the database in Access 2013 and am hosting the backend on a Windows Azure SQL Database. I converted the database to SQL Azure using SSMA for Access.

I currently have 615 rows in most of my tables, the problem I'm encountering is that the tables are not being sorted by their primary key, in one case they're being sorted by an index which doesn't allow duplicates except for NULL and in another it seems to be using the number at the start of an address line.

It's not the end of the world, honestly it's just an aesthetics issue BUT is there any way I can make my Access Forms in the front end automatically sort by Primary Key without having to force the user to right click and choose sort smallest to largest?

EDIT: Please move this to the forms sub-forum if it's more appropriate.

 

[MichaelSmith]

Quick update to this:

The weirdest part for me is that the tables display in the correct order (i.e. sorted by primary key) when I look at them in the Windows Azure management portal.

 

[namliam]

when looking at tables without any strict sort on it the order is by its nature random.... Likely the management portal you use is doing the explicit sort for you without your knowledge...

If you need the order enforce it in a query.

 

[ButtonMoon]

It's not the end of the world, honestly it's just an aesthetics issue BUT is there any way I can make my Access Forms in the front end automatically sort by Primary Key without having to force the user to right click and choose sort smallest to largest?

You just need to put ORDER BY in your queries.

There is no "default" sort order and the only way to be sure is to specify the columns in the ORDER BY clause. If you don't then SQL Server attempts to choose the fastest access path to satisfy the unordered query. That may mean the data is returned in allocation order or sorted by another index, not always the primary key.

 

[Mile-O]

I currently have 615 rows in most of my tables

Away from the sorting issue for a moment, why do most of your tables have 615 rows in them? Sounds like you've almost created a table for each shipwreck.

 

[Rx_]

This answer is just based on attending a couple of presentations, not from hands-on.
The requirement of the Access Web App conversion requires a surrogate key for linking. An auto-number field is required for the linking field.
So, a field with N,S,E,W used in a list box must now require an associated number field.
There is a lot of stuff out on the MS web site about the design of the web app. Just my opinion that you can't just make the jump with out planning.
My concerns were along the line of the old "replication". There is a lot of pre-planning for the Database structure itself before making the conversion.
"There is no conversion from Access to a Web app, it is a complete conversion " according to a post by a MVP.