?
Solved

MS Access Search and Replace Using VBA

Posted on 2016-10-06
6
Medium Priority
?
333 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
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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 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 49

Expert Comment

by:Dale Fye
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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