Find and Replace query from a 2nd table

[elightbox]

Hi, I'd be grateful for any assistance.

I am using Access 2003, but I also have Access 2007.

I have a table (Categories) with a column "descriptions". This column has html code (see example below) including several urls per field (1000 rows not all unique. some fields might be duplicated in several rows)

I have a second table "redirects" with two columns, "Old-URL" and "New-URL" (600 rows)

the two tables are completely independent.

I want to run a find and replace on the first table to find EACH "OLD-URL" from the second table in the "descriptions" column of the first table and replace it with the "new-URL" from the second table.

Example:
In the example below there are TWO old urls in this field, each would need to be found and replaced with the new-url1 and new-url2 from the "redirects" table.



<table border="0" width="600" id="table1" cellspacing="0" height="156"><tr><td><img border="0" src="images/xxx.jpg" width="600" height="175"></td></tr><tr><td class=topbanner>blah blah blah blah<br>
<span class="Content">Explore item 1.&nbsp;.<br><a href="/url1-category-109.htm">old-URL1</a>&nbsp;&nbsp;▪ &nbsp;<a href="/item2-category-343.htm">old-URL2<a> </span></td></tr></table>

I started doing this manually, but after 20 rows my wrist is hurting

[ajetrumpet]

check out this faq that I wrote a while back: http://www.access-programmers.co.uk/...ernet+explorer

There is a coding example in the "Automation Sample" section that has section of code in it that you could possibly use:

'PICK OUT THE ACTUAL LINKS TO COMPANY WEBSITES ONLY ON THE DIRECTORY PAGES
For Each C In ie.Document.links
   If InStr(C, "http://") > 0 And InStr(C, "?") = 0 And InStr(C.innerhtml, "Visit Web Site") > 0 Then
      rs.AddNew
      rs!listing = C
      rs!city = "Iowa City"
      rs!state = "IA"
      rs!Type = Mid(address, (InStr(address, "-IA/") + 4), (InStr(address, "?") - (InStr(address, "-IA/") + 4)))
      rs.Update
   End If
Next C

Next x

NextLoop:
rs2.MoveNext
Loop

You are going to need to do something similar to it. What I did there is chose some benchmarking points in the webpage's HTML code that were stable (e.g. - could always refer to them in any situation) and used the LEFT(0, RIGHT(), and INSTR() functions to parse out the values I wanted.

In your situation you're going to have to:

*find the starting point for the "<href=" string
*grab a string that starts from the above point and ends at the closing tag for the "href", and then substract the trailing constant for the tags (usually just 1 for the ">" character.
*use the find and replace (or the .edit option in a recordset) to put the new values in.

Here is a short example for you to build on:

<table border="0" width="600" id="table1" cellspacing="0" height="156"><tr><td><img border="0" src="https://www.accessforums.net/images/xxx.jpg" width="600" height="175"></td></tr><tr><td class=topbanner>blah blah blah blah<br>
<span class="Content">Explore item 1.&nbsp;.<br><a href="/url1-category-109.htm">old-URL1</a>&nbsp;&nbsp;▪ &nbsp;<a href="/item2-category-343.htm">old-URL2<a> </span></td></tr></table>

The sample function for solving the above problem could be similar to:

dim NumURLs as integer 'NUMBER OF URLS IN THE NEW TABLE (MAX OF 2)
dim ctr as integer 'GENERAL COUNTER
dim LinkStart as long 'START POS OF THE LINK'S HTML CODE
dim LinkEnd as long 'END POS OF THE LINK'S HTML CODE
dim NewString as string 'New URL inside of quote marks from the new table
dim OldString as string 'Old URL inside of the quote marks from the old table

'open recordsets here
'for example - NEWRS & OLDRS

NumLinks = iif(instr( _
             instr(OLDRS.fields("fieldname"), "<a href") + 1, _
             OLDRS.fields("fieldname"), "<a href>") = 0, 1, 2)

for ctr = 1 to NumLinks

      if ctr = 1 then
            LinkStart = instr(NEWRS.fields("fieldname"), "<a href>") + 9
            LinkEnd = instr(LinkStart + 1, _
                      NEWRS.fields("fieldname"), _
                      ">") - 2

            RSOLD.fields("fieldname") = _
                  Replace(RSOLD.fields("fieldname"), _
                  'STRING INSIDE THE "HREF" QUOTES FROM THE RSOLD table, _
                  mid(NEWRS.fields("fieldname"), LinkStart, _
                  ((LinkEnd - LinkStart) + 1)))

      else

            'YOUR DOING THE SECOND URL HERE INSTEAD

      end if

next ctr

Make sense? The code obviously needs to be written, I just gave you an example of how to do the difficult parsing tasks. There is always the option too of restructuring data so you can update your information another way rather than go through this everytime...