I'm trying to update a local Access 2010 table by pulling data from a remote PostgreSQL database using an ODBC connection. When I manually specify WHERE
conditions in the Access update query, I can see on the database server that a limited number of rows are being passed to Access. However, when I make the WHERE
condition dynamic---based on the last row in the local Access table---the entire table appears to be pulled into Access and the condition applied in memory (I presume, since it never completes).
For example, this query only fetches the 2012 rows from from the remote database, and I can see the WHERE
clause on the remove server:
INSERT INTO local (dt, latitude, longitude)
SELECT dt, latitude, longitude
FROM remote_odbc
WHERE remote_odbc.dt > #2011-12-31 23:59:59#;
But what I really want is for access to look at the last datetime (dt
) in the local table and only retrieve those rows from the remote database.
This is what I tried:
INSERT INTO local (dt, latitude, longitude)
SELECT dt, latitude, longitude
FROM remote_odbc, (SELECT max(dt) AS max_dt FROM local) AS sub
WHERE remote_odbc.dt > max_dt;
When I do this, the query being run on the server has no WHERE
clause in it at all, which makes me think that Access is retrieving the entire remote table, and then applying the WHERE
clause locally. The table is too large and the Internet is too slow for this to be practical.
How can I re-write my update query so it will only retrieve the rows I want over the ODBC link?