Find All Databases that use a Table 1

[jw45]

I have a databse that contains a table called tblWeeks. I know that there are many databases that link to this table.

I want to change the location of my database but if I do then all of the 'links' will be broken.

Is there anyway to search/find all the db that use this this table so I can update them before I move mine?

Thanks,
JW

 

[lupins46]

Only by opening the front-end and looking.
You can do it manually or write a program to do it.

 

[MichaelRed]

ummmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmm,

mayhap not?

Although it may not be any more comfortable. There are (per requirements) tables in relational databases which include hte database structure, including the actual location of data objects. For Ms. A. (Jst anyway) this is the hidden system table MSysObjects. Since all Ms. A. / Jet dbs have this table, you can (programatically) reference ALL such tables from any give Ms. A. (Jet) database. Then, in each instance of a database, inspect the table (again "programattically" read Q U E R Y!!) to find any and all references to your "table".

You cannot CHANGE these references in this manner, but thers are other possabilities, such as generting a module / procedure in your native db, and exporting it to all the dbs whichj will sufer from the exercise. The purpose of hte module would be to RE-Link the table(s), and therefore you need an additional bit of code to be generated in the startup object to instatiate the re-link proceduree(s).

Not a task for the faint of heart, and probably not even a foolproof process (those (*&^(*#&^(*$&@ "fools" keep getting smarter all the time). On the other hand, if the source db might be moved often, thers are a LOT of other dbs which reference the data, or other extenuating circumstances, the effort could be worthwhile.




MichaelRed

 

[jw45]

Thanks for your responses.

I may have not been clear in my orginal question. I do not know the the name of the database that may or may not link to my table nor do I know where they reside So how do I check to see if they are using (linking) to my table?

I look at the MSysObjects table but of course it is unreadable. How do I read/reference this table to find the info I want?

I do not mind relinking the tables manually because in this case I don't think they will be more than 15 to 20. My problem is finding them before thet blow up after I move the original database.

 

[lupins46]

There is no way to find this info in the database that all the others are linking to. The linking details only exist in the databases that are linking to your table (the 'Front-end' databases). So you have to look at all the front-end databases to see if the link is present.

If the front-end databases are scattered around multiple users then you have a bit of a slog to fix them.

You can do a File-Find to get a list of mdb files but I don't know how you get that type of result into a maintenence program.

If the data in this table is not changing then perhaps you can leave it where it is for the time being as well as using your new location, and modify the applications to use the new location over a longer period.

 

[jw45]

If the front-end databases are scattered around multiple users then you have a bit of a slog to fix them.

Thanks for your time/answers ... but that is exactly my problem. I was hoping that there wasy going to a relatively easy wasy to find them. There are hundreds of databases out there to search throuhh but only a few will have a link to my table. Guess I'll just move mine then fix others when as they crash.

 

[MichaelRed]

I have no idea why you say / think that the MSysObjects Table is unreadable, other htan the possible failure to explore it. Many of te fields have numeric information, which may not be of interest (or use) to you, while others have plain text and are quite easily read and understood.

I did not mean (and don't believe I said) that you could use the 'native' (local?) MSysObjects table to find all links back to your native table(s). As [colorblue]lupins46 [/color] mentioned, you can use filefind as ONE approach to locating other databases in the domain. I personally usually program a search in VB(A) for this, bau any process which collects the information is sufficient.

The issue then is to simply open the iundividual databases _programatically- from youR "OWN" AND INCESTIGATES that DATABASE's MSysObjects table for the occurance of the link table name of your (or actually ANY) desired database / file.

Again, I note that this is not a technique for the faint of heart, and there WILL be some issues / exceptions (if the foregin db is "secured" and you don't know this fact and /or do not have the password, or .MDW file name / location ...

On a slightly different note, going forward it would seem to be a simple matter of prudence to have such a valuable data store protected in several ways, including having password protection on the datastore. This alone would permit you to amass (over some period of time) all the users who accessed the data, thus obviating the need for the curent exercise.




MichaelRed

 

[jsteph]

You can use the Application.Filesearch object, set up the 'Type' for .mdb (There's a constant, not sure offhand what it is).

Then loop the .FoundFiles and check each of those .mdbs for the linked tables in their msysobjects.

You may run into Jet Security issues, depending if any of these front-ends use any sort of Jet security.
--Jim

 

[Remou]

This is jsteph's suggestion, I think:

Sub Search4MDB(strName As String, strPath As String)
'Use the NewSearch method to reset the search criteria to the
'default settings. All property values are retained after each search
'is run, and by using the NewSearch method you can selectively set
'properties for the next file search without manually resetting previous
'property values.
'References Office 9.0 Object Library
Dim i

With Application.FileSearch
    .NewSearch
    .LookIn = strPath
    .SearchSubFolders = True
    .FileName = strName
    .MatchTextExactly = True
    .FileType = msoFileTypeAllFiles 'look in object browser for other types

    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            Debug.Print .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With

End Sub

 

[Remou]

Here is another snippet, based on MichaelRed's advice:

Sub IsThatMyCow(tblName)
Dim db As Database
Dim rsExt As Recordset
Dim rsCur As Recordset

strDB = "C:\tek-tips.mdb"
DoCmd.TransferDatabase acImport, "Microsoft Access", strDB, acTable, "MSysObjects", "tmpObj"
Set rsExt = CurrentDb.OpenRecordset("Select * From tmpObj Where Name='" & tblName & "'")
If rsExt.EOF Then
    Debug.Print "That's not my cow."
Else
    Debug.Print tblName & " found in " & strDB
End If
rsExt.Close
Set rsExt = Nothing
DoCmd.DeleteObject acTable, "tmpObj"
End Sub

 

[MichaelRed]

Remou

Good implementations. I modified them slightly to work w/ recordsets and ran a bit of it on my home system. Issues (as noted previously) remain. Applications which have most any protection will cause the pop-up for the password and some older versions of Ms. A. applications (ver 1 & 2 .MDBs?) appear to no longer be accessible (I really did not rember saving anything that old!).

For any who might be considering using these functions, I would suggest that the use of recordsets to collect the info (vs, the debug / immediate window. and to do away with the args. The issue would, to me appear in the first instance to just collect the .MDB files within the domain, so the path is not relevant. In the second instance, the need (desire?) is just to know it the (ext) db has any links (you can sort out the ones to specific databases ot tables later), so the file name arg is easily reduced to just knowing that there is a valule in the "[Connect]" field.

Finally, Remou has implemented the (second) procedure by actually importing the foregin ("Ext") databases's MSysObjects file. I believe that it would be somewhat fster to link to the first one and then just change the link pointers. In either instance, the process is not one to be run unattended, unless / until the issues of fileformat (ver 1 or 2 .MDBs), and password functions are resolved.



MichaelRed