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.
What are you using for a pptp client on Ubuntu? Have you enabled the ppp option to set the tunnel as the default gateway in the pptp client? When the VPN is enabled on the Ubuntu server do it still need to be usable from the network that is local to the Ubuntu server? If so you won't want to set the VPN as a default gateway and you will probably want to statically define a route.
However, I cannot ping other machines
on the local LAN.
Which local lan are you talking about? Are you talking about the lan local to the Ubuntu server, or the lan local to the VPN server?
If you need clients that are on the same network as the VPN server to be able to access other hosts on the Ubuntu server's lan you will need to add a route on both sides. You will also need to have address space that doesn't conflict between the two networks.
Best Answer
Might be simple enough as just scripting the database dumping and then transferring the file to the other database server and then restoring:
The 'pg_dump' utility found with the PostgreSQL Server can be utilized by Database Administrators for taking dumps or backups of databases into a file. The syntax of pg_dump is :
pg_dump databasename -f outputfile -i -x -O -R -S username
I am not a fan completely of this script http://www.wisdombay.com/articles/pg_dmp.sh but it should work just fine for you.
The reason for my caution is - because it houses your login/passwords
Now simply scp to the backup server and run a cron job to import the databases.
If stuck - let me know.