[AccessD] File Sharing lock count exceeded

Erwin:

I closed the table before setting the objects to nothing.  Tried the .fields
approach but, at least in this case, it didn't make any difference in the
time.

I don't think closing the objects is the problem because the error occurs in
the loop before I get to the close.

Regards,

Rocky Smolin
Beach Access Software

----- Original Message ----- 
From: "Erwin Craps" <Erwin.Craps at ithelps.be>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, October 09, 2003 10:58 AM
Subject: RE: [AccessD] File Sharing lock count exceeded


>
>
> Set nothing is not sufficient.
>
> Recordsets/tabls need to be closed to.
>
>    rstTaxRate.close
>    rstRecoveryTable.close
>
>    Set rstTaxRate = Nothing
>    Set rstRecoveryTable = Nothing
>
> Is the correct syntax.
>
> Set nothing terminates the object container but not the underlying
> table. This could result in lock that stay open and is why you sometimes
> can not close access...
>
>
> Also replace !fieldname With .fields("fieldname")
> It sounds funny but your routine will run faster, tell me how much you
> won...
>
>
> >
>
> Erwin Craps
>
> Zaakvoerder
>
> www.ithelps.be/jonathan
>
>
>
> This E-mail is confidential, may be legally privileged, and is for the
> intended recipient only. Access, disclosure, copying, distribution, or
> reliance on any of it by anyone else is prohibited and may be a criminal
> offence. Please delete if obtained in error and E-mail confirmation to
> the sender.
>
> IT Helps - I.T. Help Center  ***  Box Office Belgium & Luxembourg
>
> www.ithelps.be  *  www.boxoffice.be  *  www.stadleuven.be
>
> IT Helps bvba* ** Mercatorpad 3 **  3000 Leuven
>
> IT Helps  *  Phone: +32 16 296 404  *  Fax: +32 16 296 405 E-mail:
> Info at ithelps.be
>
> Box Office **  Fax: +32 16 296 406 **  Box Office E-mail:
> Staff at boxoffice.be
>
>
>
> -----Oorspronkelijk bericht-----
> Van: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] Namens Rocky Smolin -
> Beach Access Software
> Verzonden: donderdag 9 oktober 2003 19:40
> Aan: Access Developers discussion and problem solving
> Onderwerp: Re: [AccessD] File Sharing lock count exceeded
>
>
> Gustav:
>
> Yeah.  But the problem comes before there anyway.  It's in the loop.
>
> Rocky
>
> ----- Original Message ----- 
> From: "Gustav Brock" <gustav at cactus.dk>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Thursday, October 09, 2003 10:09 AM
> Subject: Re: [AccessD] File Sharing lock count exceeded
>
>
> > Hi Rocky
> >
> > You did cut away in the code snip:
> >
> >   Set rstTaxRate = Nothing
> >   Set rstRecoveryTable = Nothing
> >
> > ?
> >
> > /gustav
> >
> >
> > > 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
> >
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>