[AccessD] Question about deleteing from datasheet view

Doug,
 
I just recreated your situation(just in query, not using forms).
 
I have a query based on the SQL below...execute, returns records, I try to delete, get a message 'Are you sure', I say yes...records go away...when I run the same SQL again...the records are back???
 
I think I remember in older versions (not sure which) that you would get a "query not updatable" error in cases like this...but why it is acting this way now...I have no idea.
 
Mark M
 

 

> From: dw-murphy at cox.net
> To: accessd at databaseadvisors.com
> Date: Thu, 1 Mar 2012 11:53:18 -0800
> Subject: Re: [AccessD] Question about deleteing from datasheet view
> 
> Hello Jack,
> 
> Thanks for your input. The problem here is not really with the way we
> refresh/requery the form. It is the fact that records that appeared to have
> been deleted really aren't. You can see this if you go into Northwind and
> add the following query. 
> 
> SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipName,
> Orders.ShipAddress, [Order Details].OrderID
> FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
> Details].OrderID
> WHERE ((([Order Details].OrderID) Is Null));
> 
> Assuming there are some orders that don't have any details you will see
> them. Delete them from the query datasheet view, then refresh/requery. The
> supposedly deleted records are there again. I am sure there is a good reason
> you can't delete records this way, but I learned something. I reconstructed
> the query to use a subquery instead of the outer join and it works as
> expected. It just runs much more slowly.
> 
> Doug
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack drawbridge
> Sent: Thursday, March 01, 2012 11:16 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Question about deleteing from datasheet view
> 
> Doug,
> I think this may be relevant
> 
> The Requery method updates the data underlying a form or control to reflect
> records that are new to or have been deleted from the record source since it
> was last requeried.
> 
> The Refresh method shows only changes that have been made to the current set
> of records; it doesn't reflect new records or deleted records in the record
> source.
> 
> The Repaint method simply repaints the specified form and its controls.
> 
> Jack
> 
> On Thu, Mar 1, 2012 at 11:35 AM, Doug Murphy <dw-murphy at cox.net> wrote:
> 
> > Folks,
> >
> > I have a form in datasheet view that is based on a query. The query 
> > shows all the records in table A that don't have a child record in 
> > Table B. The query is a simple right outer join showing columns from 
> > table A and a criteria on the id field from table B that is set to 
> > null. This shows me all the records in A that don't have a child in B. 
> > The objective is to allow the user to delete some or all of the 
> > displayed records. The interesting thing is that if a record is 
> > deleted from this view I get the "You're going to delete one record 
> > message" and the record goes away in the datasheet view, but if the 
> > form is refreshed the record reappears. It would appear that the 
> > record is removed from the forms recordset clone but not the actual 
> > recordset. I could swear that I have done this many times in the past 
> > and it always resulted in the record being deleted. Any idea what is 
> > going on?
> >
> > I opened the forms record source query and get the same behavior there.
> >
> > Thanks in advance.
> >
> > Doug
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com