RunSQL Delete is locking up/taking forever

[paulwilliamsonremove]

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPer sonIDs") 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. qryHistoryPerso nIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid 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************* *****@removespa mcop.net wrote:

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPer sonIDs") 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. qryHistoryPerso nIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid 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********@comp umarc.com wrote:

pa************ ******@removesp amcop.net wrote:

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPer sonIDs") 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. qryHistoryPerso nIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid 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 tblHistoryPerso nIDs.PersonID
FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
WHERE (((tblDonations .PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
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************* *****@removespa mcop.net wrote:

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

pa************ ******@removesp amcop.net wrote:

Hi,

Using Access 2003.

I am trying to delete records from one table/query
("qryHistoryPer sonIDs") 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. qryHistoryPerso nIDs contains one key field:

DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid 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 tblHistoryPerso nIDs.PersonID
FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
WHERE (((tblDonations .PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
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
tblHistoryPerso nIDs.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********@comp umarc.com wrote:

pa************ ******@removesp amcop.net wrote:

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

>pa************ ******@removesp amcop.net wrote:
>> Hi,
>>
>> Using Access 2003.
>>
>> I am trying to delete records from one table/query
>> ("qryHistoryPer sonIDs") 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. qryHistoryPerso nIDs contains one key field:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid 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 tblHistoryPerso nIDs.PersonID
FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
WHERE (((tblDonations .PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
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
tblHistoryPers onIDs.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********@comp umarc.com wrote:

pa************ ******@removesp amcop.net wrote:

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

>pa************ ******@removesp amcop.net wrote:
>> Hi,
>>
>> Using Access 2003.
>>
>> I am trying to delete records from one table/query
>> ("qryHistoryPer sonIDs") 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. qryHistoryPerso nIDs contains one key field:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid 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 tblHistoryPerso nIDs.PersonID
FROM tblHistoryPerso nIDs LEFT JOIN tblDonations ON
tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
WHERE (((tblDonations .PersonID) Is Null));

To:

DoCmd.RunSQL "Delete * From tblHistoryPerso nIDs LEFT JOIN
tblDonations ON tblHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
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
tblHistoryPers onIDs.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 tblHistoryPerso nIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.Pe rsonID =
tblHistoryPerso nIDs.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************* *****@removespa mcop.net wrote:

On 17 Nov 2005 00:02:35 -0800, ji********@comp umarc.com wrote: I got it James:
strSQL_pw = _
"DELETE * From tblHistoryPerso nIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.Pe rsonID =
tblHistoryPerso nIDs.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********@comp umarc.com wrote:

pa************ ******@removesp amcop.net wrote:

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

I got it James:
strSQL_pw = _
"DELETE * From tblHistoryPerso nIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.Pe rsonID =
tblHistoryPerso nIDs.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