[AccessD] File Sharing lock count exceeded

Rocky,

<<Any hints?>>

 The database(s) are all on a station or split between a client and server?

 Besides that, don't use SQL statements when opening the tables, just open
them directly.

Set rstTaxRate = db.OpenRecordset("tblTaxRates")
Set rstRecoveryTable = db.OpenRecordset("tblPrintRecoveryForm")

  JET's trying to use an implicit transaction to handle the update because
of that.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net
  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin -
Beach Access Software
  Sent: Thursday, October 09, 2003 12:45 PM
  To: AccessD at databaseadvisors.com
  Subject: [AccessD] File Sharing lock count exceeded


  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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20031009/e575a60d/attachment-0001.html>