473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Stopping/preventing Access from continuing onto another process/action

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
6 1552
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Nick | last post by:
OK Guys and Gals, I've got a script that does some work with the document object in another frame. This works fine until the other frame contains a document on another domain. This of course...
3
by: shortbackandsides.no | last post by:
I've been having trouble preventing users pressing Enter part way down a form so the incomplete form gets submitted. I came up with a possible solution - the code below seems to work in both...
35
by: deko | last post by:
Do I get more scalability if I split my database? The way I calculate things now, I'll be lucky to get 100,000 records in my Access 2003 mdb. Here some math: Max mdb/mde size = 2000 x 1024 =...
1
by: Eike | last post by:
Hi, I am unable to delete a subfolder that I have created programatically. I am using a modification of the apiSHFileOperation by Dev Ashish (http://www.mvps.org/access/api/api0026.htm) to copy...
4
by: Keith | last post by:
I'm in the same boat as the fellow who posted this message back in August: Title : Windows Service, How does one make a service "fail" properly? Author : Ross Bennett Group :...
4
by: bjm | last post by:
I am writing a program that will automate a series of application installations. I want to give the user the option of stopping the program's execution in between installations (for example, give...
2
by: pingalkar | last post by:
In my application, on one form i m getting information from user and save this information , after saving this information again we loading that page. (At this moment if any one try to refresh this...
7
by: chowdary | last post by:
I am developing a PHP-mysql database. It is noted that when the browser window is refreshed the data is inserted again in the database. unfortunately there is no unique keys that I can use to...
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.