Is there a way to find all references to linked tables on a server? 1

[AndyInNC]

I am moving some Access tables into SQL Server and I want to find out how many mdbs on the network link to each of these tables. (Desktops too, if possible.)

Any ideas?

Thanks.

 

[lameid]

I don't have a good suggestion for you.

An option would be to anounce the change, cut over and deal with the fall out. This is probably the easiest way.

Alternately I'd try to find a way to import a list of all MDB's on the network with complete paths. Then you could open connections to each one while logged in as a domain admin and loop through the tabledefs collection and grab the connect string.

Yet another way would be to make an access database that looks for MDB's/MDE's (or Access 2007 variant) files and have it populate a backend table on the network by looping as described above.

In any case you have to pick the lesser of the evils for your environment.

There is always the off chance there is some tool out there that does this that I don't know about.

 

[overdraft015]

I have an access db that goes and checks specific shares/dir's and looks in each .mdb found and checks any linked tables to see if they are linked to this path. also logs the results

cant remember if it also checks any code for the links though.

It's not what you know. It's who's on Tek-Tip's

 

[AndyInNC]

Lameid, you and I are thinking along the same lines. Of course, relying on users to know what their mdbs are linked to is pretty iffy. Which goes back to dealing w/phone calls from disgruntled users.

Overdraft, I'd love to take a look at the product you mentioned. Did you build it or buy it? Or is it a legacy thing from somebody else?

I'm seriously thinking of building that. It will really be useful for a start (if it works). It would need to do a recursive directory search for mdbs (and mdes?), connect to the MSysObjects table, and look for links there.

And checking on local machines for mdbs....<sigh>

As Overdraft015 pointed out, another hole in the process could be not checking code for links, although that wouldn't be the norm. Usually links are handled manually, not in code. Even if it was in the code, 99% of users wouldn't know anyway, so they couldn't alert me. The recursive directory search would (hopefully) get most of them and then, anything else would be the same as the cut-over and just deal with it from that point.

 

[lameid]

I would do several things in addition to the try and find it yourself...

Tell the users of the change.

Tell them you are going to plan for the roll out but there might be something missed. Tell them how to fix connect strings themselves in a document in case something is missed. This way when they can't do it themselves, you are helping them rather then breaking it. Hmm... make an application that points to a file and fixes connect strings.

Remind "advanced users" that if they have code that opens connections directly the code will need to be updated too.

Some old code of mine that changes connect strings in a target database... written before they introduced the replace function. Obviously, it needs a DAO reference and if you are really giving it to end users, error handling.

Sub ChangeConnect(strDBpath As String, strOldpath As String, strNewpath As String)
    'strDBpath Path and File Name of database that you want to update links in.
    'strOldpath Path you want to replace
        '(or other element in connect property you want to change)
    'strNewpath Path replacement (or Replacement element
    
    Dim Ws As DAO.Workspace
    Dim Db As DAO.Database
    Dim tbls As DAO.TableDefs
    Dim tbl As DAO.TableDef
    Dim strconnect As String
    Dim intStartpos As Integer
    Dim intlenoldpath
    Set Ws = DBEngine.Workspaces(0)
    Set Db = Ws.OpenDatabase(strDBpath)
    Set tbls = Db.TableDefs
    
    intlenoldpath = Len(strOldpath)
    For Each tbl In tbls
        strconnect = tbl.Connect
        If Len(strconnect) > 0 Then
            intStartpos = InStr(1, strconnect, strOldpath, vbTextCompare)
            If intStartpos > 0 Then
                strconnect = Left(strconnect, (intStartpos - 1)) & strNewpath & Right(strconnect, (Len(strconnect) - intStartpos - intlenoldpath + 1))
                'MsgBox tbl.Connect & Chr(13) & Chr(13) & strconnect
                tbl.Connect = strconnect
                tbl.RefreshLink
                tbls.Refresh
                'MsgBox tbl.Connect
            End If
        End If
    Next
    Db.Close
    Set tbl = Nothing
    Set tbls = Nothing
    Set Db = Nothing
    Set Ws = Nothing
End Sub

 

[overdraft015]

I had a quick look at the prog, it was a developement between myself and another IT guy at work we was replacing a server and moving all the data to another server so we needed to know which .mdb's were mapped to that server.

it does look like we checked into the code to find the links.

for local files on peoples desktops/laptops you could have a batch file load when the user logs on and runs the .mdb to check or you could add a list of host names and alter the code to go and check all the users machines. obviously they would have to be online for this and you would need admin rights but im sure you can ensure that.

i just need to clean some of the code and document the main bits and i will post on here for you. hopefully by tommorow afternoon (UK time)

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

sorry about the delay been busy.

ok find attached the location to the db i have. some code may need cleaning/tweaking to your requirements.

--------------

Const cLinkSearch = "S:\"
Change this to what you are searching the linked tables/code for.

--------------

SearchLinks "\\sbl00011\share$\", True
SearchLinks "D:\", True

these are the paths you wish to search for a list of all access db's

--------------

Call FillDir(colDirList, strPath, "*.mdb", True)

comment this out if you have your list of db's and just want to check for your constant.

any questions feel free ask and i hope this helps you achieve your goal.

Michael

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

forgot to add, on the machine you run this on you will need to set your security permissions for macro to low to avoid having to press the open button each time it finds an mdb and checks it.

obviously check the code first so you are happy theres nothing malicious in it. and its a good idea to step through the first few mdbs so you are happy at what its doing.

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

Link:

It's not what you know. It's who's on Tek-Tip's

 

[AndyInNC]

LameID, your code makes a lot of sense to incorporate into my search.

Overdraft, thanks for the search code, especially the bit that searches for links created within a module. I've been able to find many of the mdbs out there with this.

Thanks for the help.

 

[overdraft015]

glad it helped Andy. i would be interested in knowing which method you use(d) to search individual machines for any db's

i.e. batch script, gpo etc..? logs?

It's not what you know. It's who's on Tek-Tip's

 

[AndyInNC]

Ah, yes. That.

To find all the mdbs, I created a module and stole some code (noted below -- I'm not above giving credit). My contribution to the code was the addition of a property for the current directory (DirectoryName).

I moved the driving code over to an Access form and set the form's timer event to change a label to the currently searched directory, which, as you can see below, loads into a collection that I can peruse in the code.

Oh yeah, I also added a DoEvents in the loop.

Hope this makes sense!

Option Compare Database
Option Explicit

[COLOR=green]'*** Stolen from:
'*** [URL unfurl="true"]http://www.ammara.com/access_image_faq/recursive_folder_search.html[/URL][/color]

Private m_strDirectoryName As String

Public Property Get DirectoryName() As String
    DirectoryName = m_strDirectoryName
End Property


[COLOR=green]'Usage Example:
'    Dim colFiles As New Collection
'    RecursiveDir colFiles, "C:\Photos", "*.jpg", True
'
'    Dim vFile As Variant
'    For Each vFile In colFiles
'        Debug.Print vFile
'    Next vFile
'
'
'Output:
'C:\Photos\2006-10-28\IMG_2851.JPG
'C:\Photos\2006-10-28\IMG_2852.JPG
'C:\Photos\2006-11-04\IMG_2853.JPG
'C:\Photos\2006-11-04\IMG_2854.JPG
'C:\Photos\2006-11-04\IMG_2855.JPG[/color]

Public Function RecursiveDir(colFiles As Collection, _
                             strFolder As String, _
                             strFileSpec As String, _
                             bIncludeSubfolders As Boolean)
On Error Resume Next
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

[COLOR=green]    'Add files in strFolder matching strFileSpec to colFiles[/color]
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    If Err = 52 Then strTemp = ""
    Do While (strTemp <> vbNullString) And (strTemp > "")
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
[COLOR=green]        'Fill colFolders with list of subdirectories of strFolder[/color]
        strTemp = Dir(strFolder, vbDirectory)
        If Err = 52 Then strTemp = ""
        Do While (strTemp <> vbNullString) And (strTemp > "")
            DoEvents
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        m_strDirectoryName = strFolder

[COLOR=green]        'Call RecursiveDir for each subfolder in colFolders[/color]
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If

End Function


Public Function TrailingSlash(strFolder As String) As String
    If Len(strFolder) > 0 Then
        If Right(strFolder, 1) = "\" Then
            TrailingSlash = strFolder
        Else
            TrailingSlash = strFolder & "\"
        End If
    End If
End Function

 

[AndyInNC]

For anyone who may be interested, here is the first draft of my solution @ Planet Source Code.

http://www.planet-source-code.com/v...9&lngWId=1&txtForceRefresh=107200910173770151

 

[overdraft015]

Hi Andy,

the screen shots look good. i have downloaded and when i click browse on the form i get an error:

Class doesn't support Automation (Error 430)

Michael

It's not what you know. It's who's on Tek-Tip's

 

[AndyInNC]

Hmmmmm....

It sounds like a referenced file is not the right version.

Open the code for editing and check the references. The problem you're having is with the "Microsoft Shell Controls and Automation" dll. On my system, it is C:\Windows\system32\SHELL32.dll. Although this is an Access 2000 format mdb, it was actually developed in Access 2003 so there could be some compatibility issues in there somewhere.

So far, I've tested it on Windows XP 32-bit and Vista 64-bit in Access 2003 and Access 2007 and everything has been fine.

If the Browse button doesn't work, you can still copy and paste or type a valid path into the "Search Path" box and click "Find Links" and it should find the links. (Make sure the DAO version is up to speed in the References.) An invalid path does not give an error, it just finishes REALLY fast.

 

[overdraft015]

yeah was a reference that needed unticking then reloading.

looks good i like the user interface you have done.

doesnt look like it checks any code? did you decide on only checking for linked tables?

It's not what you know. It's who's on Tek-Tip's

 

[AndyInNC]

Hey Michael, glad you like it!

No, I haven't included the code check yet. This was the first draft. (Well, first and a half.) What I'll probably do is add a couple of check boxes on the form for "Hard" linked tables and Coded linked tables.

I only work at the site that needs this tool 2 days/week so I ditched some of the functionality (like checking code, sorting the listbox results, etc).