I have a fairly large table (700,000 rows or so) that I'd like to run a
process. However, the procedure we have right now was designed with
tables of more like 20,000 rows and isn't able to handle it. Access will
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. The procedure takes
input like, for example, "The Yummy and Tasty Waffle Cor****ation" or
"Yummy & Tasty Waffle, Incor****ated" and turns both into "TASTYWAFFLE".
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. Filling 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. It 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. Which of these
approaches is more efficient? Also, where could I go to find some
guidelines on writing the most scalable VBA code? I know Access has
limitations, but I'd like to be limited by those and not by our own
inefficiencies.
Thanks in advance,
Carlos
|
