Access SQL - UPDATE ODBC table with JOIN without linking the ODBC table in Access

Question

Running MS Access 2010 and SQL Server 2008 SP1

I need to figure out how to successfully run DoCmd.RunSQL on an UPDATE query where the table being updated is an un-linked ODBC SQL Server table.

Here is what I tried, but I get ERROR 3135: SYNTAX ERROR IN JOIN OPERATION. I know this is correct form for an UPDATE with a JOIN in Access:

unsuccessful variation 1:

UPDATE [ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl]
INNER JOIN [myTbl]      
ON ([ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl].[CDL] = [myTbl].[CDL])      
    AND ([ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl].[Archive ID] = [myTbl].[Archive ID])      
    AND ([ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl].[TimeInserted] = [myTbl].[TimeInserted])      
    and ([ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl].[Exported] = [myTbl].[Exported])  
SET [ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl].[RecordStatus] = [myTbl].[RecordStatus]  
WHERE Nz([ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl].[RecordStatus],'0') <> [myTbl].[RecordStatus]

unsuccessful variation 2:

UPDATE [ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl]  as a
INNER JOIN [myTbl] as b   
    ON a.[CDL] = b.[CDL]      
    AND a.[Archive ID] = b.[Archive ID]      
    AND a.[TimeInserted] = b.[TimeInserted]      
    and a.[Exported] = b.[Exported] 
SET a.[RecordStatus] = b.[RecordStatus]  
WHERE Nz(a.[RecordStatus],'0') <> b.[RecordStatus]

I am able to sucessfully SELECT records with similar query conditions:

 SELECT a.*
 FROM [ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl]  as a
 INNER JOIN [myTbl] as b    
     ON a.[CDL] = b.[CDL]      
     AND a.[Archive ID] = b.[Archive ID]      
     AND a.[TimeInserted] = b.[TimeInserted]      
     and a.[Exported] = b.[Exported] 
 WHERE Nz(a.[RecordStatus],'0') <> b.[RecordStatus]

I am able to successfully INSERT records using the schema/table convention being used in the UPDATE queries:

INSERT INTO [ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl] 
SELECT a.* FROM  [myTbl] AS a WHERE a.[RecordStatus] IS NULL;

EDIT - for those curious, here is how I am executing the SQL statement from VBA:

Function fn_UpdateSQLServer()

    Dim sTblNm As String
    Dim sTypExprt As String
    Dim sCnxnStr As String, vStTime As Variant
    Dim db As Database, tbldef As DAO.TableDef

    On Error GoTo ExportTbls_Error

    sTypExprt = "ODBC Database" 'Export Type
    sCnxnStr = "ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword" 'Create the connection string

    vStTime = Timer
    Application.Echo False, "Visual Basic code is executing."

    Set db = CurrentDb()

    'need a reference to Microsoft DAO 3.x library
    DoCmd.SetWarnings False
    For Each tbldef In db.TableDefs

        ' UPDATE
        If (tbldef.Name = "myTbl") Then
            'Debug.Print tbldef.Name
            sTblNm = tbldef.Name
            sSQL2 = " UPDATE [ODBC;DSN=MyDSNname;UID=MyUserID;PWD=MyPassword].[myTbl]  as a " _
                    & " INNER JOIN [myTbl] as b " _
                    & "     ON a.[CDL] = b.[CDL] " _
                    & "     AND a.[Archive ID] = b.[Archive ID] " _
                    & "     AND a.[TimeInserted] = b.[TimeInserted] " _
                    & "     and a.[Exported] = b.[Exported] " _
                    & " SET a.[RecordStatus] = b.[RecordStatus] " _
                    & " WHERE Nz(a.[RecordStatus],'0') <> b.[RecordStatus] "
            Debug.Print sSQL2
            DoCmd.RunSQL (sSQL2)
        End If

    Next tbldef
    DoCmd.SetWarnings True

    MsgBox ("Done! Time taken= " & Timer & vStTime)

    On Error GoTo 0

SmoothExit_ExportTbls:
    Set db = Nothing
    Application.Echo True
    Exit Function

ExportTbls_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
    Resume SmoothExit_ExportTbls

End Function

Does your second failed variation have a SET statement? Or did you just forget it in typing it up on SO? — VBlades, Jul 21 '14 at 20:40
Hi, yes, I updated it to include the `SET`. Typo on my part. Behavior has not changed. — sion_corn, Jul 21 '14 at 21:12
I've played with this a bit, and in theory, should be fine. Maybe dump the SQL into a query pane, make sure the UPDATE warnings are on, then walk back each JOIN condition? I think it's actually something about the JOIN (thought it was a red herring at first) and not the weird table reference. — VBlades, Jul 21 '14 at 22:43
@VBlades answer posted below. Thanks for helping me troubleshoot. — sion_corn, Jul 22 '14 at 16:36
Glad you figured it out. When you don't have a primary key, it would usually give a 'Must use updateable query' exception (or something like taht) as opposed to pointing towards the JOIN. Glad you solved it, though! — VBlades, Jul 22 '14 at 21:06
Right, it was actually giving me the error you mentioned when I executed the VBA. I was also testing the update statement in the Access 'build query' window, and was getting the error mentioned in the OP. Was too lazy to make the distinction, although I realize that probably worked against me... — sion_corn, Jul 23 '14 at 20:07

score 0 · Accepted Answer · answered Jul 22 '14 at 16:35

0

Ok - I figured it out. The update queries shown in my OP were written correctly; however, they only work if the SQL Server table has a primary key. As soon as I defined one, I was able to execute the VBA/SQL successfully.

answered Jul 22 '14 at 16:35

sion_corn

3,043
8
39
65

Access SQL - UPDATE ODBC table with JOIN without linking the ODBC table in Access

1 Answers1