Identifying Tables in Access DBs on an entire drive

[TailWagger]

Does anyone know of any software that will search an entire drive automatically for certain tables in any Access databases? I'm not looking for code where you have to type in each database.

In a nutshell, I work in a very large company and everytime we need to do impact analysis on a DB2 table, we have no way of finding what Access apps are using them.

Any help would be very appreciated!!

Thanks!

 

[Guest_imported]

Hello.

I am going to give you the code for Access 2000/XP. It is different if you have 97, so let me know.

Use the Application.Filesearch object to locate all *.mdb files and open the MSysObjects table in each one, then search the MSysObjects table/s for entries of each of the entries of the table name you are looking for, and outputs which databases to the screen. I could alter this to write a log file to the disk which would write a report about which databases they are, rather than to screen. Let me know if you'd prefer that.

An example function call for this function would be :

call MyExamnFnctn("N:\IT\Databases", "tblCustIdent&quot

Please let me know if this is what you wanted.

Mr Big



Public Function MyExamnFnctn(Byval Pathnm as string, ObjectName as string) as boolean

'N.B. Ensure "Path" points to folder where the databases will be located. Eg. C:\My Documents

'filesystem object used for file I/O
Set fs = Application.FileSearch
With fs
.LookIn = Pathnm
.FileName = "*.mdb"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" mdb database file(s) found."
For i = 1 To .FoundFiles.Count
'if they are found, then import into table
Dim noentries as Double
noentries = CDbl(DBEngine.Workspaces(0).Databases(0).OpenRecordset("Select count(*) from MSysObjects in " & chr(34) & Trim(Pathnm) & "\" & .FoundFiles(i) & chr(34) & " where Name = " & chr(34) & Trim(ObjectName) & chr(34) & " and Type = -32768&quot.Fields(0))
if noentries > 0 then Msgbox("Database : " & Pathnm & "\" & .FoundFiles(i) & " has " & noentries & " occurances of " & Trim(ObjectName)) 'you could also use debug.print

Next i
Else
MsgBox "There were no mdb files found."
End If
End With

Msgbox("Done&quot

'----------------------------------------

End function

 

[TailWagger]

Hi Mr Big!

My company is currently on Access 97, but we will be converting very soon, so I could use both sets of code if you have them!

This is great - thanks so much!!