[AccessD] File Sharing lock count exceeded

Well.  Simple solutions for simple people, I always say.  That seems to have done it.  That "Select *... syntax is just a habit with me.  Thank you.

The other solution that Robert Steward sent me also works although I'm not sure why.  But it slows down the processing considerably - like a factor of five.  I need to figure out what he did because it looks like a good technique when needed.

Is there ever a reason to "Select * FROM tblXYZ"?

Tanks again and regards,

Rocky Smolin
Beach Access Software

  ----- Original Message ----- 
  From: Jim Dettman 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, October 09, 2003 2:03 PM
  Subject: RE: [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




------------------------------------------------------------------------------


  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20031009/b333c3fe/attachment-0001.html>