Does SQL Server contain all of the tables you're going to work with? If yes, then I would suggest using an Access Project instead of a regular Access database.

If that's not the case, then I suggest you look at www.connectionstrings.com and review the connection string for SQL Server- you might be missing a parameter.