Using VBA to find/replace in entire table

[The_Kurgan]

Greetings, group! I simply would like to use VBA to do a find/replace in an entire table. I'm using the code below which works great for a single column. My question is this: Is there a way to do a find/replace on an entire table (all columns) or will I have to iterate through all columns?

Dim db As Database
Set db = CurrentDb

sSQL = "UPDATE MyTable SET [My Field] = ""Gecko"" WHERE [My Field] = ""Monkey"""

db.Execute sSQL, dbFailOnError

I've tried

sSQL = "UPDATE MyTable SET MyTable.* = ""Gecko"" WHERE MyTable.* = ""Monkey"""

but that doesn't seem to work.


TIA!

 

[james_lankford]

I think having a bunch of sql statements and updating each column separately is the best

and you can use single quotes
to make things easier to read

sSQL = "UPDATE MyTable SET [My Field] = 'Gecko' WHERE [My Field] = 'Monkey' "

 

[The_Kurgan]

I was afraid of that. Was just hoping for something cleaner. For anyone interested, my new code is below:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()

'Read all columns in Loan_Data (via the FldTypes table)
SQL = "SELECT FldTypes.* FROM FldTypes;"
Set rs = db.OpenRecordset(SQL)
cnt = 0
If rs.EOF = False Then
    rs.MoveFirst
    Do While Not rs.EOF
        FldName(cnt) = rs.Fields(0).Value
        MyFldType(cnt) = rs.Fields(1).Value
        rs.MoveNext
        cnt = cnt + 1
    Loop
End If

'Iterate through columns, replacing nulls with the word "blank" etc...
For iter = 0 To cnt - 1
    If MyFldType(iter) = "String" Then
        sSQL = "UPDATE Loan_Data2 SET [" & FldName(iter) & "] = 'blank' WHERE [" & FldName(iter) & "] IS NULL"
    ElseIf MyFldType(iter) = "Double" Then
        sSQL = "UPDATE Loan_Data2 SET [" & FldName(iter) & "] = 0 WHERE [" & FldName(iter) & "] IS NULL"
    ElseIf MyFldType(iter) = "Date" Then
        sSQL = "UPDATE Loan_Data2 SET [" & FldName(iter) & "] = '01/01/1900' WHERE [" & FldName(iter) & "] IS NULL"
    End If
    db.Execute sSQL, dbFailOnError
Next iter

Thanks for the reply!

 

[xenou]

You could also consider a default value but that depends on how the table is populated.

 

[The_Kurgan]

It's being imported from an Excel file.

 

[xenou]

That should still work for NULL if the default is defined in the database. For the record here's the SQL method:

UPDATE 
	Table3 
SET 	
	Table3.TransDate = NZ(Table3.TransDate,#1/1/1900#), 
	Table3.Amount = NZ(Table3.Amount,0),
	Table3.[Desc] = NZ(Table3.Desc, 'blank')

Or using IIF() which is slightly more friendly to ADO connections from other applications:

UPDATE 
	Table3 
SET 	
	Table3.TransDate = IIF(ISNULL(Table3.TransDate),#1/1/1900#,Table3.TransDate), 
	Table3.Amount =  IIF(ISNULL(Table3.Amount),0,Table3.Amount),
	Table3.[Desc] = IIF(ISNULL(Table3.Desc),'blank',Table3.Desc)

You have to beware of cases where, for instance, if zero is imported, then you can't really tell if the original data was zero or if you updated Null to zero. Also Null vs an empty string can sometimes be confusing (for instance, if you delete a string in the table using the delete key, it seems to get recorded as "", not as NULL).

 

[The_Kurgan]

I'm definitely going to play with that code. Thank you!
Very true about original zero vs. manufactured. I think it'll work for my application. Still brain-storming this project...

 

[xenou]

Okay - I fixed a small typo in my code.

 

[Micron]

Wondering if you realize that by not declaring a variable, it is automatically a variant (cnt). In your case, it should work because you initialize it first (cnt = 0) , but one day it may trip you up. Not to mention the increase in memory allocation over declaring an integer or long...