Code to search for filename and replace

I need a way to search for //ABCD and replace with ABC1 in multiple databases?  Is there a way to use one database to open other databases?  Or just insert code into each database to do the search.  I am need a quick way for many databases to do and find and replace.
leezacAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
So you need to search for the filename IN VBA CODE?

If so, you there is no quick and easy way to do this. The best solution is something like Rick Fisher's Find and Replace (www.rickworld.com), but you can't automate that.

You can get to the VBA code in various ways, but it's fraught with issues, and can leave your databases hopelessly corrupt (and definitely un-compile them). There's a code snippet on this post that shows how to do that:

http://stackoverflow.com/questions/865530/replace-module-text-in-ms-access-using-vba

You'd have to change it slightly to work with an automated Access object. Change the function header to this:

Public Function SearchOrReplace(ByVal Module As Access.Module, ByVal StringToFind As String, _
Optional ByVal NewString, Optional ByVal FindWholeWord = False, _
Optional ByVal MatchCase = False, Optional ByVal PatternSearch = False) As Boolean

And then changed this line:

Set mdl = Modules(ModuleName)

To this:

Set mdl = Module

Now call the SearchOrReplace function like this:

Dim oAcc As Access.Application
Set oAcc = New Access.Application

oAcc = OpenCurrentDatabase("Full path to your external db")

Dim mdl as Access.Module

For each mdl in oAcc.Modules
  SearchOrReplace mdl, "StingToFine", "NewString" etc etc
Next
Dim oAcc As Access.Application
oacc.Modules
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you replacing DATA? That is, you need to update a Table?

Or are you trying to find this in Code?

If it's just Data, you can simply connect to the database and update as needed:

Dim dbs As DAO.Database
dbs = OpenCurrentDatabase("YourDatabasePath")

dbs.Execute "UPDATE MyTAble blah blah"
0
 
leezacAuthor Commented:
It is just code  - no tables
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
leezacAuthor Commented:
LSM Consulting - sorry I had been out and did not see your reply until today.   Does the code above search modules only?  If so, do I need to know the modules name?  I do not want to open each database, but scan for any VBA to find a certain string.  Thank you.
0
 
leezacAuthor Commented:
I am going to repost .  Thanks for help.
0
 
leezacAuthor Commented:
I reposted at

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27840140.html

I would greatly appreciate your help.
0
 
leezacAuthor Commented:
LSM - I am really having trouble trying to figure out how to call the function to make it work.....
Can you help please?

Public Function searchorreplace(ByVal Module As Access.Module, ByVal StringToFind As String, _
Optional ByVal NewString, Optional ByVal FindWholeWord = False, _
Optional ByVal MatchCase = False, Optional ByVal PatternSearch = False) As Boolean


Set mdl = Module

'Now call the SearchOrReplace function like this:

Dim oAcc As Access.Application
Set oAcc = New Access.Application

oAcc = OpenCurrentDatabase("P:/")

Dim mdl As Access.Module

For Each mdl In oAcc.Modules
  searchorreplace mdl, "Dim" '"NewString"
Next
Dim oAcc As Access.Application
oAcc.Modules
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Add the SearchOrReplace code to a standard Module (name the module something like basSearch).

In another function, do this:
Function ChangeCode As Boolean
  Dim oAcc As Access.Application
  Set oAcc = New Access.Application

  oAcc = OpenCurrentDatabase("Full path to your external db")
 
  Dim mdl as Access.Module

  For each mdl in oAcc.Modules
    SearchOrReplace mdl, "StingToFine", "NewString" etc etc
  Next

  Set oAcc = Nothing
End Function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.