So I get how to connect Access to SQL Server, and I understand that the tables have to be linked, but my problem is security. If I link the tables from SQL Server into Access, then anyone who comes into the Access forms to enter data can ultimately get at the tables via the link can't they? I know that newer Access versions have no user security, but is there any way to restrict what data they see in some of the tables, or do I need to do that on the back end in SQL Server Express?
The easiest way to do that is on the back end. Define a user that has only the access you want and then update the ODBC DSN that Access uses to that user account.
You just need to make sure that the credentials used to set up the linked tables only have the bare minimum permissions they need to do the things the forms do.
You will always have to deal with the fact that if you give someone the keys to the door, they will be able to get into your house even when you are not home.
So you need to make sure that if your forms implement any kind of constraints, business logic, etc. that this is repeated in the database, so that it will still be enforced even if users bypass the forms and interact with the tables directly.