Is there a way to tell if another Access db is linked to mine?

I have an old Access db with a plethora of lookup tables. Supposedly it was a warehouse of sorts for a bunch of other dept-made access apps to link to. We want to kill it. But, id there a way to find out if an开发者_JAVA技巧y app is currently linked to it?

You need the full path and filename of all the Access apps; this may not be possible.

For those you can, loop through all the files:

1. connect to each database to test for link.
2. Loop through all the tables in TestForLinkDatabase.TableDefs
3. Check to see if there is a .SourceTableName and the .Connect = YourLookupTableWarehouse for each table. I think the SourceTableName is an empty string for local tables.
4. Keep track of #3. You can optionally stop checking the rest of the tables if you find a single instance in the other file.

Again, it is not foolproof, but would be a good exercise to get a grip on all the Access apps floating around your company.

*Code does not exclude system tables.

Private Sub CheckToSeeIfLinked()
    Dim Dbs As DAO.Database
    Dim Tdf As DAO.TableDef
    Dim Tdfs As TableDefs

    Dim wrk As DAO.Workspace
    Set wrk = DBEngine.Workspaces(0)

    Dim TestDatabaseForLinks As String
    TestDatabaseForLinks = "C:\FileNameToCheck.mdb"

    Set Dbs = wrk.OpenDatabase(TestDatabaseForLinks)
    Set Tdfs = Dbs.TableDefs

   For Each Tdf In Tdfs

         If Tdf.Connect <> "" Then
             Debug.Print "Table: " & Tdf.Name & " - Is Linked To: " & Tdf.Connect
         Else
             Debug.Print "Table:  " & Tdf.Name & " is not linked"

         End If

    Next

    If Not (Dbs Is Nothing) Then
      Dbs.Close
      Set Dbs = Nothing
      Set Tdfs = Nothing
   End If

End Sub

Move it to another directory. Linked tables have hard-coded paths.

Not while the database isn't in use. When it is in use you should see an LDB/LACCDB file. You can open it with Notepad to see the workstation name.

If you are using Access security you will also see the Access userid. Otherwise you will see "Admin"

Opening the .ldb/.laccdb file using notepad will show you both who's currently in the database and some of the workstations which were in the database. When a person exits Access their workstation name and Access login id, Admin unless you are using Access security, are left in a "slot" or record in the ldb file. This slot or record may get overwritten the next time someone enters the MDB depending on what slot or record is available previous to it in the ldb file.

Determining the workstation which caused the Microsoft Access MDB corruption