MS Access Passthrough Query Update

Question

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

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

The query is simply: SELECT FullName, PointNumber FROM DNP3.CDNP3AnalogIn

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

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.

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

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

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!

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.

Answer 1

Are you saying that [DNP3_CDNP3AnalogIn Query] is server side based and that table [SCADA DB Tags] is local based? In that case you cannot use a pass-through query.

However since the tables ARE in different locations pass-though cannot touch BOTH at the same time.

You can however execute "single" server side (pass-though) in a loop. If you setup a pass-though query and SAVE it, then this code will work:

  Dim qdfPass       As DAO.QueryDef

  Dim rstLocal      As DAO.Recordset
  Dim strSQL        As String
  Dim strSQL2       As String

  Set qdfPass = CurrentDb.QueryDefs("MyPass")

  strSQL = "UPDATE [DNP3_CDNP3AnalogIn Query] " & _
           "SET [DNP3_CDNP3AnalogIn Query].[PointNumber] = 'xxxx' " & _
           "WHERE [DNP3_CDNP3AnalogInQuery].FullName = 'zzzz' "

  Set rstLocal = CurrentDb.OpenRecordset("[SCADA DB Tags]")

  Do While rstLocal.EOF = False
     strSQL2 = Replace(strSQL, "xxxx", rstLocal!PointNumber)
     strSQL2 = Replace(strSQL2, "zzzz", rstLocal!FullName)
     qdfPass.SQL = strSQL2
     qdfPass.Execute
     rstLocal.MoveNext
  Loop

  rstLocal.Close

Answer 2

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

Notes:

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.