Find & Replace in multiple tables - or Query?

[RobJDB]

I have an Access DB with over 40 tables, and in each table, any field may contain a particular character code which I need to replace with the character itself.
What I effectively need is a Find & Replace that'll sweep across every field in every table in one go, or a query perhaps.
Does anyone know how I should do this?

 

[RickSpr]

You'll need to write a VBA procedure for this. Here's an outline:
1. In the outer loop, use For Each to enumerate the tables in the current database, setting a TableDef object variable. Remember to skip any system tables.
2. In a second-level loop, open a Recordset on the table and loop through the rows using Edit and MoveNext methods until the Recordset's EOF property is True.
3. In a third-level loop, enumerate the Fields collection of the recordset. Skip any fields that are not of type dbText or dbMemo.
4. In the innermost loop, use InStr() to test for your character code, and replace each instance with the character you want.
5. Finishing the second-level loop, if you made any changes to the record, call the recordset's Update method before you call MoveNext. (You could actually call the Update method whether or not you made changes, but it will execute more slowly if you do.)

If you're not familiar with DAO programming, the help topics you'll need to study are:
CurrentDb() function
Database object
TableDefs property
OpenRecordset method
TableDef object
Attributes property
Recordset object
EOF property
Edit method
Update method
MoveNext method
Fields property
Field object
Type property
Value property
To scan the field values for your character code, you'll need to use the functions Instr(), Left$(), Mid$(), and Right$(). If you're using Access 2000 or later, you can use the Replace() function instead. Rick Sprague

 

[RobJDB]

Thanks Rick. That gives me plenty to be getting on with. I'm not very familiar with DAO programming, so the list of topics you provided is very useful. I should be able to get the result I want now.
One further question - and this probably should be obvious to me - but how do I actually program within Access? Is that what I need to do? I'm not much good with Access and normally I just write ASP to manipulate databases and run it from PWS.
Thanks again,

Rob

 

[RickSpr]

I, on the other hand, have no idea how you use ASP to manipulate Access databases, nor do I know anything about programming with ASP. Maybe it's possible you could do all this in ASP, but the only help I could offer is that you'd need to reference the DAO type library.

If you want to tackle programming in Access (VBA)--and if you use Access or Office a lot, I'd recommend it highly--you should probably pick up one of the aftermarket books, like the For Dummies series, that covers Office VBA programming.

If you've got enough programming experience to start at a more intermediate level, then just create a standard module in Access and hit the F1 key. Once you're in programming mode, the help system will display VBA topics (assuming you've got the VBA help file installed). Or, if you've got the MSDN library (every professional developer should have that), the introductory topics in the Office VBA branch will get you started. Rick Sprague

 

[RobJDB]

Thanks for that Rick. It's high time I got into VBA and stopped depending on ASP. You've been a great help.

Rob