Why can't I replace more than 9489 occurances in a field?

[Guest]

When I perform a Find and Replace, the replace function will not replace more
than 9489 occurances in the table. Is there a preset limit for replace? My
table includes 868,000 records with ove 300,000 null spaces to replace in one
fieild.

 

[Douglas J. Steele]

I've never heard of such a limit. However, it sounds as though you should be
using an Update query rather than Find and Replace.

 

[storrboy]

When I perform a Find and Replace, the replace function will not replace more
than 9489 occurances in the table. Is there a preset limit for replace? My
table includes 868,000 records with ove 300,000 null spaces to replace in one
fieild.

An update query would be more efficient.

 

['69 Camaro]

Hi.

Is there a preset limit for replace?

No. However, the maximum number of replaced items is dependent upon the
amount of RAM allocated to the Access database application. It's better
(and far faster) to use an UPDATE query to make the needed change. For more
information, please see the tip, "How to 'Find and Replace' a value in as
many records as needed" on the following Web page:

http://www.Access.QBuilt.com/html/queries.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Sascha Trowitzsch]

Hi,

Hi.


No. However, the maximum number of replaced items is dependent upon the
amount of RAM allocated to the Access database application.

I don't believe this to be a RAM-related problem.
I sounds rather as if was the limit for table locks. As the find&replace
code isn't embedded in an implicit transaction, like update queries are, but
is IMO located in an access wizard (acwzmain?..), there will be placed a
lock on every record containing the search expression. This could exceed the
limit for JET locks (MAXLOCKS).

Ciao, Sascha

 

['69 Camaro]

Hi, Sascha.

there will be placed a lock on every record containing the search
expression. This could exceed the limit for JET locks (MAXLOCKS).

In my testing, changing the MAXLOCKS value didn't affect the maximum number
of records that could be replaced at one time with "find and replace." But
I have to admit I didn't test this method extensively because the preferred
method of changing existing data is to use an UPDATE query, and that's what
I use. You should, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.