perform 'smart' find and replace on existing VBA code

Hi

I have migrated MS Access db to SQL Server 2008 and come across 'identity' errors and I found out that I just need to change connection type,option so:

Before migration: CurrentDb.Execute ("update tbl set field = 1")

After fix is applied: CurrentDb.Execute "update tbl set field = 1" , dbSeeChanges + dbFailOnError

In the application I got there are hundreds of places where it needs changing so I'm after an easy solution. I used find replace on similar examples where the code was variable but in this case I need to find the line starting with 'Currentdb.execute (' and remove ( and replace closing ) at the end of line with ', dbSeeChanges + dbFailOnError'

Any ideas how to do that? I think there is a way to amend code in VBA dynamically which I think might work I also could export everything and run some VBA on the export files but what I'm after is quick and realiable way(manual interventioned reduced to minimum) to make the changes so I can also repeat it in the future (I will have to perform migration again in the near future once FE changes are finished)

Many thanks in advance
Emil
LVL 10
itcoupleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If I had to do that, I would be using:

http://www.rickworld.com/products.html#Find%20and%20Replace%209.0

which I have been since Access 1.1

mx
0
Patrick MatthewsCommented:
I second MX's suggestion.  That is a spectacular product, and something every Access developer should have.

No points, please :)
0
itcoupleAuthor Commented:
Sounds like a very good solution.... Iit seems it won't work with 2007 so I just have to find Access 2003 version and test it :)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Ahh ... here is the A2007 version, sorry:

http://www.rickworld.com/beta

Do not let the 'beta' fool you ... it's ready to go.

mx
0
itcoupleAuthor Commented:
Many thanks for the info. I've read that 'macros can only be searched, not modified' Does it mean I can still modify VB code and they refer to macros as in 'access objects'?

I will have a go with this version anyway and see how it goes :)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You can definitely modify VBA code.   According to Rick, the macro issue is minor.  I use F&R daily ... for years.

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itcoupleAuthor Commented:
Thanks, I will have a go soon :)
0
itcoupleAuthor Commented:
Hi
I installed the software and played with it. But I cannot find the option to perform 'smart' replace

Before migration: CurrentDb.Execute ("update tbl set field = 1")
After fix is applied: CurrentDb.Execute "update tbl set field = 1" , dbSeeChanges + dbFailOnError

Basically I would like to find Currentdb.execute (" and remove ( and then and the end of the command replace ) with , dbSeeChanges + dbFailOnError

Can this sofware do that?

Many thanks in advance.
Emil
0
itcoupleAuthor Commented:
Hi

I have googled alternative way and it seems it worked so just for knowledge sharing I add it below.
Public Sub SmartFindReplace()
    On Error Resume Next
    Dim VBAEditor As VBIDE.VBE
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule

    Const strFIND_TEXT As String = "CurrentDB.Execute ("
    Const strREPLACE_WITH As String = "CurrentDB.Execute "
    Const strADD_TO_END As String = ", dbSeeChanges + dbFailOnError"

    Dim strText As String
    Dim i As Long
    Dim iTextLength As Integer
    Dim iTest As Integer


    Set VBAEditor = Application.VBE
    Set VBProj = VBAEditor.ActiveVBProject

    For Each VBComp In VBProj.VBComponents
        
        Set CodeMod = Nothing
        Set CodeMod = VBComp.CodeModule

        For i = 1 To CodeMod.CountOfLines
        
            strText = CodeMod.Lines(i, 1)
        
            If InStr(1, strText, strFIND_TEXT, vbTextCompare) > 0 Then
                'text found
                iTest = iTest + 1

                strText = Replace(strText, strFIND_TEXT, strREPLACE_WITH, , , vbTextCompare)
                
                'check if last character is )
                iTextLength = Len(strText)
                If InStr(iTextLength, strText, ")", vbTextCompare) = iTextLength Then
                    'ok
                    strText = Left(strText, iTextLength - 1) & strADD_TO_END
                    Stop
                    With CodeMod
                        .DeleteLines i
                        .InsertLines i, strText
                    End With
                    
                   Stop
                   
                End If
                
            End If
        
        Next


    Next


MsgBox iTest

End Sub

Open in new window

0
itcoupleAuthor Commented:
I just need to test if the app is still working :p
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.