0

In my Access (2013) database I have a form of which the recordsource is set on load through code. Although the query works fine when I execute it (it returns the right records) I am not able to edit or add new records to it. The adding doesn't matter but I have to be able to edit some records. The query in question is as follows:

SELECT io.*, lpo.batchid, lpo.lydiaUserID
FROM tblInkOrd io
  LEFT JOIN tblLydiaPurchaseOrder lpo ON io.becode & '.' & io.ionummer & '.' & io.iovolgnr = lpo.orderNr
WHERE becode='1SW'
ORDER BY IIF(ISNULL(levdat),0,1), levdat DESC, ionummer DESC, iovolgnr DESC

As you can see it JOINS on concatenated values and my bet is that this causes the problem. I used to work with ADP but since I migrated to Linked tables it doesn't. Any clues how to fix it or how to make a workaround with still the same result set?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Nicolas
  • 2,277
  • 5
  • 36
  • 82
  • Join-querys aren't updatable. – jarlh Jan 26 '16 at 09:49
  • 1
    That [smart key](http://stackoverflow.com/a/34082143/3404097) is an anti-pattern. – philipxy Jan 26 '16 at 10:04
  • @jarlh: Join queries are very much updateable, when proper key fields are used for the join(s). – Andre Jan 26 '16 at 10:10
  • Thanks all, I just figured it out. I created a view of the SELECT statement in my SQL Server and linked that view to my Access database and set the right primary keys so it was updatable. Instead of the SELECT statement I used the view and this seems to work (can edit/update the records). – Nicolas Jan 26 '16 at 11:18

1 Answers1

0

I just figured it out. I created a view of the SELECT statement in my SQL Server and linked that view to my Access database and set the right primary keys so it was updatable. Instead of the SELECT statement I used the view and this seems to work (can edit/update the records).

Nicolas
  • 2,277
  • 5
  • 36
  • 82