How can I perform Find and Replace to a portion of data on a large scale?

[Rose427]

I am working with a large database that contains names and addresses. I have been asked to create a routine in Access that would be used to standardize specific words in the address such as St, Str, St. (Street) and Ave, Ave. (Avenue) and more by doing a find and replace. I have tried creating an update query, but ended up replacing the whole field instead of the targeted word. I would like to try creating a macro that could do the find and reqplace, but need help with this. This routine would be used on a regular basis on approximately 500,000 records.

[Rabbit]

An update query would be faster. You probably just wrote it wrong. Post what you tried and we can help you fix it.

[Mihail]

I am not sure that can be done using an update query because the request is to replace some words in sentences (if I understand well).

See if the attached database fit your needs.

PS
Even if you will enjoy this solution, do not select this post as the best answer (forum policy)

[Rose427]

Rabbit, I entered "* St *" in the criteria and "* Street*" for the Update and I ended up with * Street* entered in the entire field.

Mihail, thanks for your suggestion, but I haven't worked with anything like this before. I am just beginning to learn VB. If I create a db with the code you given me, I assume I can then create a macro to run it. This would need to be run once a month. Thank you

[Mihail]

Unfortunately seems that I understand well your request (to replace some abbreviations in the sentences with a single word).
Again unfortunately, I can't see a way to do this without using VBA.

@Rose427
"If I create a db with the code you given me, I assume I can then create a macro to run it."

No need to create a macro. The VBA code run itself as you can see (if you can, based on your skill in VBA) in my database.

Good luck !

[Rabbit]

Yeah, you definitely did it wrong.

UPDATE tableName
SET fieldName = REPLACE(fieldName, " st ", " street ")
WHERE fieldName LIKE '* st *'

The last line is optional. It's there to make it run faster.

[Rose427]

I entered the above code, but I am getting the following message: "This action or event has been blocked by disabled mode." I looked it up and found the following: "It is probably the Trust Centre settings. You need to alter these to allow the code to run rather than be blocked. You normally get a pop up message below the ribbon menus that you sleect to allow the content." I have checked for this and cannot tell what needs to be reset. Any thought?

[Rose427]

Mihail, I don't understand VBA code to use it. I have looked at the code that was supplied, but don't know how to use it (or where to put it.)

[Rabbit]

There should be a banner near the top of the window that will allow you to enable it.

http://office.microsoft.com/en-us/access-help/stop-disabled-mode-from-blocking-a-query-HA010256403.aspx

[Mihail]

I'm sorry to heard this.
I can do this for you in minutes but is a bad idea because you will not be able to maintain your database if something will going wrong.

In your situation I think that can be a good idea to export your table to Excel.
Then (in Excel) you can record a macro with Find and Replace in order to run it when you need.
Then re-import the table in Access.

About your security issue:
Make a folder somewhere in your PC. Say you name it TCA (Trust Center for Access)
Open Access (or a database)
Click the Office button (Top-Left in Access window).
Then click Access Options. this will open the Access Options window.
Go to tab Trust Center.
Click Trust Center Settings...
Go to tab Trusted Locations.
Click Add new location... This will open the Microsoft Office Trusted Location window.
Check Subfolders of this location are also trusted then click Browse....
Brows your TCA folder.
Ok - Ok - Ok ....
From now, any database that contain code will run the code if it is in folder TCA or in a TCA's subfolders.

Note this steps because they are also available for Excel in order to run code in workbooks.

Cheers !

[Rose427]

I have been able to clear the message, but have found that my update query is still not working properly. It seems like what I am trying to do is very simple, but obviously I am doing something wrong. Here is an example of the data that I am trying to update:

PHYSICAL_ADDRES S

6410 SWOPE PKWY
GUNNISON RD
9610 LINKMEADOW LN
1717 SUPERIOR AVE
44-04 QUEENS BLVD
101 AHEARN FIELD
251 N ILLINOIS ST.
160 OLD TOWER HILL Rd
1525 NW 3RD ST
1601 PERDIDO ST Apt 12
15000 CONFERENCE Rd
270 1ST STR
312 WESTOVER RD
1070 US HgWY 46

There are about six to eight abbreviations that I will need to change. Again, what here is what I have entered; "* Ave *" and in the Update field, I am entering "* Avenue ". Any help you can give me will be much appreciated.

[Rabbit]

That's because you've ignored the SQL I posted and continue to use the SQL that you know is incorrect.

[Rose427]

Rabbit, I did try the code you gave me above, I enter it as SQL and then ran it.

UPDATE tableName

SET fieldName = REPLACE(fieldNa me, " st ", " street ")

WHERE fieldName LIKE '* st *'

I again got the following message "The action or event has been blocked by Disable Mode."

To fix this problem, I have tried adding the path to my db to trusted site, but that didn't work. I also tried creating a certificate which didn't work either.
I am grasping at straws now. I need to have this ready to use for tomorrow. Very fustrating.

[Rabbit]

So you didn't read that link I posted then? Because it shows a different way of enabling it.

[Rose427]

Rabbit and Mahail, Thank you both for your patience with me. I finaly got both of your suggestions working. I will be using Rabbit's code as it worked best.