Re: VBA/Access Global Search&Replace from LUT

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

Relevant Pages

  • Re: VBA/Access Global Search&Replace from LUT
    ... Didnt really fancy setting up loads of queries - Because each table ... pretty quick and cant imagine there being any errors. ... Good points - strengthens the case to come up with some VBA code to ... UMO the time you spend doing this project and they spend fixing data would ...
    (comp.databases.ms-access)
  • Re: Inserting data from 2 tables into 1
    ... been busy with Access and VBA for about 6 ... I would make all the queries and then use a set of macros to ... > Do you perhaps mean look for duplicates in tbResultsTemp? ... > The first is easier and lets you use all the nice interfaces like query ...
    (microsoft.public.access.modulesdaovba)
  • Re: Data Changing Issue
    ... *if* you are in VBA. ... think you were using either VBA code, either a query.) ... is very unlikely to print some value of myid present in table1... ... I am running some queries using a form I created and for some strange ...
    (microsoft.public.access.queries)
  • Re: a guide/reference to queries via VBA
    ... What I'm planning to do is to re-write some of my queries in VBA as modules ... > Dim qdfCurr As QueryDef ... > Set dbCurr = CurrentDb ...
    (microsoft.public.access.queries)
  • Re: Tough One: displayalerts = false while OUTSIDE VBA
    ... AFAIK you can't set this property to False outside VBA. ... Can anyone think of a way to disable alerts on background queries ... > when the Excel application is in control. ... > I have a vba routine that generates potentialy thousands of web ...
    (microsoft.public.excel.programming)