如何在不加载整个表的情况下通过ODBC更新Access表 [英] How to update an Access table via ODBC without loading entire table

问题描述

我正在尝试通过使用ODBC连接从远程PostgreSQL数据库中提取数据来更新本地Access 2010表.当我在Access更新查询中手动指定WHERE条件时，可以在数据库服务器上看到将有限数量的行传递给Access.但是，当我根据本地Access表的最后一行使WHERE条件动态化时-整个表似乎都被拉入Access并将该条件应用于内存中(我想是因为它从未完成) ).

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).

例如，此查询仅从远程数据库中获取2012年的行，我可以在删除服务器上看到WHERE子句:

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#;

但是我真正想要的是访问本地表中的最后一个日期时间(dt)，并且仅从远程数据库中检索那些行.

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.

这是我尝试过的:

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;

执行此操作时，在服务器上运行的查询中根本没有WHERE子句，这使我认为Access正在检索 entire 远程表，然后应用WHERE子句在本地.桌子太大了，互联网太慢了，无法实用.

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.

如何重新编写更新查询，使其仅通过ODBC链接检索我想要的行?

How can I re-write my update query so it will only retrieve the rows I want over the ODBC link?

推荐答案

"FROM remote_odbc，(...)AS sub"使我怀疑这是问题的原因.尝试使用此INSERT语句...

"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");

由于您报告建议不限制发送回Access的服务器行，因此请使用参数查询将单个Date/Time值放入WHERE子句中.以下过程可能无法很好地适合您当前的应用程序上下文，但是我将其提供为诊断测试……以查看是否可以使某些东西正常工作.

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

请注意，由于我没有要使用的表和示例数据，因此代码未经测试.但是，它确实可以从Access 2007进行编译而没有错误.

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.

这篇关于如何在不加载整个表的情况下通过ODBC更新Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！