How to achieve scalability

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 Corporation" or
"Yummy & Tasty Waffle, Incorporated" 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 passes 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
.

Relevant Pages

  • Re: How to achieve scalability
    ... rules that convert "The Yummy and Tasty Waffle Corporation" into ... the procedure is used in the process of data cleanup and ... filling it with the contents of the original field. ...
    (comp.databases.ms-access)
  • Re: Data Transfer Kit, Can you use it more than once?
    ... Makes no sense to be filling ... up landfills with a one-time transfer cable. ... Is their some sort self- ... destruct mechanism after you transfer one time? ...
    (alt.games.video.xbox)
  • Re: OT: Facebook
    ... Robin Fairbairns wrote: ... filling in a 'phone number, I usually give the organisation in ... I do that sort of thing. ... so tag 9 random digits onto one of those. ...
    (uk.media.radio.archers)
  • Re: OT: Facebook
    ... filling in a 'phone number, I usually give the organisation in ... I do that sort of thing. ... And I don't mind them knowing for general profiling purposes that ... people of my age group want this kind of item or service. ...
    (uk.media.radio.archers)
  • Re: /var/log/reporting
    ... Links indicate its related to HTTP Proxy usage reports: ... has this and it keeps filling up. ... It does sort of rotate out this ...
    (RedHat)