VBA/Access Global Search&Replace from LUT

[simon.robin.jackson]

Ok. I need to develop a macro/vba code to do the following.

>I have a database with 30 tables. Theres a NAME field in nearly all of the tables.

I have been given a spreadsheet with two columns. One contains the NAME of every unique record in the database and the second column is contains either the newly correct NAME or its blank.

I need to automate replacing all of the incorrect NAMEs in the database with the correct NAME entry.

There are at least 300 corrections and its expected for this to happen
a lot more in the future.

Therefore id like a nice button that does this all for me.
In my head the method should go something like this:

1. Import spreadsheet into database and use it as a Look Up Table.
2. Record a macro: Open a table, search&replace for (incorrect) NAME
in target table and replaces with (correct) NAME from LUT. Repeat
for every table. Stop Macro.
3. Step into Macro and somehow adapt it to use each record out of LUT
as a variable to begin with and search and replace with.

Sorry that sounds a bit vague. Im a bit of an amateur with Access &
VBA.

Any pointers on how to do this, or snippets would make my life a lot
happier.

[paii, Ron]

<si*****************@gmail.comwrote in message
news:21**********************************@c65g2000 hsa.googlegroups.com...

Ok. I need to develop a macro/vba code to do the following.

I have a database with 30 tables. Theres a NAME field in nearly all of

the tables.

>

I have been given a spreadsheet with two columns. One contains the NAME

of every unique record in the database and the second column is contains
either the newly correct NAME or its blank.

>

I need to automate replacing all of the incorrect NAMEs in the database

with the correct NAME entry.

>
There are at least 300 corrections and its expected for this to happen
a lot more in the future.

Therefore id like a nice button that does this all for me.
In my head the method should go something like this:

1. Import spreadsheet into database and use it as a Look Up Table.
2. Record a macro: Open a table, search&replace for (incorrect) NAME
in target table and replaces with (correct) NAME from LUT. Repeat
for every table. Stop Macro.
3. Step into Macro and somehow adapt it to use each record out of LUT
as a variable to begin with and search and replace with.

Sorry that sounds a bit vague. Im a bit of an amateur with Access &
VBA.

Any pointers on how to do this, or snippets would make my life a lot
happier.

You don't need any VBA to do this, assuming your spread sheet is clean.
1) Link to the spread sheet, it will look like a table to Access.
2) Create an update query linking your Access table to the Spread sheet.
Filter out any names that are blank.
3) Run the query whenever the spreadsheet is updated.

PS: Why do you have 30 tables with the same Name information. You should
have 1 table with the names and a key, then reference the key in the other
tables. You can also eliminate the update step by supplying a form linked to
your names table to whoever is updating the names.

[simon.robin.jackson]

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).
I too thought I could create an update query but I was wrong -
I tried adding all the tables to a query.
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.

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

Please advise.

[simon.robin.jackson]

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).
I too thought I could create an update query but I was wrong -
I tried adding all the tables to a query.
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.

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

Please advise.

[paii, Ron]

<si*****************@gmail.comwrote in message
news:2f**********************************@x41g2000 hsb.googlegroups.com...

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.

[simon.robin.jackson]

I have no control over the database structure - its a clients and I
cant really mess with it, I just need to be able to
Find and replace data across multiple tables.

I thought id be able to do this with one update query, but like you
mention...

>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.

Didnt really fancy setting up loads of queries - Because each table
has three or so fields within it that need checking for old ID values
and replacing with new ID values. Therefore each table itself may
need 3 separate update queries, 30 table = lots of time consuming
queries. Id like to leave this as a last resort.

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.

I have found snippet of code that finds and replaces, but I need to
automate
Opening a target table, opening the Look Up Table, loop through the
Look Up Table to feed into the S&R function the Old And New IDs, then
move onto the next record until its finished. Then move onto the next
table.

I imagine this may take awhile but the tables contain around 7000
records each, so memory shouldnt be an issue and not fussed about time
as long as it works.
Manually, if I select everything in a table and S&R, it seems to be
pretty quick and cant imagine there being any errors.

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.

Good points - strengthens the case to come up with some VBA code to
S&R?

Thanks for your help so far, please dont leave me!

[paii, Ron]

<si*****************@gmail.comwrote in message
news:2b**********************************@y38g2000 hsy.googlegroups.com...

I have no control over the database structure - its a clients and I
cant really mess with it, I just need to be able to
Find and replace data across multiple tables.

I thought id be able to do this with one update query, but like you
mention...

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.

>
Didnt really fancy setting up loads of queries - Because each table
has three or so fields within it that need checking for old ID values
and replacing with new ID values. Therefore each table itself may
need 3 separate update queries, 30 table = lots of time consuming
queries. Id like to leave this as a last resort.

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.

I have found snippet of code that finds and replaces, but I need to
automate
Opening a target table, opening the Look Up Table, loop through the
Look Up Table to feed into the S&R function the Old And New IDs, then
move onto the next record until its finished. Then move onto the next
table.

I imagine this may take awhile but the tables contain around 7000
records each, so memory shouldnt be an issue and not fussed about time
as long as it works.
Manually, if I select everything in a table and S&R, it seems to be
pretty quick and cant imagine there being any errors.

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.

Good points - strengthens the case to come up with some VBA code to
S&R?

Thanks for your help so far, please dont leave me!

Queries would be the quickest and safest way to do this. Use VBA and
transactions to run the queries to prevent only some of the tables updating.
If you don't want to store the queries, you could build them in code as part
of your VBA. If each table has 3 name field to update you will need 90
queries, with 7000 records per table or 630,000 possible updates so memory
could be an issue.

You could use recordsets to update 1 table at a time but that would be
harder then queries and much slower.

Add some code to verify the update table before you start updating your
data.

I hope the client understands the structure will cause inconsistent data.
UMO the time you spend doing this project and they spend fixing data would
be better spent fixing the structure.