HELP! does access has limit in back out data?

[ges via AccessMonster.com]

Hi,
I have created a back out button. If my data to back out less than 9,500
records then it back out the whole thing, but if my data over 9,000 - 10,000
(most my file that need back out is over 15,000 records), it's stop around 9,
500 it did not finish the job, JUST STOP AND DOING NOTHING then I have to
click the button again to delete the rest ($5,500 records). Can anybody HELP
me? Thanks so much for any input!
Below are the code I created:

Private Sub cmdbtnBackOut_Click()
On Error GoTo Exit_cmdbtnBackOut

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim parmBatchNo As DAO.Parameter
Dim rstCollectionsInfo As DAO.Recordset

Dim strSQL As String
Dim intBatchNo As Integer
Dim Counter As Double

Dim RecsToDelete

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("")

strSQL = "PARAMETERS intBatchNumber INT; "
strSQL = strSQL & "SELECT tblCollectionsInfo.BatchNumber FROM
[tblCollectionsInfo] "
strSQL = strSQL & "WHERE ( ([BatchNumber] = [intBatchNumber] And
[BatchNumber] > 0) );"
qdf.SQL = strSQL

Set parmBatchNo = qdf.Parameters!intBatchNumber

intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))
parmBatchNo = intBatchNo

Debug.Print qdf.Parameters.Count
For Each parmBatchNo In qdf.Parameters
Debug.Print , parmBatchNo.Name, parmBatchNo.Type, parmBatchNo.Value
Next parmBatchNo

Set rstCollectionsInfo = qdf.OpenRecordset()

rstCollectionsInfo.MoveLast
RecsToDelete = rstCollectionsInfo.RecordCount
MsgBox "We have " & RecsToDelete & " records to delete."
DoCmd.Hourglass True
Counter = 0

Do Until rstCollectionsInfo.BOF
rstCollectionsInfo.Delete
rstCollectionsInfo.MovePrevious
Counter = Counter + 1
Loop

MsgBox "We have deleted " & Counter & " records."

qdf.Close

Exit_cmdbtnBackOut:


THANK YOU!

 

[Stefan Hoffmann]

hi,

I have created a back out button.

What's that?

strSQL = "PARAMETERS intBatchNumber INT; "
strSQL = strSQL & "SELECT tblCollectionsInfo.BatchNumber FROM
[tblCollectionsInfo] "
strSQL = strSQL & "WHERE ( ([BatchNumber] = [intBatchNumber] And
[BatchNumber] > 0) );"
qdf.SQL = strSQL

As far as I understand your code, you want to delete records matching a
criteria?

So this should do it:

intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))
SQL = "DELETE FROM tblCollectionsInfo " & _
"WHERE BatchNumber = " & intBatchNo
CurrentDb.Execute SQL, dbFailOnError


mfG
--> stefan <--

 

[ges via AccessMonster.com]

Hi Stefan,
back out button is a command button in main menu form, when user click it
will run the code to delete record.

Yes I would like to be able to delete record based on the batch number.

I try :
intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))
strSQL = "DELETE* FROM tblCollectionsInfo" & _
"WHERE BatchNumber=" & intBatchNo
CurrentDb.Execute strSQL

But it did not delete anything at all, do I miss something? Without using
recordset, how do I show message how many records being deleted?

Thanks,
Ges

hi,

I have created a back out button.

What's that?

strSQL = "PARAMETERS intBatchNumber INT; "
strSQL = strSQL & "SELECT tblCollectionsInfo.BatchNumber FROM
[tblCollectionsInfo] "
strSQL = strSQL & "WHERE ( ([BatchNumber] = [intBatchNumber] And
[BatchNumber] > 0) );"
qdf.SQL = strSQL

As far as I understand your code, you want to delete records matching a
criteria?

So this should do it:

intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))
SQL = "DELETE FROM tblCollectionsInfo " & _
"WHERE BatchNumber = " & intBatchNo
CurrentDb.Execute SQL, dbFailOnError

mfG
--> stefan <--

 

[Paolo]

Hi ges,
in order to make your statement works you must add a space between delete
and * in this way

strSQL = "DELETE * FROM tblCollectionsInfo" & _
"WHERE BatchNumber=" & intBatchNo

HTH Paolo

Hi Stefan,
back out button is a command button in main menu form, when user click it
will run the code to delete record.

Yes I would like to be able to delete record based on the batch number.

I try :
intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))
strSQL = "DELETE* FROM tblCollectionsInfo" & _
"WHERE BatchNumber=" & intBatchNo
CurrentDb.Execute strSQL

But it did not delete anything at all, do I miss something? Without using
recordset, how do I show message how many records being deleted?

Thanks,
Ges

hi,

I have created a back out button.

What's that?

strSQL = "PARAMETERS intBatchNumber INT; "
strSQL = strSQL & "SELECT tblCollectionsInfo.BatchNumber FROM
[tblCollectionsInfo] "
strSQL = strSQL & "WHERE ( ([BatchNumber] = [intBatchNumber] And
[BatchNumber] > 0) );"
qdf.SQL = strSQL

As far as I understand your code, you want to delete records matching a
criteria?

So this should do it:

intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))
SQL = "DELETE FROM tblCollectionsInfo " & _
"WHERE BatchNumber = " & intBatchNo
CurrentDb.Execute SQL, dbFailOnError

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I try :
intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))
strSQL = "DELETE* FROM tblCollectionsInfo" & _
"WHERE BatchNumber=" & intBatchNo
CurrentDb.Execute strSQL

But it did not delete anything at all, do I miss something? Without using
recordset, how do I show message how many records being deleted?

You don't need the * in the DELETE statement. Using

CurrentDb.Execute strSQL, dbFailOnError

would throw an appropriate error message.


mfG
--> stefan <--

 

[John Spencer]

This is also missing a space between
TblCollectonsInfo and WHERE BatchNumber ...


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ges via AccessMonster.com]

Thank you all for your reply! I apreciate every one of you!

Ges

This is also missing a space between
TblCollectonsInfo and WHERE BatchNumber ...

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

hi,

[quoted text clipped - 15 lines]

mfG
--> stefan <--

 

[ges via AccessMonster.com]

Stefan,
Thanks again! I don't use * and the code you gave me work great!

Ges

hi,

I try :
intBatchNo = Int(Inputbox("Enter Batch Number of Records to Remove"))

[quoted text clipped - 4 lines]

But it did not delete anything at all, do I miss something? Without using
recordset, how do I show message how many records being deleted?

You don't need the * in the DELETE statement. Using

CurrentDb.Execute strSQL, dbFailOnError

would throw an appropriate error message.

mfG
--> stefan <--