Update local table with linked table (but without primary key)

Question

I want to read a linked table and update the local one.

Importing new entries works.
When I try to update an existing one it throws an exception

runtime error 3073

Sub UpdateBLPNR()
With CurrentDb
    Set tdf = .CreateTableDef("ext_BEL_PLZ")
    tdf.Connect = "ODBC;DSN=EasyProd PPS;DataDirectory=PATH;SERVER=NotTheServer;Compression= ;DefaultType=FoxPro;Rows=False;Language=OEM;AdvantageLocking=ON;Locking=Record;MemoBlockSize=64;MaxTableCloseCache=5;ServerTypes=6;TrimTrailingSpaces=False;EncryptionType=RC4;FIPS=False"
    tdf.SourceTableName = "BEL_PLZ"
    .TableDefs.Append tdf
    .TableDefs.Refresh
End With

Dim SQLUpdate As String
Dim SQLInsert As String
SQLUpdate = "UPDATE BEL_PLZ " & _
            "INNER JOIN ext_BEL_PLZ " & _
            "ON(BEL_PLZ.NR = ext_BEL_PLZ.NR) " & _
            "SET BEL_PLZ.BEZ = ext_BEL_PLZ.BEZ "
SQLInsert = "INSERT INTO BEL_PLZ (NR,BEZ) " & _
            "SELECT NR,BEZ FROM ext_BEL_PLZ t " & _
            "WHERE NOT EXISTS(SELECT 1 FROM BEL_PLZ s " & _
            "WHERE t.NR = s.NR) "
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLUpdate)
DoCmd.RunSQL (SQLInsert)
DoCmd.SetWarnings True

DoCmd.DeleteObject acTable, "ext_BEL_PLZ"
End Sub

Already figured out that Access might have some problems using a linked table to update a local one but I can't figure out a workaround.
(SQLInsert is working, SQLUpdate is not)

This is my final and working solution (thanks to ComputerVersteher)

Sub UpdateBLPNR()
'Define Variables
Dim SQLUpdate As String
Dim SQLInsert As String
Dim qdf As DAO.QueryDef
'Create temporary table and update entries
With CurrentDb
    Set tdf = .CreateTableDef("ext_BEL_PLZ")
    tdf.Connect = "ODBC;DSN=EasyProd PPS;DataDirectory=PATH;SERVER=NotTheServer;Compression= ;DefaultType=FoxPro;Rows=False;Language=OEM;AdvantageLocking=ON;Locking=Record;MemoBlockSize=64;MaxTableCloseCache=5;ServerTypes=6;TrimTrailingSpaces=False;EncryptionType=RC4;FIPS=False"
    tdf.SourceTableName = "BEL_PLZ"
    .TableDefs.Append tdf
    .TableDefs.Refresh
     With .OpenRecordset("SELECT ext_BEL_PLZ.NR, ext_BEL_PLZ.BEZ " & _
                        "FROM ext_BEL_PLZ INNER JOIN BEL_PLZ ON BEL_PLZ.NR = ext_BEL_PLZ.NR", dbOpenSnapshot)
        Set qdf = .Parent.CreateQueryDef("")
        Do Until .EOF
             qdf.sql = "PARAMETERS paraBEZ Text ( 255 ), paraNr Text ( 255 );" & _
                       "Update BEL_PLZ Set BEL_PLZ.BEZ = [paraBEZ] " & _
                       "Where BEL_PLZ.NR = [paraNr]"
             qdf.Parameters("paraBez") = .Fields("BEZ").Value
             qdf.Parameters("paraNr") = .Fields("NR").Value
             qdf.Execute dbFailOnError
             .MoveNext
        Loop
    End With
End With
'Run SQL Query (Insert)
SQLInsert = "INSERT INTO BEL_PLZ (NR,BEZ) " & _
            "SELECT NR,BEZ FROM ext_BEL_PLZ t " & _
            "WHERE NOT EXISTS(SELECT 1 FROM BEL_PLZ s " & _
            "WHERE t.NR = s.NR) "
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLInsert)
DoCmd.SetWarnings True
'Drop temporary table
DoCmd.DeleteObject acTable, "ext_BEL_PLZ"
End Sub

Then remove the join in update! That causes the read only. Use a sub select. — ComputerVersteher, Jun 19 '19 at 11:26
How can you exchange the inner join for a subselect way? (Would like to stay with the linked table) — Moritz, Jun 19 '19 at 11:44
Just try, I am noz sure. But at least you can create a recordset and update with its values. — ComputerVersteher, Jun 19 '19 at 11:45
OK, maybe not https://stackoverflow.com/questions/1375207/how-do-i-perform-update-query-with-subquery-in-access. but recordset should do it. — ComputerVersteher, Jun 19 '19 at 11:51
Answer completed. Maybe parameter type needs to be changed to your needs. — ComputerVersteher, Jun 19 '19 at 13:11
Any reason, not to use`.Execute`for insert query too?`DoCmd.SetWarnings False` may be surprising ;) — ComputerVersteher, Jun 19 '19 at 13:56
just put it together "quick and dirty" ... so yeah, might be an improvement to move that stuff inside the "With currentDB" loop too — Moritz, Jun 19 '19 at 14:01
Avoid`DoCmd`especially with`SetWarnings False` ,ignorance is not bliss;), Use [On Error Resume Next](https://rubberduckvba.wordpress.com/2019/05/22/on-error-resume-next/) and Err object to decide if warning is important or not. — ComputerVersteher, Jun 19 '19 at 14:19

score 1 · Answer 1 · answered Jun 19 '19 at 10:28

1

You can't do that.

Linked tables on other data sources than Access itself require a primary key to support updates.

When linking through the GUI, Access does allow you to specify an alternate key that uniquely identifies rows if there is no primary key, but if there is one that should be your primary key.

answered Jun 19 '19 at 10:28

Erik A

31,639
12
42
67

As far as I know there isn't a primary key within the linked table (it is a really old dbf database but unfortunately we can't switch to a SQL one before 2020). My idea was to link the table using VBA, copy / update a local table and unlink the table. I have to avoid any changes to the linked table as it will be accessed and changed by another department. – Moritz Jun 19 '19 at 10:40
1

Then just start by making a full copy of the table, don't execute an update with a linked table involved. Simple as that. – Erik A Jun 19 '19 at 10:41
correct, I've got a local table, import 2 columns from the linked table and either update or create the entries inside the local table and disconnect the linked table for safety reasons – Moritz Jun 19 '19 at 11:14
If the table has only these two columns, an alternate approach might be: use a `DELETE` query to delete already occurring rows, then use the insert query to insert all rows. If there are additional columns you want to leave unaffected, that's not an option, of course. – Erik A Jun 19 '19 at 11:18
1

Btw, I opened up an issue about this [on Access UserVoice](https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/37955803). It's a common and restrictive limitation of the Access database engine, and often generates questions. – Erik A Jun 19 '19 at 11:28

ComputerVersteher · Accepted Answer · 2019-06-19T13:08:04.110

1

Create a recordset from read only table to get values.

Dim qdf As DAO.QueryDef
With CurrentDb
    With .OpenRecordset("SELECT ext_BEL_PLZ.NR, ext_BEL_PLZ.BEZ " & _
                        "FROM ext_BEL_PLZ INNER JOIN BEL_PLZ ON BEL_PLZ.NR = ext_BEL_PLZ.NR", dbOpenSnapshot)
        Set qdf = .Parent.CreateQueryDef(vbNullString)
        qdf.SQL = "PARAMETERS paraBEZ Text ( 255 ), paraNr Long;" & _
                       "Update BEL_PLZ Set BEL_PLZ.BEZ = [paraBEZ] " & _
                       "Where BEL_PLZ.NR = [paraNr]"
        Do Until .EOF
             qdf.Parameters("paraBez") = .Fields("BEZ").Value
             qdf.Parameters("paraNr") = .Fields("NR").Value
             qdf.Execute dbFailOnError
             .MoveNext
        Loop
    End With
End With

edited Jun 19 '19 at 13:08

answered Jun 19 '19 at 12:30

ComputerVersteher

2,638
1
10
20

Parameter types match tables fields? BEL_PLZ.Bez has less or equal 255 chars? – ComputerVersteher Jun 19 '19 at 13:46
yeah, "Bez" has always less than 255 chars due some restrictions in our planing software – Moritz Jun 19 '19 at 13:51

Update local table with linked table (but without primary key)

2 Answers2

Linked