Good questions and good advice. There are several "it depends" as might be expected.
Let me add one more level.
Linked Tables via ODBC is much like using an Access table (plus the data type differences) described above.
Even in Access (or SQL Linked Tables) my preference is not to link a form directly to a Table. Link the form to a Query.
Access since at least 2010 (maybe 2007) actually has some great internal features at the form level to interact with SQL Server. To prepare for Azure, they added features to prevent the worries of locking (in the average use)
When using a Query with a parameter(s) (Filter), ODBC actually communicates with SQL and creates the equal of a Stored Procedure.
But, let this be a warning. In the Access Query, avoid non SQL functions. For example an Access Query can use the immediate If statement. Or, Access can use public VBA Functions (UDF) in the Query. These Access only queries should be avoided in the Form's query def. Stick to standard SQL standard queries.
Using a SQL statement query, SQL will actually evaluate the request and utilize the index and other execution plans.
You indicated that you don't currently have access to SQL Server outside Linked Tables.
Many of us believe that a Table in SQL Server that becomes a Query (View) is what Access should be linked to. A View in SQL Server can be one table with added security and other administration context.
I have one form that uses a query that links a dozen tables.
This query was moved to SQL Server to create a View. My Access application used the Linked Table to this View. In a prototype stage, I probably build the query on my Access side. Later, it moves to SQL Server as a View.
Generally, you appear to be on the right track. You are asking good questions. And, you have the added benefit of receiving some excellent answers from high quality people.
Your process will be fine. And, then it can be improved as it progresses.
