Problem deleting records

[MarcVS]

I have a Frontend on the PC of every user, a Backend with the tables on the
server.
The Frontend has also one table (which has unique records for every user)

I have this Query:

SELECT PLEEGGEZIN.*, tblSelectiesPleeggezinnen.Selectie
FROM tblSelectiesPleeggezinnen RIGHT JOIN PLEEGGEZIN ON
tblSelectiesPleeggezinnen.[Code Pleeggezin] = PLEEGGEZIN.[code pleeggezin];

tblSelectiesPleeggezinnen is the Table in the FE, PLEEGGEZIN is the table
in the BE.

When I try to delete a record in this Query, it seems to work fine, but when
I reopen the query, the record in PLEEGGEZIN is still there. (But the
record in the tblSelectiesPleeggezinnen is deleted)

When I delete the record in the PLEEGGEZIN table, no problem...

Why could this happen?

Marc

 

[Michel Walsh]

Why do you use the outer join? Using the outer join insure you that EACH
record from PLEEGGEZIN will be in the result of the join, but you only want
to delete... them?

DELETE PLEEGGEZIN.*
FROM PLEEGGEZIN


would do the same thing, or I missed a point?



Vanderghast, Access MVP

 

[MarcVS]

The query is used as datasource for a form. The problem arises when I want
to delete a record, with a deletebutton on the form.
I first thought that the problem was with the form, but when I try to delete
a record from within the query itself, I have the same problem, so the
problem lies in the query, not in the form...

Why do you use the outer join? Using the outer join insure you that EACH
record from PLEEGGEZIN will be in the result of the join, but you only
want to delete... them?

DELETE PLEEGGEZIN.*
FROM PLEEGGEZIN


would do the same thing, or I missed a point?



Vanderghast, Access MVP

I have a Frontend on the PC of every user, a Backend with the tables on
the server.
The Frontend has also one table (which has unique records for every user)

I have this Query:

SELECT PLEEGGEZIN.*, tblSelectiesPleeggezinnen.Selectie
FROM tblSelectiesPleeggezinnen RIGHT JOIN PLEEGGEZIN ON
tblSelectiesPleeggezinnen.[Code Pleeggezin] = PLEEGGEZIN.[code
pleeggezin];

tblSelectiesPleeggezinnen is the Table in the FE, PLEEGGEZIN is the table
in the BE.

When I try to delete a record in this Query, it seems to work fine, but
when I reopen the query, the record in PLEEGGEZIN is still there. (But
the record in the tblSelectiesPleeggezinnen is deleted)

When I delete the record in the PLEEGGEZIN table, no problem...

Why could this happen?

Marc

 

[Michel Walsh]

Add the keyword DISTINCTROW ? Jet may then be able to trace back which
original row produces each join-result-row.

(that assumes each of the original implied 'table's (queries) in the FROM
clause is already updateable, all alone)


Vanderghast, Access MVP

The query is used as datasource for a form. The problem arises when I want
to delete a record, with a deletebutton on the form.
I first thought that the problem was with the form, but when I try to
delete a record from within the query itself, I have the same problem, so
the problem lies in the query, not in the form...

Why do you use the outer join? Using the outer join insure you that
EACH record from PLEEGGEZIN will be in the result of the join, but you
only want to delete... them?

DELETE PLEEGGEZIN.*
FROM PLEEGGEZIN


would do the same thing, or I missed a point?



Vanderghast, Access MVP

I have a Frontend on the PC of every user, a Backend with the tables on
the server.
The Frontend has also one table (which has unique records for every
user)

I have this Query:

SELECT PLEEGGEZIN.*, tblSelectiesPleeggezinnen.Selectie
FROM tblSelectiesPleeggezinnen RIGHT JOIN PLEEGGEZIN ON
tblSelectiesPleeggezinnen.[Code Pleeggezin] = PLEEGGEZIN.[code
pleeggezin];

tblSelectiesPleeggezinnen is the Table in the FE, PLEEGGEZIN is the
table in the BE.

When I try to delete a record in this Query, it seems to work fine, but
when I reopen the query, the record in PLEEGGEZIN is still there. (But
the record in the tblSelectiesPleeggezinnen is deleted)

When I delete the record in the PLEEGGEZIN table, no problem...

Why could this happen?

Marc

 

[MarcVS]

I tried this, but it didn't help...

Any other ideas would be of great help.

Marc

Add the keyword DISTINCTROW ? Jet may then be able to trace back which
original row produces each join-result-row.

(that assumes each of the original implied 'table's (queries) in the FROM
clause is already updateable, all alone)


Vanderghast, Access MVP

The query is used as datasource for a form. The problem arises when I
want to delete a record, with a deletebutton on the form.
I first thought that the problem was with the form, but when I try to
delete a record from within the query itself, I have the same problem, so
the problem lies in the query, not in the form...

Why do you use the outer join? Using the outer join insure you that
EACH record from PLEEGGEZIN will be in the result of the join, but you
only want to delete... them?

DELETE PLEEGGEZIN.*
FROM PLEEGGEZIN


would do the same thing, or I missed a point?



Vanderghast, Access MVP


I have a Frontend on the PC of every user, a Backend with the tables on
the server.
The Frontend has also one table (which has unique records for every
user)

I have this Query:

SELECT PLEEGGEZIN.*, tblSelectiesPleeggezinnen.Selectie
FROM tblSelectiesPleeggezinnen RIGHT JOIN PLEEGGEZIN ON
tblSelectiesPleeggezinnen.[Code Pleeggezin] = PLEEGGEZIN.[code
pleeggezin];

tblSelectiesPleeggezinnen is the Table in the FE, PLEEGGEZIN is the
table in the BE.

When I try to delete a record in this Query, it seems to work fine, but
when I reopen the query, the record in PLEEGGEZIN is still there. (But
the record in the tblSelectiesPleeggezinnen is deleted)

When I delete the record in the PLEEGGEZIN table, no problem...

Why could this happen?

Marc

 

[Michel Walsh]

One of your 'table' is not updateable?


In Northwind, with:

SELECT DISTINCTROW Orders.*, Orders.CustomerID
FROM [Order Details] RIGHT JOIN Orders
ON Orders.OrderID = [Order Details].OrderID


you can add (and then delete) records. So the SQL construction *IS*
updateable. If your statement is not, the problem seems to be elsewhere than
with the SQL statement ITSELF.



Vanderghast, Access MVP

I tried this, but it didn't help...

Any other ideas would be of great help.

Marc

Add the keyword DISTINCTROW ? Jet may then be able to trace back which
original row produces each join-result-row.

(that assumes each of the original implied 'table's (queries) in the FROM
clause is already updateable, all alone)


Vanderghast, Access MVP

The query is used as datasource for a form. The problem arises when I
want to delete a record, with a deletebutton on the form.
I first thought that the problem was with the form, but when I try to
delete a record from within the query itself, I have the same problem,
so the problem lies in the query, not in the form...


"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> schreef in bericht
Why do you use the outer join? Using the outer join insure you that
EACH record from PLEEGGEZIN will be in the result of the join, but you
only want to delete... them?

DELETE PLEEGGEZIN.*
FROM PLEEGGEZIN


would do the same thing, or I missed a point?



Vanderghast, Access MVP


I have a Frontend on the PC of every user, a Backend with the tables on
the server.
The Frontend has also one table (which has unique records for every
user)

I have this Query:

SELECT PLEEGGEZIN.*, tblSelectiesPleeggezinnen.Selectie
FROM tblSelectiesPleeggezinnen RIGHT JOIN PLEEGGEZIN ON
tblSelectiesPleeggezinnen.[Code Pleeggezin] = PLEEGGEZIN.[code
pleeggezin];

tblSelectiesPleeggezinnen is the Table in the FE, PLEEGGEZIN is the
table in the BE.

When I try to delete a record in this Query, it seems to work fine,
but when I reopen the query, the record in PLEEGGEZIN is still there.
(But the record in the tblSelectiesPleeggezinnen is deleted)

When I delete the record in the PLEEGGEZIN table, no problem...

Why could this happen?

Marc