Solved

MS Access - loop through contents of table and iteratively find/replace words from second table

Posted on 2016-11-27
6
79 Views
Last Modified: 2016-11-27
I have two tables, Table1 and Table2. Table1 has a column called Dump which contains a bunch of string data. I want to iteratively go through the string data in Dump and remove certain reference words that exist in Table2 (these words exist in a column called Word). I'd like to do this programmatically (seems cleaner) but am open to doing it in a query.  I keep getting a variety of errors depending on what I change -- ranging from "member not found" to "two few parameters." Here's the code as it exists now.  
    Dim dbs As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim iDataID As String
    Dim WordData As String

 
    Set dbs = CurrentDb
    Set rs1 = dbs.OpenRecordset("SELECT [Table1].[Dump] FROM Table1")
    Set rs2 = dbs.OpenRecordset("SELECT [Table2],[Word] FROM Table2")
 
  
 rs2.MoveFirst
    Do While Not rs2.EOF
        
         iDataID = rs2.Fields("Word").Value
        WordData = rs1.Fields("Dump").Value
 
        rs1.MoveFirst
        Do While Not rs1.EOF
            
        WordData = Replace(rs1.Fields("Dump"), iDataID, " ")
         
             rs1.MoveNext

        Loop

            rs1.MoveFirst
 
        rs2.MoveNext
    Loop
 
    rs1.Close
    rs2.Close
 
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set dbs = Nothing

Open in new window

I could probably solve this over the next few knuckleheaded days but seems better to ask the experts. Feedback welcomed.

Edit: Code placed in snippet box - GrahamSkan
0
Comment
Question by:alfamikefoxtrot
6 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41903135
can you provide some sample data and expected output.
0
 

Author Comment

by:alfamikefoxtrot
ID: 41903140
Sure --

Original contents of Table1:

ID      Dump
1      this is a test. this is only a test.
2      the quick brown fox jumps over the lazy dog.


Contents of Table2:

ID      Word
1      the
2      this


I want the code to be able to remove from Table1.Dump the words that appear in Table2.Word, resulting in the contents of Table1 now looking like:

ID      Dump
1      is a test. is only a test.
2      quick brown fox jumps over lazy dog.


Of note -- the contents of Table 2 will vary (e.g., different words can be present in table) and there can be anywhere from a couple to 20 or 30 that would need to iteratively be removed.
0
 
LVL 1

Assisted Solution

by:rquaglia
rquaglia earned 25 total points
ID: 41903156
hi!
in the main loop try:
==========
        rs1.MoveFirst
        Do While Not rs1.EOF

        WordData = Replace(rs1.Fields("Dump"), iDataID, " ")
        rs1.edit                                        ' begin record editing
        rs1!dump = wordData              ' modify field
        rs1.update                                  ' update record with new field value
        rs1.MoveNext

        Loop
========
So you can edit the record you are analyzing.
Good work!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 25 total points
ID: 41903163
Try..

CREATE TABLE MSTable1
(
	 ID   INT   
	,Dump VARCHAR(1000)
)


INSERT INTO MSTable1 VALUES
(1     , 'this is a test. this is only a test.'),
(2     , 'the quick brown fox jumps over the lazy dog.')



CREATE TABLE MSTable2
(
	 ID   INT   
	,Word VARCHAR(1000)
)


INSERT INTO MSTable2 VALUES
(1    ,  'the'),
(2    ,  'this')


SELECT t.aID , t.NewCol AS Dump 
FROM 
(
	SELECT a.Dump , a.ID AS aID, b.ID , b.Word , REPLACE(Dump,Word,'') AS NewCol 
        FROM MSTABLE1 AS a , MSTABLE2 AS b
) AS t INNER JOIN MSTABLE1 AS K ON k.Dump <> t.NewCol AND k.ID = t.ID
ORDER BY t.aID

--

Open in new window


Output

aID         Dump
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1            is a test.  is only a test.
2            quick brown fox jumps over  lazy dog.

Open in new window


Hope it helps !!
0
 
LVL 45

Accepted Solution

by:
aikimark earned 450 total points
ID: 41903193
You can do the update with a single Access query:
Update Q_28985668_Table1 
    Inner join Q_28985668_Table2 on [Q_28985668_Table1].Dump Like "*" & [Q_28985668_Table2].Word & "*"
Set [Q_28985668_Table1].Dump = Replace([Q_28985668_Table1].Dump, [Q_28985668_Table2].Word, "")

Open in new window

1
 

Author Closing Comment

by:alfamikefoxtrot
ID: 41903261
Thanks guys -- appreciate the commentary. The Cartesian solution in update query form looks like it's the best after all. Nice and elegant....!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question