RunSQL Delete is locking up/taking forever

[paulwilliamsonremove]

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPersonIDs") if they don't exist in another table
("qryDonations"). But the SQL syntax I came up with is taking forever
or locking up Access 2003.

What am I doing wrong?

The code is below. dFromDate_pw and dToDate_pw are set to values of
date fields on a form. qryHistoryPersonIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs Where
qryHistoryPersonIDs.personID Not In (Select qryDonations.personid From
qryDonations Where tblDonations.[DonationDate] Not Between #" &
dFromDate_pw & "# And #" & dToDate_pw & "# )"

I'd rather not manually read through the table as it has 4,000 records
and is growing.

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

[jimfortune]

pa******************@removespamcop.net wrote:

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPersonIDs") if they don't exist in another table
("qryDonations"). But the SQL syntax I came up with is taking forever
or locking up Access 2003.

What am I doing wrong?

The code is below. dFromDate_pw and dToDate_pw are set to values of
date fields on a form. qryHistoryPersonIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs Where
qryHistoryPersonIDs.personID Not In (Select qryDonations.personid From
qryDonations Where tblDonations.[DonationDate] Not Between #" &
dFromDate_pw & "# And #" & dToDate_pw & "# )"

I'd rather not manually read through the table as it has 4,000 records
and is growing.

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

The 'Not In (Select... syntax is known to be slow for larger tables.
If you use the wizard to create an unmatched query it will show you an
alternate syntax for returning the same records using a LEFT JOIN that
runs much faster.

James A. Fortune

[paulwilliamsonremove]

On 16 Nov 2005 15:20:27 -0800, ji********@compumarc.com wrote:

pa******************@removespamcop.net wrote:

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPersonIDs") if they don't exist in another table
("qryDonations"). But the SQL syntax I came up with is taking forever
or locking up Access 2003.

What am I doing wrong?

The code is below. dFromDate_pw and dToDate_pw are set to values of
date fields on a form. qryHistoryPersonIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs Where
qryHistoryPersonIDs.personID Not In (Select qryDonations.personid From
qryDonations Where tblDonations.[DonationDate] Not Between #" &
dFromDate_pw & "# And #" & dToDate_pw & "# )"

I'd rather not manually read through the table as it has 4,000 records
and is growing.

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

The 'Not In (Select... syntax is known to be slow for larger tables.
If you use the wizard to create an unmatched query it will show you an
alternate syntax for returning the same records using a LEFT JOIN that
runs much faster.

James A. Fortune

Hi James.

I did as you suggested and converted this (from the wizard SQL
Syntax):

SELECT tblHistoryPersonIDs.PersonID
FROM tblHistoryPersonIDs LEFT JOIN tblDonations ON
tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE (((tblDonations.PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPersonIDs LEFT JOIN
tblDonations ON tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
And #" & dToDate_pw & "#"

And I am getting "Specify the table containing the records you want to
delete". Can't figure it out. Am getting sleepy ;-0

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

[jimfortune]

pa******************@removespamcop.net wrote:

On 16 Nov 2005 15:20:27 -0800, ji********@compumarc.com wrote:

pa******************@removespamcop.net wrote:

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPersonIDs") if they don't exist in another table
("qryDonations"). But the SQL syntax I came up with is taking forever
or locking up Access 2003.

What am I doing wrong?

The code is below. dFromDate_pw and dToDate_pw are set to values of
date fields on a form. qryHistoryPersonIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs Where
qryHistoryPersonIDs.personID Not In (Select qryDonations.personid From
qryDonations Where tblDonations.[DonationDate] Not Between #" &
dFromDate_pw & "# And #" & dToDate_pw & "# )"

I'd rather not manually read through the table as it has 4,000 records
and is growing.

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

The 'Not In (Select... syntax is known to be slow for larger tables.
If you use the wizard to create an unmatched query it will show you an
alternate syntax for returning the same records using a LEFT JOIN that
runs much faster.

James A. Fortune

Hi James.

I did as you suggested and converted this (from the wizard SQL
Syntax):

SELECT tblHistoryPersonIDs.PersonID
FROM tblHistoryPersonIDs LEFT JOIN tblDonations ON
tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE (((tblDonations.PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPersonIDs LEFT JOIN
tblDonations ON tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
And #" & dToDate_pw & "#"

And I am getting "Specify the table containing the records you want to
delete". Can't figure it out. Am getting sleepy ;-0

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

I didn't check your unmatched query, but when you get the unmatched
records the 'Not In (SELECT...' switches to 'IN (SELECT
tblHistoryPersonIDs.PersonID ...

That is, the unmatched query should return the ones that are 'Not In'
so they are now 'In.'

I explained this in:

http://groups.google.com/group/comp....63454c21472dc3

but I didn't do that great a job of explaining it there either. If
that doesn't help, post back and I'll organize my thoughts into
something a little more elegant and understandable. BTW, the ecommerce
code in that thread was from about six years ago and I cringed when I
saw it.

James A. Fortune

[paulwilliamsonremove]

On 17 Nov 2005 00:02:35 -0800, ji********@compumarc.com wrote:

pa******************@removespamcop.net wrote:

On 16 Nov 2005 15:20:27 -0800, ji********@compumarc.com wrote:

>pa******************@removespamcop.net wrote:
>> Hi,
>>
>> Using Access 2003.
>>
>> I am trying to delete records from one table/query
>> ("qryHistoryPersonIDs") if they don't exist in another table
>> ("qryDonations"). But the SQL syntax I came up with is taking forever
>> or locking up Access 2003.
>>
>> What am I doing wrong?
>>
>> The code is below. dFromDate_pw and dToDate_pw are set to values of
>> date fields on a form. qryHistoryPersonIDs contains one key field:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPersonIDs Where
>> qryHistoryPersonIDs.personID Not In (Select qryDonations.personid From
>> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>>
>> I'd rather not manually read through the table as it has 4,000 records
>> and is growing.
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail
>
>The 'Not In (Select... syntax is known to be slow for larger tables.
>If you use the wizard to create an unmatched query it will show you an
>alternate syntax for returning the same records using a LEFT JOIN that
>runs much faster.
>
>James A. Fortune
Hi James.

I did as you suggested and converted this (from the wizard SQL
Syntax):

SELECT tblHistoryPersonIDs.PersonID
FROM tblHistoryPersonIDs LEFT JOIN tblDonations ON
tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE (((tblDonations.PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPersonIDs LEFT JOIN
tblDonations ON tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
And #" & dToDate_pw & "#"

And I am getting "Specify the table containing the records you want to
delete". Can't figure it out. Am getting sleepy ;-0

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

I didn't check your unmatched query, but when you get the unmatched
records the 'Not In (SELECT...' switches to 'IN (SELECT
tblHistoryPersonIDs.PersonID ...

That is, the unmatched query should return the ones that are 'Not In'
so they are now 'In.'

That hurts! <g>
I explained this in:

http://groups.google.com/group/comp....63454c21472dc3

but I didn't do that great a job of explaining it there either. If
that doesn't help, post back and I'll organize my thoughts into
something a little more elegant and understandable.
Okay. Go ahead ;-)
BTW, the ecommerce
code in that thread was from about six years ago and I cringed when I
saw it.

Then I'll have to check it out for sure! :)

Thanks again,

-paul
James A. Fortune

-pw

use paulwilliamson at spamcop dot net for e-mail

[paulwilliamsonremove]

>

but I didn't do that great a job of explaining it there either. If
that doesn't help, post back and I'll organize my thoughts into
something a little more elegant and understandable.

Oops. I had a brain lapse and started a new thread instead of
replying to you. Oh well. Will be interesting to see if I get any
bites.

Thanks James,

-paul
-pw

use paulwilliamson at spamcop dot net for e-mail

[paulwilliamsonremove]

On 17 Nov 2005 00:02:35 -0800, ji********@compumarc.com wrote:

pa******************@removespamcop.net wrote:

On 16 Nov 2005 15:20:27 -0800, ji********@compumarc.com wrote:

>pa******************@removespamcop.net wrote:
>> Hi,
>>
>> Using Access 2003.
>>
>> I am trying to delete records from one table/query
>> ("qryHistoryPersonIDs") if they don't exist in another table
>> ("qryDonations"). But the SQL syntax I came up with is taking forever
>> or locking up Access 2003.
>>
>> What am I doing wrong?
>>
>> The code is below. dFromDate_pw and dToDate_pw are set to values of
>> date fields on a form. qryHistoryPersonIDs contains one key field:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPersonIDs Where
>> qryHistoryPersonIDs.personID Not In (Select qryDonations.personid From
>> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>>
>> I'd rather not manually read through the table as it has 4,000 records
>> and is growing.
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail
>
>The 'Not In (Select... syntax is known to be slow for larger tables.
>If you use the wizard to create an unmatched query it will show you an
>alternate syntax for returning the same records using a LEFT JOIN that
>runs much faster.
>
>James A. Fortune

Hi James.

I did as you suggested and converted this (from the wizard SQL
Syntax):

SELECT tblHistoryPersonIDs.PersonID
FROM tblHistoryPersonIDs LEFT JOIN tblDonations ON
tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE (((tblDonations.PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPersonIDs LEFT JOIN
tblDonations ON tblHistoryPersonIDs.PersonID = tblDonations.PersonID
WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
And #" & dToDate_pw & "#"

And I am getting "Specify the table containing the records you want to
delete". Can't figure it out. Am getting sleepy ;-0

Any ideas?

-pw

use paulwilliamson at spamcop dot net for e-mail

I didn't check your unmatched query, but when you get the unmatched
records the 'Not In (SELECT...' switches to 'IN (SELECT
tblHistoryPersonIDs.PersonID ...

That is, the unmatched query should return the ones that are 'Not In'
so they are now 'In.'

I explained this in:

http://groups.google.com/group/comp....63454c21472dc3

but I didn't do that great a job of explaining it there either. If
that doesn't help, post back and I'll organize my thoughts into
something a little more elegant and understandable. BTW, the ecommerce
code in that thread was from about six years ago and I cringed when I
saw it.

James A. Fortune

I got it James:
strSQL_pw = _
"DELETE * From tblHistoryPersonIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.PersonID =
tblHistoryPersonIDs.PersonID " & _
"AND (DonationDate < #" & dFromDate_pw & "# " & _
"OR DonationDate > #" & dToDate_pw & "#))"

DoCmd.RunSQL strSQL_pw

And it ran quickly when I put in a date range to delete all the
records.

Phew! That was an important stumbling block because I have to do it
for a bunch of other combinations on this form.

Thanks!

-paul
-pw

use paulwilliamson at spamcop dot net for e-mail

[jimfortune]

pa******************@removespamcop.net wrote:

On 17 Nov 2005 00:02:35 -0800, ji********@compumarc.com wrote: I got it James:
strSQL_pw = _
"DELETE * From tblHistoryPersonIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.PersonID =
tblHistoryPersonIDs.PersonID " & _
"AND (DonationDate < #" & dFromDate_pw & "# " & _
"OR DonationDate > #" & dToDate_pw & "#))"

DoCmd.RunSQL strSQL_pw

And it ran quickly when I put in a date range to delete all the
records.

Phew! That was an important stumbling block because I have to do it
for a bunch of other combinations on this form.

Thanks!

-paul
-pw

use paulwilliamson at spamcop dot net for e-mail

I was dubious about the 'Not Between' also. I'm glad you found a way
to do it efficiently. The way I suggested should also have worked
quite well though.

James A. Fortune

[paulwilliamsonremove]

On 17 Nov 2005 14:16:59 -0800, ji********@compumarc.com wrote:

pa******************@removespamcop.net wrote:

On 17 Nov 2005 00:02:35 -0800, ji********@compumarc.com wrote:

I got it James:
strSQL_pw = _
"DELETE * From tblHistoryPersonIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.PersonID =
tblHistoryPersonIDs.PersonID " & _
"AND (DonationDate < #" & dFromDate_pw & "# " & _
"OR DonationDate > #" & dToDate_pw & "#))"

DoCmd.RunSQL strSQL_pw

And it ran quickly when I put in a date range to delete all the
records.

Phew! That was an important stumbling block because I have to do it
for a bunch of other combinations on this form.

Thanks!

-paul
-pw

use paulwilliamson at spamcop dot net for e-mail

I was dubious about the 'Not Between' also. I'm glad you found a way
to do it efficiently. The way I suggested should also have worked
quite well though.

James A. Fortune

Yeah, that Not Between was a killer. Thank you much for your help!

-pw

use paulwilliamson at spamcop dot net for e-mail