[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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
0
itcouple
Asked:
itcouple
  • 6
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
You can definitely modify VBA code.   According to Rick, the macro issue is minor.  I use F&R daily ... for years.

mx
0
 
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now