?
Solved

Code to search for filename and replace

Posted on 2012-08-16
8
Medium Priority
?
543 Views
Last Modified: 2012-08-29
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.
0
Comment
Question by:leezac
  • 5
  • 3
8 Comments
 
LVL 85
ID: 38302229
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
 

Author Comment

by:leezac
ID: 38302528
It is just code  - no tables
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38307574
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:leezac
ID: 38326148
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
 

Author Closing Comment

by:leezac
ID: 38326184
I am going to repost .  Thanks for help.
0
 

Author Comment

by:leezac
ID: 38326210
I reposted at

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

I would greatly appreciate your help.
0
 

Author Comment

by:leezac
ID: 38326952
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
 
LVL 85
ID: 38344919
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question