On Jul 1, 8:45=A0pm, "Carlos Nunes-Ueno" <sulla...@[EMAIL PROTECTED]
>
wrote:
> I have a fairly large table (700,000 rows or so) that I'd like to run a
> process. =A0However, the procedure we have right now was designed with
> tables of more like 20,000 rows and isn't able to handle it. =A0Access
wil=
l
> always crash before it can complete.
>
> Some background: the procedure is used in the process of data cleanup
and
> is designed to process company names into a standardized form, so that
we
> can use it to confirm data across various datasets. =A0The procedure
takes=
> input like, for example, "The Yummy and Tasty Waffle Cor****ation" or
> "Yummy & Tasty Waffle, Incor****ated" and turns both into "TASTYWAFFLE".
=
=A0
> We can then sort, link, and filter, etc. on this field along with others
> to see if there are duplicates or check if companies that have different
> IDs are in fact the same company.
>
> Specifically, another procedure takes a specified table and field and
> creates a new field, filling it with the contents of the original field.
> That procedure then p***** a DAO recordset and the name of the new field
> to the main procedure which then performs 11 operations to arrive at the
> "TASTYWAFFLE" stage. =A0Filling the new field is clearly double work and
> I'll be triming that part out.
>
> Now for my specific questions: Currently, the procedure takes the whole
> contents of the field at once, and uses a lot of InStr, Mid, Left, and
> Right functions to perform all of the operations, then moves on to the
> next row. =A0It seems more direct to just read character by charater
until=
> I have a complete word (i.e. I hit a space or other delimiter), process
> that bit, then move on to the next part of the field. =A0Which of these
> approaches is more efficient? =A0Also, where could I go to find some
> guidelines on writing the most scalable VBA code? =A0I know Access has
> limitations, but I'd like to be limited by those and not by our own
> inefficiencies.
>
> Thanks in advance,
>
> Carlos
The functions you name are old and stale; they were always
inefficient. My guess is that Regular Expressions would solve your
problem and be hundreds, possibly thousands of times faster than
straight VBA code. Of course, those who don't know Regular Expressions
may disagree. No doubt, initially Regular Expressions can be
bewildering. But a little work and a modi*** of patience can result in
the tedious being made simple, and the impossible, only challenging.
|
