MS Access直通查询更新 [英] MS Access Passthrough Query Update

问题描述

我正在尝试使用对我无法控制的ODBC服务器的MS Access对Passthrough查询进行更新.我必须使用Passthrough的原因是我正在访问的记录具有超过255个字段(如果可以的话，我将使用链接表).

I am trying to make an Update to a Passthrough query using MS Access to an ODBC server that I have no control over. The reason I have to use a Passthrough is that the records I am accessing have more than 255 fields (I would use a linked table if I could).

我一直在使用此资源通过Passthrough获取数据( http: //www.techonthenet.com/access/tutorials/passthrough/basics09.php )

I've been using this resource to get the data using Passthrough (http://www.techonthenet.com/access/tutorials/passthrough/basics09.php)

查询很简单:SELECT FullName, PointNumber FROM DNP3.CDNP3AnalogIn

ODBC Connect Str为:ODBC;DSN=SCX6_DB;LOCATION=Main;UID=admin;PWD=password;LOCALTIME=False;

The ODBC Connect Str is: ODBC;DSN=SCX6_DB;LOCATION=Main;UID=admin;PWD=password;LOCALTIME=False;

现在在Access数据库中，我有一个表(SCADA DB标记)，该表具有与字段(FullName，PointNumber)相同的名称，并且我想使用Update Passthrough查询来更新ODBC数据库中的字段，但是我不确定这该怎么做.

Now inside an Access Database I have a table (SCADA DB Tags) with same name for the Fields (FullName, PointNumber), and I want to update the fields inside the ODBC Database using an Update Passthrough query, but I am unsure how to do this.

我将之前的查询另存为DNP3_CDNP3AnalogIn查询，并尝试进行新的查询:

I saved the previous Query as DNP3_CDNP3AnalogIn Query, and tried to make a new Query:

UPDATE [DNP3_CDNP3AnalogIn Query] INNER JOIN [SCADA DB Tags] ON 
[DNP3_CDNP3AnalogInQuery].FullName = [SCADA DB Tags].FullName 
SET [DNP3_CDNP3AnalogIn Query].[PointNumber] = [SCADA DB Tags].[PointNumber];

但是我从Access中收到一个错误:Operation must use an updateable query.

But I get an error from Access: Operation must use an updateable query.

我知道有某种方法可以做到这一点，但是我似乎找不到一个示例(我可能没有在搜索正确的短语). Microsoft页面( http://technet.microsoft. com/zh-CN/library/bb188204％28v = sql.90％29.aspx )说:There is, however, one important limitation: the results returned by SQL pass-through queries are always read-only. If you want to enable users to perform updates based on the data retrieved, you must write code to handle this.不幸的是，它没有给出示例！

I know there is someway to do this but I can't seem to find an example (I might not be googling the correct phrase). Microsoft page (http://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx) says: There is, however, one important limitation: the results returned by SQL pass-through queries are always read-only. If you want to enable users to perform updates based on the data retrieved, you must write code to handle this. Unfortunately it doesn't give an example to do it!

任何人都可以给我一个解决方案，如果需要，我可以使用VBA吗?如果需要，我还可以提供更多背景信息.不幸的是，我不是Access方面的专家，我只是想提出一个可以为我节省一些时间的自动化解决方案.

Can anyone give me a solution, I can use VBA if required? I can also give more background if required. Unfortunately I'm not an expert in Access, I'm just trying to come up with an automated solution that could save me some time.

推荐答案

当他们说如果要使用户能够基于从[直通查询]中检索到的数据执行更新，则必须将代码编写为处理"，它们可能表示类似这样的内容:

When they said that "If you want to enable users to perform updates based on the data retrieved [from a pass-through query], you must write code to handle this" they probably meant something like this:

Option Compare Database
Option Explicit

Public Sub UpdateSqlServer()
    Dim cdb As DAO.Database, rst As DAO.Recordset
    Dim con As Object  ' ADODB.Connection
    Dim cmd As Object  ' ADODB.Command
    Const adParamInput = 1
    Const adInteger = 3
    Const adVarWChar = 202

    Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset( _
            "SELECT " & _
                "[SCADA DB Tags].FullName, " & _
                "[SCADA DB Tags].PointNumber " & _
            "FROM " & _
                "[DNP3_CDNP3AnalogIn Query] " & _
                "INNER JOIN " & _
                "[SCADA DB Tags] " & _
                    "ON [DNP3_CDNP3AnalogIn Query].FullName = [SCADA DB Tags].FullName", _
            dbOpenSnapshot)

    Set con = CreateObject("ADODB.Connection")
    con.Open "DSN=SCX6_DB;"
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = con
    cmd.CommandText = _
            "UPDATE DNP3.CDNP3AnalogIn SET " & _
                "PointNumber=? " & _
            "WHERE FullName=?"
    cmd.Parameters.Append cmd.CreateParameter("?", adInteger, adParamInput)  ' PointNumber
    cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255)  ' FullName
    cmd.Prepared = True

    Do Until rst.EOF
        cmd.Parameters(0).Value = rst!PointNumber
        cmd.Parameters(1).Value = rst!FullName
        cmd.Execute
        rst.MoveNext
    Loop
    Set cmd = Nothing
    con.Close
    Set con = Nothing
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
End Sub

注意:

1. 该代码使用您现有的ODBC DNS.
2. 它使用Prepared语句来执行更新，从而提高效率并防止与SQL注入相关的故障.
3. 源Recordset对直通查询执行INNER JOIN，以确保代码仅尝试更新服务器上实际存在 的服务器上的行.

1. The code uses your existing ODBC DNS.
2. It uses a Prepared Statement to perform the updates, increasing efficiency and protecting against failures related to SQL Injection.
3. The source Recordset performs an INNER JOIN on the pass-through query to ensure that the code only tries to update rows on the server that actually exist on the server.

这篇关于MS Access直通查询更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！