Use the SQL Client configuration app ("Client Network Utility" on older versions of SQL), set up a named service (aka "alias") that uses tcpip to connect to the server. Set the Access app to use the named service/alias you create.
In more recent versions of SQL, this is done via the "SQL Server Configuration Manager" tool, in the "SQL Native Client Configuration" area, where you can create an alias.
Another approach that works for ADO and I believe ODBC: In your connection string, specify server and port. In SQL speak this is done with a comma:
<sql server name or ip>,<port>
e.g.
mysqlserver2008instance,1433