By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,208 Members | 1,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,208 IT Pros & Developers. It's quick & easy.

Stopping/preventing Access from continuing onto another process/action

P: n/a
Rey
Howdy all.

Am attempting to delete a large number of records (123K) from a table
using:
db.execute "delete from tblname"

Then I double check (code below) to see if records still remain as
during my testing they have...and that causes problems when the app
reloads the table w/current data and encounters already existing
keys...

Seems that at 63K records in tbl, the db.execute statement had no
problems but at 123K it still seems to be working/executing in the
backkground...

I'm having no luck in attempting to check/make sure that no records
exist in tbl and that process has completely finished deleting records
from the other six tables before continuing on with the process of
reloading data.

BTW, Delay function is from an Allen Browne post regarding use of the
Sleep API.

Appreciate any help/suggestions.

Thank you,
Rey
Double check code:
Private Sub CheckRecordSetAgain(strTbl As String)
' check rec count & print out info...
Dim rs As New ADODB.Recordset
Dim X As Long

rs.Open strTbl, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Write #intFile, "Deleting " & strTbl & " - " & Str(rs.RecordCount) &
" Records"

If strTbl = "OrderList" Or strTbl = "TaxDesc" Then
Stop
End If

If rs.RecordCount = 0 Then
Write #intFile, strTbl & " - No Records to delete"
Else
Do While Not rs.EOF
rs.Delete
rs.MoveNext
Loop

Delay (5)

' tell me o genie, have I succeeded...
Write #intFile, "Deleted " & strTbl & " - " & Str(rs.RecordCount) &
" Records"

End If

' If rs.RecordCount = 0 Then
' Write #intFile, strTbl & " - No Records to delete"
' Else
' Write #intFile, strTbl & " - " & Str(rs.RecordCount) & " Records"
' Delay (5) ' add slight delay
' ' try again deleting remaining recs
' With rs
' For X = 1 To rs.RecordCount
' .Delete
' .MoveNext
' Next
' End With
'
' End If
rs.Close

End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Use the switch:
db.Execute "Delete FROM tblName;", dbFailOnError

Note that dbFailOnError simply bails out at the point where a failure
occurs, i.e. it does not roll back the state of the database (despite the
Access 97 documentation).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rey" <re********@cox.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Howdy all.

Am attempting to delete a large number of records (123K) from a table
using:
db.execute "delete from tblname"

Then I double check (code below) to see if records still remain as
during my testing they have...and that causes problems when the app
reloads the table w/current data and encounters already existing
keys...

Seems that at 63K records in tbl, the db.execute statement had no
problems but at 123K it still seems to be working/executing in the
backkground...

I'm having no luck in attempting to check/make sure that no records
exist in tbl and that process has completely finished deleting records
from the other six tables before continuing on with the process of
reloading data.

BTW, Delay function is from an Allen Browne post regarding use of the
Sleep API.

Appreciate any help/suggestions.

Thank you,
Rey
Double check code:
Private Sub CheckRecordSetAgain(strTbl As String)
' check rec count & print out info...
Dim rs As New ADODB.Recordset
Dim X As Long

rs.Open strTbl, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Write #intFile, "Deleting " & strTbl & " - " & Str(rs.RecordCount) &
" Records"

If strTbl = "OrderList" Or strTbl = "TaxDesc" Then
Stop
End If

If rs.RecordCount = 0 Then
Write #intFile, strTbl & " - No Records to delete"
Else
Do While Not rs.EOF
rs.Delete
rs.MoveNext
Loop

Delay (5)

' tell me o genie, have I succeeded...
Write #intFile, "Deleted " & strTbl & " - " & Str(rs.RecordCount) &
" Records"

End If

' If rs.RecordCount = 0 Then
' Write #intFile, strTbl & " - No Records to delete"
' Else
' Write #intFile, strTbl & " - " & Str(rs.RecordCount) & " Records"
' Delay (5) ' add slight delay
' ' try again deleting remaining recs
' With rs
' For X = 1 To rs.RecordCount
' .Delete
' .MoveNext
' Next
' End With
'
' End If
rs.Close

End Sub

Nov 13 '05 #2

P: n/a
Rey
Howdy Allen.
Forgot to mention that I'm using Access 2002. However, will check help
and try it.

Thanks,
Rey

Nov 13 '05 #3

P: n/a
Rey
Howdy Allen.
Forgot to mention that I'm using Access 2002. However, will check help
and try it.

Thanks,
Rey

Nov 13 '05 #4

P: n/a
Rey
Howdy Allen.
Forgot to mention that I'm using Access 2002. However, will check help
and try it.

Thanks,
Rey

Nov 13 '05 #5

P: n/a
The documention for Execute (DAO) should be correct in Access 2000 onwards,
if you can find it!

In A2000 and 2002, you may need to add a reference so MS DAO 3.6. Presumably
you already have this, as you gave the db.Exectue example, If not and you
don't know how, see:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rey" <re********@cox.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Howdy Allen.
Forgot to mention that I'm using Access 2002. However, will check help
and try it.

Nov 13 '05 #6

P: n/a
ADO has many wonderful capabilities that DAO has not. "State" is one of
them. State may or may not be helpful in your case. It is for me when I
want to wait for a query to finish before I do anything else. Here is
an example:

Dim c As ADODB.Command
Set c = New ADODB.Command
Dim r As Long
With c
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "DELETE FROM Temp"
.CommandTimeout = 0 'the default; included here for clarity
.Execute r
While .State And adStateExecuting = adStateExecuting '(wait for
completion)
Wend
End With
Debug.Print r & " records were deleted"

Of course, I haven't tried this with 123 K of records, (perhaps, you
will), and probably, if the table did not have relationships I would
just delete the table and recreate it.

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.