CODEWITHSUNDEEP
vbams-access
Moritz
Moritz

Reputation: 389

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

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

Upvotes: 2

Views: 532

Answers (2)

ComputerVersteher
ComputerVersteher

Reputation: 2696

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

Upvotes: 1

Erik A
Erik A

Reputation: 32642

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.

Upvotes: 1

Related Questions