1

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?

cswingle
  • 585
  • 1
  • 8
  • 13

1 Answers1

1

"FROM remote_odbc, (...) AS sub" makes me suspect it's the cause of the problem. Try this INSERT statement instead ...

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

Since you reported that suggestion didn't restrict the server rows sent back to Access, use a parameter query to put a single Date/Time value in the WHERE clause. The following procedure may not fit nicely in your current application context, but I'm offering it as more like a diagnostic test ... to see if we can get something to work correctly.

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

Note that code is untested since I don't have your tables and sample data to work with. However, it does compile without error from Access 2007.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thanks, but that doesn't make a difference. The `WHERE` clause still isn't part of the server-side query. It's as though the Access planner doesn't evaluate the right-side condition (be it a sub-query or something like `DMax()`) until after it's retrieved the rows from the remote table. – cswingle Sep 26 '12 at 14:57
  • That surprises me. Use ShowPlan to examine the actual query plan Access is using. http://www.techrepublic.com/article/use-microsoft-jets-showplan-to-write-more-efficient-queries/5064388 – HansUp Sep 26 '12 at 15:06
  • Hmm. Well, I tried what that web site suggested (modifying the registry, restarting Access, etc.), but no 'SHOWPLAN.OUT' appears anywhere I can search on C: (including ?CurDir, which is C:\Users\cswingle\Documents). 32-bit version of Windows 7, Access 2010. – cswingle Sep 26 '12 at 15:59
  • Nuts. The link I gave you was for Jet, but you're using the ACE db engine instead. For my Access 2007, the registry path is reported to be `HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug`, but I can't get it to work. I'm afraid I wasted your time with that suggestion. Sorry. – HansUp Sep 26 '12 at 16:40
  • Maybe this isn't the right place to ask this, but how do I turn this into something an Access user would click (like an update query) to run? – cswingle Sep 27 '12 at 21:29
  • Add a command button to a form, and use that VBA code as the button's click event procedure. – HansUp Sep 27 '12 at 21:42
  • Thanks. That's what I did. I would have preferred a method that makes the Sub look like a query (or some other object that can be clicked from the left Navigation Pane) but a form will allow me to provide some feedback to the user, so that's probably best anyway. – cswingle Sep 27 '12 at 22:11
  • You could create a function for that VBA code. Then create a macro with the function as the runcode action of the macro. Then the user could run the macro from the navigation pane. I think that could all work, but I seldom use macros. A form is my default choice. – HansUp Sep 27 '12 at 22:17