Solved

MS Access Search and Replace Using VBA

Posted on 2016-10-06
6
135 Views
Last Modified: 2016-10-06
I have a Microsoft Access 2010 database.

In it I have a table named DEADLINES.  In the table DEADLINES, I have a field named ACTIONDUE.

I want VBA (visual basic) code to go through the field ACTIONDUE and make the following changes.
   Search for the word "REQ" and replace it with REQUEST (Anywhere in the field)
   Search for the word "REISSUE DL" and replace it with "REISSUE DECLARATION" (Whole field)

I'd prefer to do this using VBA instead of in SQL because I have about 75 changes to make.

Can anyone help me?

Thanks in advance!

LDMueller
0
Comment
Question by:Senniger1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Expert Comment

by:Rajesh Joshi
ID: 41831948
Instead write a query like this
Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REQ","REQUEST")
Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REISSUE DL","REISSUE DECLARATION")

It will take same time as VBA.
you can use excel, to create 3 columns
1)Old String 2) New String 3) SQL statement formula from col. 1& 2.
then copy paste one by one from 3rd column.
0
 

Author Comment

by:Senniger1
ID: 41831970
I can be open to this, however, I pasted the following in my SQL window and ran it.  I get Syntax error (missing operator) in query expression.

Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REQ","REQUEST")
Update DEADLINES set  ACTIONDUE = replace(ACTIONDUE,"REISSUE DL","REISSUE DECLARATION")

If I only paste one line at a time they work individually.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41832014
If I only paste one line at a time they work individually.

Yes. Access SQL can only handle one update command per query.

/gustav
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 41832025
your best bet would be to create a translation table (tblTranslate) with fields "ActionDue" (make this the PK in the table) and "ActionDueNew".  Fill in all of those values for the 70+ changes you need to make.  

Then create a single query to select the values from the original table an this new table, something like:

SELECT Deadlines.ActionDue, tblTranslate.ActionDueNew
FROM Deadlines 
INNER JOIN tblTranslate 
ON Deadlines.ActionDue = tblTranslate.ActionDue

Open in new window

Once you have that query, attempt to manually change one of the ActionDue values, to determine whether the query is updateable.  If it is, you should be able to simply modify the SELECT query to an UPDATE query and enter tblTranslate.ActionDueNew in the Update To cell of the query grid.

If, however, the SELECT query is not updateable, you could use VBA to create a recordset and loop through the recordset, something like:

Dim db as dao.database
Dim rs as dao.recordset
dim strsql as string

strsql = "SELECT ActionDue, ActionDueNew FROM tblTranslate"
set db = currentdb
set rs = db.openrecordset(strsql)
while not rs.eof
    strSQL = "UPDATE Deadline SET [ActionDue] = '" & rs!ActionDueNew & "' " _
                 & "WHERE [ActionDue] = '" & rs!ActionDue & "'"
    db.execute strsql, dbfailonerror
    rs.movenext
wend

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 41832030
The problem with using the Replace() function in this context is that you could have a value where the value "REQ" shows up in more than one record type for that field.  So if you already have values for:

REQ
REQUEST

Then running the replace command against those to records would return
REQUEST
REQUESTUEST
1
 

Author Closing Comment

by:Senniger1
ID: 41832568
This was the best suggestions, however, I decided to go another route.
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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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