Changed MaxLocksPerFile in register - no result

[Ronny Sigo]

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

[Lyle Fairfield]

"Ronny Sigo" <roon ad skynet.be> wrote in
news:44***********************@news.skynet.be:

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

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

[FMS Development Team]

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:
http://www.fmsinc.com/tpapers/queries/index.html#custom

Luke Chung
President
FMS, Inc.
http://www.fmsinc.com

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.

[Ron2005]

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.

[Ronny Sigo]

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

Greetz ...

"Ronny Sigo" <roon ad skynet.be> schreef in bericht
news:44***********************@news.skynet.be...

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

[Ron2005]

Here is the microsoft knowledge base reference:
http://support.microsoft.com/kb/161329/en-us
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