Re: VBA/Access Global Search&Replace from LUT


<simon.robin.jackson@xxxxxxxxx> wrote in message
news:2f511a6c-e66f-412f-91a1-2e707db8e8d0@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I should have better described this in my original thread - apologies
I was trying to simplify it so it would be easier to read/comprehend.

Lets start over.

I have 30 tables that have a field that contains an ID (descriptive
ID, text strings of up to 50 characters).

You will simplify the problem by only having the ID field in the 30 tables,
use a join table with the ID and Descriptive ID. If any chance the ID can
change, use a AutoNumber primary key in the new join table to be used to
link your 30 tables. This Key would never be displayed to a user, changes to
the ID would only happen to 1 record.

I too thought I could create an update query but I was wrong -
I tried adding all the tables to a query.

With your structure you will need 30 update queries, one for each table. The
only VBA required would be to run each of the 30 queries. This of would need
to be run inside a transaction so if one failed no changes would be saved.


I then joined them all to the LUT which I imported into Access.
(joined on the corresponding ID field)
I then did an update query to replace the values.

However, what I have now realised is that other fields also contain
these ID names, but NOT in the same record.

Basically this is for a mapping project and each record is a river
catchment. The ID is unique to that catchment. However, there is
also a field which is Upstream_ID and Downstream_ID, and these too
need replacing with the corrected names. In fact there are a series
of different fields that have ID Names in them but do not correspond
to the actual ID for that record.

So if I were to do it via code, you could do a find and replace for
all fields in all tables and loop through the LUT feeding the Search
string to be the Original ID, and the replace variable to be the
Corrected ID.

There is no find and replace feature for data in multiple tables. You could
code some VBA to open 31 record sets to do the lookup and edit, but that can
be very slow running and pron to error.


Pretty sure that would be the best way to go about it, but not too
familiar on how to actually implement it.

Please advise.


What are the 30 tables? Your current structure allows one upstream and one
downstream? What happens if a river has 2 upstream; add a 2nd upstream ID
and Name then edit your update loop? Lookup normalization, it will help you
in the long run.


.

Relevant Pages

  • Try again
    ... Maybe I gave too much info in my post so I'll simplify it. ... crashes when I open and close a table, so that I can't edit a table or ... Mark A. Sam ...
    (microsoft.public.access.tablesdbdesign)
  • Re: GUI question
    ... let me simplify the problem asked. ... I am designing a GUI with ... into Edit Text. ...
    (comp.soft-sys.matlab)