Removing References to Other DBs at Runtime. 1

[Ed2020]

Hi,

I have some code (pasted below) to search the modules in another database for instances of keywords. The code adds a reference to the target database, performs the search, and then removes the reference (References.Remove ref)

The line to remove the reference isn't working for some reason, so as I check one database after another my source DB is having more and more references added. This is creating naming conflicts as I have over 5000 databases to analyse many of which share the same VB Project name.

Can anyone shed any light on why the reference removal isn't working please?

Private Function IsCodeAccess() As Boolean
    Dim ref As Access.Reference
    Dim oVBE As Object
    Dim mdl As Object
    Dim blnFound As Boolean
    Dim StartLine As Long
    Dim StartColumn As Long
    Dim EndLine As Long
    Dim EndColumn As Long
    Dim astrKeyWords() As String
    Dim pntr As Integer
    
    blnFound = False
    astrKeyWords = Split("<keywords list here>", ",")
    
    Set ref = References.AddFromFile(m_dbsToAnalyse.Name)
    
    'To make life easier and lines shorter.
    Set oVBE = Application.VBE.VBProjects(ref.Name).VBComponents

    'Check each module ...
    For Each mdl In oVBE
        'for the required procedure ...
        StartLine = 1
        StartColumn = 1
        EndLine = oVBE(mdl.Name).CodeModule.CountOfLines
        EndColumn = 60
        For pntr = 0 To UBound(astrKeyWords())
            If oVBE(mdl.Name).CodeModule.Find(astrKeyWords(pntr), StartLine, StartColumn, EndLine, EndColumn) Then
                blnFound = True
                GoTo ExitHere
            End If
        Next pntr
    Next
    
    References.Remove ref

ExitHere:
    IsCodeAccess = blnFound
End Function

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

You may try this:

...
[!]Set mdl = Nothing
Set oVBE = Nothing[/!]
References.Remove ref
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ed2020]

Thanks PHV,

I've added those two extra lines but I'm still getting the same problem... :-(

Ed Metcalfe.

Please do not feed the trolls.....

 

[Hap007]

Hi Ed,

It appears that when you find a key word match, you set your flag to true and go to the routine exit, which bypasses the remove ref command.

Might this be your problem?

Question, is the command failing and giving you an error or are you just having additional references left behind?

You may need to try compacting after the reference is removed or closing the mdb and re-open the same db. Adding and removing Access references can do funny things.

Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Ed2020]

Hi Hap,

I can't believe I missed the first thing you spotted. This code doesn't like running in break mode so I haven't been able to check program flow that way but I should have seen that.

There are no runtime errors. The reference just isn't removed. I'll give your suggestions a go on Monday.

Thanks again.

Ed Metcalfe.

Please do not feed the trolls.....