I presume our paths have crossed somewhere in the past, but haven't been able to figure out where and when.
First, lets make sure we are talking about the same thing. What version of Access is currently being used? Second, is it split into a front-end and a back-end? And while we are at it, what is the version of SQL Server, and is it using Integrated Security? And finally if it is split, are design changes of the front-end requested frequently?
If the tables are in a back-end, we typically use an upsizing tool to create those tables where it works. For those that contain NVarchar, we create the table in SQL Server with appropriate data types, and then run queries in Access to populate them after having linked the new table to the accdb file. That of course assumes you have setup and ODBC data source beforehand so you can link from Access. Once you get the data to SQL Server, you may want to consider the following:
One way to improve performance is to create Views in SQL Server. If you want to have it updateable, you will need to define an index for the view. Doing that sort of thing has couple of advantages - you avoid Access queries with more than two linked tables, and you can do things like archiving changes and logical deletes.
As I noted before, you typically want to avoid Access queries with more than 2 linked SQL Server tables. As long as you do that, the ODBC driver will convert your Access SQL to SQL Server syntax and give the result set, or a limited number of rows if the result is a large number of rows. You also typically don't want to run queries where some tables are in Access and others are in SQL Server.
Then you start looking for queries in the front-end that have performance issues. It means looking first at the queries that are saved. Then you look at forms and reports that are slow, and design queries that run well. In some cases, you may want to create a UDF, in others a pass-through query.
There's a lot more info in the references I pointed you to in my previous reply, but those are the basics. Taking that approach, we built systems with nearly 100 local users, and tables with multi-million rows, and local user forms response times typically under 1 second.
Hope that helps - I guess I should add that you need to become fairly proficient in Access forms, reports and queries, as well as VBA. And I guess I'm agreeing with Albert as far as the basic approach.
You can't see the view if you don't climb the mountain!