Replacing text throughout a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Relative newbie to Access VBA here -- what's the best way to search
throughout a table and replace a text string with another string that can
vary and will be supplied by the code (in my case, the filename that some
text was just imported from)?

I investigated Update Queries, but they seem to either need a definite
string to replace with, or require user interaction (unless there's a way to
supply the string via code at the time the Update Query is called...?).

I know the Replace() function is a possibility (albeit perhaps a slower
one), but I don't yet know how to specify each field of each record in a
table as a string to do the Replace() comparison. My record set will be
about 100,000, at it's max, so speed is not necessarily a significant factor.

Can anyone point me to the easiest method of doing this? Thanks.
 
Matthew Bell said:
Relative newbie to Access VBA here -- what's the best way to search
throughout a table and replace a text string with another string that
can vary and will be supplied by the code (in my case, the filename
that some text was just imported from)?

I investigated Update Queries, but they seem to either need a
definite string to replace with, or require user interaction (unless
there's a way to supply the string via code at the time the Update
Query is called...?).

I know the Replace() function is a possibility (albeit perhaps a
slower one), but I don't yet know how to specify each field of each
record in a table as a string to do the Replace() comparison. My
record set will be about 100,000, at it's max, so speed is not
necessarily a significant factor.

Can anyone point me to the easiest method of doing this? Thanks.
Click to expand...

You can build and execute an update query on the fly, using the Replace
function to replace one string with another. For example,

'----- start of example code -----

Dim strSQL As String
Dim strOldString As String
Dim strNewString As String

strOldString = "abcde"
strNewString = "fghij"

strSQL = _
"UPDATE MyTable " & _
"SET MyField = Replace(MyField, '" & _
strOldString & _
"', '" & _
strNewString & _
"')"

CurrentDb.Execute strSQL, dbFailOnError

'----- end of example code -----
 
Thanks -- I'm almost there. If I just replace "MyTable" in your code below
with the name of the table I want to use ("Site_Survey"), I get a run-time
error 3061, "Too few parameters, expected 1"

Do I need to refer to the table indirectly via a string variable, or
reference it in the context of the database (loosely like
Currentdb.Table("Site_Survey")) or some such?
 
Matthew Bell said:
Thanks -- I'm almost there. If I just replace "MyTable" in your code
below with the name of the table I want to use ("Site_Survey"), I get
a run-time error 3061, "Too few parameters, expected 1"

Do I need to refer to the table indirectly via a string variable, or
reference it in the context of the database (loosely like
Currentdb.Table("Site_Survey")) or some such?
Click to expand...

Did you also replace "MyField" with the name of the field you want to
update? You'll get that error message if the query to be executed
contains an unresolved parameter, or any unrecognized name that it
*thinks* must be a parameter. So make sure you've properly spelled the
table name and all fields that you refer to.
 
That was it -- thanks. I'm still learning what are reserved words and what
are sample parameters, but something called "MyField" should have tipped me
off. Thanks.
 
Back
Top