Find DBs Accessing an Oracle Data Source? 2

[Ed2020]

Hi,

I have been asked to carry out a piece of analysis to identify all Access databases on our network share that access our Oracle MIS database.

I need to come up with some code to cycle through each Access DB (details of all of them will be stored in a table of some description) and determine the following information:

1. Does it access the Oracle database?
2. If so, how does it access it?
3. What tables/fields is it accessing?

As far as I can tell I need to check for the following methods of access:

1. ODBC linked tables.
2. Pass-through queries.
3. DAO or ADO in VBA.

Can anyone think of anything I have missed?

Also if anyone could give me any pointers on how to check for the third option (the first two should be easy enough) that would be much appreciated too.

I have approximately 4000 (!!) database files to check so an automated solution is pretty much essential.

TIA,

Ed Metcalfe.

Please do not feed the trolls.....

 

[Remou]

I cannot quite see how .MDEs could be checked, but otherwise it should be easy enough to check the code for key words.

Some notes.

Sub AddDAO()
    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

    'To make life easier and lines shorter.
    Set oVBE = VBE.ActiveVBProject.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
        blnFound = oVBE(mdl.Name).CodeModule.Find("tblTable", StartLine, StartColumn, EndLine, EndColumn)
        'if it is found. ...
        If blnFound = True Then
            Debug.Print oVBE(mdl.Name).CodeModule

        End If
    Next
End Sub

 

[Ed2020]

Remou,

Excellent, that was far more than I was expecting. Have a star!

I think the MDEs issue is going to the bottom of my list. I'm hoping there won't be too many!

Ed Metcalfe.

Please do not feed the trolls.....

 

[markamatte]

Remou,

Very nice solution. Could this be modified to search different databases from a single mdb?

Thanks,

Mark

 

[jrbarnett]

Ed - for ADO - look for lines of code with .Open in it - this will cover ADO connections.

For finding ODBC database connection strings, look for:

ODBC;

for data sources that use OLEDB, look for

Provider=

For pass through queries, just look at the .Connect property (eg - debug.print Currentdb.QueryDefs("qryPassThrough").Connect
- note this will be blank for non pass through queries.

For linked tables, look at the connect property
Debug.Print CurrentDb.Tabledefs("SomeTable").Connect

Another way I've found - is to look at the DB side of things: your apps have to be able to authenticate - get the list of database server side logins, you can then use the client side connection information to match these up and maybe rationalise them at the same time (as I did with mine).

John

 

[jrbarnett]

Markamette - there's some code explaining how to do that linked from

http://www.tek-tips.com/viewthread.cfm?qid=1420197&page=1

John

 

[Ed2020]

John,

Thanks for all your assistance with the questions I have posted for this piece of work. It's been a job with a fairly quick turn-around time and your help has been invaluable. I owe ya one!

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

BTW...

Mark,

My intentions is (time allowing) to create a single solution to build a file listing from a selected area and then scan each system for the relevant information. I have a lot of the work done already but my priority is getting the information collated so the code is somewhat fragmented at the moment.

If you're interested I'd be happy to upload it to my webspace when I've finished it so you can grab a copy. It's likely to be a couple of weeks (at least) before I get around to doing this though...

Ed Metcalfe.

Please do not feed the trolls.....

 

[jrbarnett]

Another tactic that may be worth investigating:

Loop through the Application.References collection
It includes a list of the object libraries referenced, so will include any references to DAO, ADO etc.

Obviously you can't tell just from its inclusion whether it is being used, but its a good way of telling if it has dependencies on extra object libraries.

If you're using Office XP or earlier, and also have Excel based applications, its also worth checking for XLODBC in them as well (see

http://support.microsoft.com/kb/124218

and

http://www.microsoft.com/downloads/...67-13A0-4895-9942-5B177846AB8A&displaylang=en

If you find any, I strongly recommend replacing it with ADO, since this isn't supported in Office 2003 or 2007, and its only available in Excel, my experience is that ADO is also a lot faster).

John

 

[Ed2020]

I seem to be having one of my thicko days!

How do I amend this line of Remou's code to open the VBProject of another database?

Set oVBE = VBE.ActiveVBProject.VBComponents

TIA,

Ed Metcalfe.

Please do not feed the trolls.....

 

[Remou]

You could have more fun with VBE. Some notes.

Sub Ed()
    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 ref As Reference

    'To make life easier and lines shorter.
    'Set oVBE = VBE.ActiveVBProject.VBComponents

    On Error Resume Next
    
    Set ref = References.AddFromFile(CurrentProject.Path & "\db1.mdb")
    
    'You can put the error handler back here.    

    Set oVBE = VBE.VBProjects(ref.Name).VBComponents
    
    'Check each module ...
    For Each mdl In oVBE
        Debug.Print mdl.Name
        EndLine = oVBE(mdl.Name).CodeModule.CountOfLines
        For i = 1 To EndLine
        Ln = oVBE(mdl.Name).CodeModule.Lines(i, 1)
        If (InStr(1, Ln, "Connect") > 0 _
         Or InStr(1, Ln, "Execute") > 0) Then
            Debug.Print Ln & "(" & i & ")"
        End If
        Next

    Next
    
    References.Remove ref
End Sub

 

[Ed2020]

Aha! Easy when you know how.

Thank you!

Ed Metcalfe.

Please do not feed the trolls.....

 

[markamatte]

Thanks again for the code...but when I run it...I don't get errors but ref.name is "object variable or with block variable not set"? I did replace db1.mdb with a known database name.

Any suggestions?

Thanks,

Mark

 

[Remou]

markamatte

I use CurrentProject.Path for the example, did you change this to a valid path for your DB?

You can test for projects:

For Each proj In VBE.VBProjects
    Debug.Print proj.Name
Next

 

[markamatte]

Yes I change it to a valid path...the code does not error...but if I step through ref.name is "object variable or with block variable not set"?

Thanks, Mark

 

[Remou]

Comment the line "On error resume next" and see what errors you get.

 

[markamatte]

Run-time error '35021'
Can't find project or library.

Here is the line:
Set ref = References.AddFromFile("C:\Temp\AddAllToList.mdb")

Just found something though...the AddAllToList.mdb I was referring to is Access97...used one that was XP...and it worked fine.
Being that the tool I'm building gathers a bunch of MDB info, including version...can this example be modified to handle A97?

Thanks Again,

Mark