Changed MaxLocksPerFile in register - no result

Re: Changed MaxLocksPerFile in register - no result

"Ronny Sigo" <roon ad skynet.be> wrote in
news:44437bf6$0 $23533$ba620e4c @news.skynet.be :
[color=blue]
> Hi all,
> I am trying to search a table of emailaddresses records (tblMails -
> slightly more then 7000)for
> the occurance of words which are stored in another table
> (tblFilterwords- aprox 3000 records) using DAO.
> The program works as follows:
> First I define a recordset called rstMails
> Then I make a loop in VBA while looking for the occurence of "<" in
> the field emailaddress. Using this I am able to extract the "pure"
> emailaddress, without any names or other information. The remainder of
> the information is being written to another field.
> After this has been done (about 7000 times) I make another loop and
> start looking for the occurance of the filterwords which are stored in
> the second table (about 3000 records). So each emailaddress has to be
> compared with evey value in the second table. So the total number of
> comparisions is 7000 x 3000 = about 21,000,000.
> This all worked fine when the first table had only about 6000 records.
> When adding another 1200 records to the first table, all of the sudden
> Access gave me the following warning (translated in English 'cause I
> have the Dutch version): "The number of locks for shared file using
> has been exceeded. Increase MaxLocksPerFile in register.
>
> So, that's what I did, I increased the value from the default (9500)
> to 200000. No result at all, it keeps on giving me the same message. I
> don't really understand what this MaxLocksPerFile is.
> Can anybody please help me with this problem?
>
> Thanks in advance[/color]

When we meet the limits of Access or the OS, it is usually because we
have chosen to try to implement an inefficient solution.
If you tell us the version of Access you are using someone may post a
suggestion for a cost-effective (in terms of resources) alternate way of
resolving the difficulty.
Very frequently, there is a better way than looping through recordsets.

--
Lyle Fairfield

Re: Changed MaxLocksPerFile in register - no result

Try doing this in a query rather than VBA code using the InStr function
to locate the "<" and Left or Right function to get the remainder.

Alternatively, you can create a custom function that is passed the
field value as a parameter and let it parse out the part you want as
the return value.

That way you can get what you need without opening a recordset and you
will avoid the MaxLocks problem.

For more information, read my paper on Query Tips and Techniques with
the section on using Custom Functions:


Luke Chung
President
FMS, Inc.


P.S. Visit our web site for a FREE preview of Total Access Emailer
2003, version 11.5! Send personalized emails to your contacts. You can
now embed Access tables, queries, and reports in your email messages.
Use the Access report generator as your HTML email creator.

Re: Changed MaxLocksPerFile in register - no result

I will have to go back and check some old mdb files that I had. But I
had a similar problem, and the solution to the problem of the # not be
reset was that I had to issue the command in the db to reset the
number. However the solutions that would cut down on the acquisitions
would be the best.

Re: Changed MaxLocksPerFile in register - no result

Hi,
Thanks for responding :) My version of Access is Access 2003

Greetz ...

"Ronny Sigo" <roon ad skynet.be> schreef in bericht
news:44437bf6$0 $23533$ba620e4c @news.skynet.be ...[color=blue]
> Hi all,
> I am trying to search a table of emailaddresses records (tblMails -
> slightly more then 7000)for
> the occurance of words which are stored in another table (tblFilterwords-
> aprox 3000 records) using DAO.
> The program works as follows:
> First I define a recordset called rstMails
> Then I make a loop in VBA while looking for the occurence of "<" in the
> field emailaddress. Using this I am able to extract the "pure"
> emailaddress, without any names or other information. The remainder of the
> information is being written to another field.
> After this has been done (about 7000 times) I make another loop and start
> looking for the occurance of the filterwords which are stored in the
> second table (about 3000 records). So each emailaddress has to be compared
> with evey value in the second table. So the total number of comparisions
> is 7000 x 3000 = about 21,000,000.
> This all worked fine when the first table had only about 6000 records.
> When adding another 1200 records to the first table, all of the sudden
> Access gave me the following warning (translated in English 'cause I have
> the Dutch version): "The number of locks for shared file using has been
> exceeded. Increase MaxLocksPerFile in register.
>
> So, that's what I did, I increased the value from the default (9500) to
> 200000. No result at all, it keeps on giving me the same message. I don't
> really understand what this MaxLocksPerFile is.
> Can anybody please help me with this problem?
>
> Thanks in advance
>
>
>[/color]

Re: Changed MaxLocksPerFile in register - no result

Here is the microsoft knowledge base reference:



Method 2: Using SetOption to Change MaxLocksPerFile Temporarily
The SetOption method temporarily overrides values for the Microsoft Jet
database engine keys in the Windows Registry. The new value remains in
effect until you change it again, or until the DBEngine object is
closed.

NOTE: Changes made to the MaxLocksPerFile setting using the SetOption
method will only be available through the current session of DAO (Data
Access Objects). Queries run through the Microsoft Access user
interface, will still use the settings within the registry.

The article has an example.

I believe this is what I did, and the problem was resolved.

Hope this helps.

Ron