Robert L. Stewart
rl_stewart at highstream.net
Fri Oct 10 07:56:16 CDT 2003
Rocky,
I am sending this again with comments this time.
I think that will help you (and others) to see
what I did.
Break your process into steps.
The following should be close but I think the
term is air code.
' If you had a flag of some kind that showed
' that the process had already been run against
' the records, we could reduce the number of
' records and get some blazing speed out of it.
' The basic problem is that we are reprocessing
' records that have already been done before
' instead of just processing those that need it
' Once the flag is added and set, we would be
' processing a much smaller set of data.
dim db as database, rs as recordset, rsTax as recordset
dim strSQL as string
dim curSalesTax as currency
' set up the SQL string to use for opening
' the recordset
strSQL = "Select * "
strsql = strsql & " FROM tblPrintRecoveryForm"
strsql = strsql & " WHERE Nz(fldInvoiceDetailPriceShouldBe) = 0;)
' Open the recordset
Set rs = db.OpenRecordset(strsql, dbopendynaset)
' check to see if there are records in it
if not(rs.eof and rs.bof) then ' records were returned
' close the recordset
rs.close
' setup the SQL string to do the update
strsql = "UPDATE tblPrintRecoveryForm SET PriceError = 0,"
strsql = strsql & " TotalDue = 0"
strsql = strsql & " WHERE Nz(fldInvoiceDetailPriceShouldBe) = 0;)
' run the SQL directly to perform the update
docmd.runsql strsql
end if
' set up the SQL string to use for opening
' the next recordset
strSQL = "Select * "
strsql = strsql & " FROM tblPrintRecoveryForm"
strsql = strsql & " WHERE fldInvoiceDetailPricePaid < 0;)"
Set rs = db.OpenRecordset(strsql, dbopendynaset)
if not(rs.eof and rs.bof) then ' records were returned
rs.close
' setup the SQL string to do the update
strsql = "UPDATE tblPrintRecoveryForm"
strsql = strsql & " SET PriceError = fldInvoiceDetailPricePaid,"
strsql = strsql & " TotalDue = PriceError * fldInvoiceDetailQuantity"
strsql = strsql & " WHERE fldInvoiceDetailPricePaid < 0;)"
' run the SQL directly to perform the update
docmd.runsql strsql
end if
' set up the SQL string to use for opening
' the next recordset
strSQL = "Select * "
strsql = strsql & " FROM tblPrintRecoveryForm"
strsql = strsql & " WHERE fldInvoiceDetailSalesTax = 'Y';)"
Set rs = db.OpenRecordset(strsql, dbopendynaset)
' since we are limiting the records here instead of
' checking the value as we move through the recordset
' we are actually process less records
if not(rs.eof and rs.bof) then ' records were returned
' here is where it will be slow
' we are having to move through all of the records
' and then check for the tax rate for each individual
' record
with rs
.movefirst
do until .eof
' this SQL string should return a single record
' if there is a tax rate for the date range
strsql = SELECT *"
strsql = strsql & " FROM tblTaxRates
strsql = strsql & " WEHRE fldTaxRateStartDate <= #"
strsql = strsql & !fldInvoiceDate & "# AND
fldTaxRateEndDate >= #"
strsql = strsql & !fldInvoiceDate & "#;"
set rstax = db.openrecordset(strsql, dbopensnapshot)
if not(rstax.eof and rstax.bof) then ' a tax rate was found
.edit
' now we can edit and update the record
' with the tax info
curSalesTax = !PriceError * rsTax!fldTaxRate
!SalesTax = curSalesTax
!TotalDue = (!PriceError + curSalesTax) *
!fldInvoiceDetailQuantity
.update
end if
' now we can go to the next record and
' check it
.movenext
loop
end with
end if
rs.close
rstax.close
set db=nothing
Watch for line wraps
HTH
Robert
At 11:45 AM 10/9/2003 -0500, you wrote:
>Date: Thu, 9 Oct 2003 09:44:40 -0700
>From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
>Subject: [AccessD] File Sharing lock count exceeded
>To: <AccessD at databaseadvisors.com>
>Message-ID: <015f01c38e84$a2551ca0$6701a8c0 at HAL9002>
>Content-Type: text/plain; charset="iso-8859-1"
>
>Dear List:
>
>I am getting an error in a loop which says:
>
>File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.
>
>I don't think that's the solution. The lock limit should not be
>exceeded. Somehow I'm not releasing the locks as they're being
>created. Is there a trick to doing that? It blows at different counts,
>too. Sometimes over 70,000 records get processed, sometimes 40,000.
>
>Here's the code:
>
>'********************************************************************************
>Set rstTaxRate = db.OpenRecordset("Select * FROM tblTaxRates")
>Set rstRecoveryTable = db.OpenRecordset("Select * FROM tblPrintRecoveryForm")
>
>With rstRecoveryTable
>
>If .BOF = True And .EOF = True Then GoTo GoodBye:
>
>.MoveLast
>txtTotalRecords = .RecordCount
>.MoveFirst
>lngCount = 0
>
>Do While Not .EOF
>
> lngCount = lngCount + 1
> If lngCount Mod 1000 = 0 Then
> txtRecordNumber = lngCount
> Me.Repaint
> End If
>
> .Edit
>
> If Nz(!fldInvoiceDetailPriceShouldBe) = 0 Then
> !PriceError = 0
> !TotalDue = 0
> End If
>
> If Nz(!fldInvoiceDetailPricePaid) < 0 Then
> !PriceError = !fldInvoiceDetailPricePaid
> !TotalDue = !PriceError * !fldInvoiceDetailQuantity
> End If
>
> If !fldInvoiceDetailSalesTax = "Y" Then
>
> ' find tax rate for this invoice date; if not found, tax is zero
> rstTaxRate.FindFirst "fldTaxRateStartDate <= #" & !fldInvoiceDate _
> & "# AND fldTaxRateEndDate >= #" & !fldInvoiceDate & "#"
>
> If rstTaxRate.NoMatch = False Then
> !SalesTax = !PriceError * rstTaxRate!fldTaxRate
> !TotalDue = (!PriceError + !SalesTax) * !fldInvoiceDetailQuantity
> End If
>
> End If
>
> .Update
>
> .MoveNext
>Loop
>
>End With
>'*************************************************************************
>
>Any hints?
>
>MTIA,
>
>Rocky Smolin
>Beach Access Software