Geek asks and answers

How to update an Access table through ODBC without loading the entire table

I am trying to update a local Access 2010 table by pulling data from a remote PostgreSQL database using an ODBC connection. When I manually specify the conditions WHEREin the Access update request, I can see on the database server that a limited number of rows are passed to Access. However, when I make a dynamic condition WHEREdynamic — based on the last row in the local Access table — the whole table seems to be pulled into Access and the condition applied in memory (I suppose since it never terminates).

For example, this query only retrieves 2012 rows from the remote database, and I can see the offer WHEREon the delete 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 I really want to access the latest datetime ( dt) in the local table and get only these 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 executed on the server does not have a clause at all WHERE, which makes me think that Access retrieves the entire deleted table and then applying the clause WHERElocally. The table is too large and the Internet is too slow to be practical.

How do I rewrite my update request so that it only gets the rows that I want via the ODBC link?

+1
source share
1 answer

"FROM remote_odbc, (...) AS sub" makes me suspect that this is the cause of the problem. Try this statement INSERTinstead ...

INSERT INTO local (dt, latitude, longitude)
SELECT dt, latitude, longitude
FROM remote_odbc
WHERE remote_odbc.dt > DMax("dt", "local");

, , Access, , Date/Time WHERE. , ... , - .

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim dteLast As Date
Dim strInsert As String

dteLast = DMax("dt", "local")
Debug.Print "dteLast: " & dteLast
Set db = CurrentDb
strInsert = "PARAMETERS which_date DateTime;" & vbCrLf & _
    "INSERT INTO local (dt, latitude, longitude)" & vbCrLf & _
    "SELECT dt, latitude, longitude" & vbCrLf & _
    "FROM remote_odbc" & vbCrLf & _
    "WHERE remote_odbc.dt > which_date;"
Debug.Print "strInsert:" & vbCrLf & strInsert
Set qdf = db.CreateQueryDef("", strInsert)
qdf.Parameters("which_date") = dteLast
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing

, , . Access 2007.

+1

More articles:


All Articles