Why is Access so slow?

Lateralusx777 

Ouch. That's my mistake. I forgot to ask about the size of the recordsets involved. 

Loading that many records will be slow, IF you load them all at once into a form's recordsource.

I think that explains it in a way. 

When you first open a form bound to one of those tables, Access loads only a small percentage of the whole, enough to show an initial display. Then it begins loading the rest in the background. If you ask for all of them, by moving to the "last" record in the recordsource, it has to retrieve hundreds of thousands of records to do that. Improperly, or inadequately, indexed tables are even more of a problem in that situation. And forms with lots of combo boxes or list boxes or subforms are also problematic in that regard.

So, the solution would be to avoid ever loading all of the records in a table unless you have some specific reason, such as an aggregate query that needs to display the entire history of the transactions in a 430,000 record table. 

Start with a single record. Offer a way to select a different record when needed and load only that record. And so on.

This kind of problem also reflects the fact that an initial stage relational database application may contain only a small number of records, and hence the problem of mass loading is not obvious. Over time the number of records in the tables grows and the problem makes itself apparent.

Moving the data to SQL Server should help in allowing the Server to manage those huge recordsets, but I'm surprised that it completely disappears.

Again, my apologies for not thinking through all of the possibilities, including this one.