Short answer: Yes.
Long answer: If you are returning smaller data sets, yes. You'll also need to enable specific flags and settings in the pgodbc driver as Access will barf from what is fed back to it from the server:
KSQO (Keyset Query Optimization) should be on. Yes, I know the docs say it's not required, but what's happening is that MSAccess is passing the server some rather knarly looking SQL. This just de-clutters the SQL statement a wee bit. Don't believe me? Turn on Logging for ODBC, send a single query, turn off the log, plow through several megs of chaff, and 90% of the way down you'll see a nasty looking SQL statement that was generated and passed. No, it's not the ODBC driver, it's Access doing that.
Use Declare/Fetch should be on if you plan to interactively look at a large data set. This will cause the driver to fetch smaller chunks of your result set, instead of dumping one monster load into it.
Text as LongVarChar - you may wish to test this. I suspect you will want it "on".
Unknown Sizes should be set to Maximum.
Max Varchar is recommended to be at 254 or less, although you can make it more.
Extra Opts - set to 0x6 (Fake MS SQL Server + Reply in ANSI not Unicode).
(all of these can be found at http://psqlodbc.projects.postgresql.org/config.html)
Keep in mind that large data sets will cause you pain, despite the fact that the data returned from the server is generally in a compact binary format.
Check with your CoLo provider, they may be blocking access to port 1433. Many CoLos started doing this after the SQL Slammer went around a while back.
It's also recommended that you not try and access SQL Server's directly over the internet. Instead setup a VPN between the CoLo and your office and access the servers over this secure connection instead of over the public internet.
Best Answer
You can eliminate the need for the local DSN on each machine by using a bit of code in the Access database. You do of course still need the appropriate ODBC driver installed but I expect the one you need is probably standard on Windows anyway.
A local DSN will be required to initially create the database.
The following in what I use for dynamically relinking to a MySQL database, so you will need to edit it accordingly. The code is called from the AutoExec macro or can be run manually or from a form.
Note that this is not my code but I've been using it for a very long time and don't recall where I originally got it. All I've done is edit it to suit my requirements.
End Function