[AccessD] Search for linked documents....

Hi Sander

This is doable but not straight forward.

First, linked "documents" are treated as ISAM databases and tables. For
text and xBase and Paradox files, the directory is the "database" while
for Excel and Html the file is the database. Linked MAPI tables are even
more excotic so I will leave them for this. Linked ODBC tables are
complete different as they are identified by their connect strings.

Second, tables may be linked with other names than their true names.

This a query which will return all non-deleted links:

  SELECT DISTINCT 
    RTrim([Database]) AS DbsPath, 
    MSysObjects.Connect, 
    Hex(GetTableType([Flags])) AS TableType, 
    MSysObjects.ForeignName, 
    GetTrueForeignName(GetTableType([Flags]),[ForeignName]) AS
TrueForeignName, 
    MSysObjects.Name AS LinkedName
 FROM 
    MSysObjects
  WHERE 
    (IsTablePresent([Flags])=True) 
    AND 
    (MSysObjects.Type=4 Or MSysObjects.Type=6);

It uses these supporting functions:

<code>

Declarations.

  ' Table types known.
  '
  ' ODBC connection.
  Private Const clngTableTypeNone       As Long = &H0
  ' Note: MS MSDE/SQL Server has some higher bits set as well.
  Private Const clngTableTypeOdbc       As Long = &H100000
  ' Jet file attached by UNC name.
  Private Const clngTableTypeJetUnc     As Long = &H200000
  ' Jet file attached by mapped drive letter.
  Private Const clngTableTypeJetDrive   As Long = &H400000
  Private Const clngTableTypeParadox    As Long = &H700000
  Private Const clngTableTypeDbase      As Long = &H800000
  Private Const clngTableTypeText       As Long = &HA00000
  Private Const clngTableTypeExcel      As Long = &HB00000
  Private Const clngTableTypeOutlook    As Long = &HD00000
  Private Const clngTableTypeHtml       As Long = &HE00000

Public Function GetTrueForeignName( _
  ByVal lngTableType As Long, _
  ByVal strForeignName As String) _
  As String
  
' Returns true foreign name for those ISAM table types
' where this is stored as an alias.
' Foreign names for other table types are not altered.
'
' Example Excel:
'   Alias for worksheet: Sheet1$
'   True name: Sheet1
' Example Paradox:
'   Alias for table name: Pdoxfile#db
'   True name: Pdoxfile.db
'
' 2004-05-10. Cactus Data ApS, CPH.

  Const cstrSepDbase    As String = "#"
  Const cstrSepParadox  As String = "#"
  Const cstrSepText     As String = "#"
  Const cstrSepExcel    As String = "$"
  Const cstrRplExcel    As String = vbNullString
  
  Dim strTrueName   As String
  Dim lngPos        As Long
  
  Select Case lngTableType
    Case clngTableTypeParadox
      lngPos = InStr(strForeignName, cstrSepParadox)
      If lngPos > 1 Then
        strTrueName = Left(strForeignName, lngPos - 1)
      Else
        strTrueName = strForeignName
      End If
    Case clngTableTypeDbase
      lngPos = InStr(strForeignName, cstrSepDbase)
      If lngPos > 1 Then
        strTrueName = Left(strForeignName, lngPos - 1)
      Else
        strTrueName = strForeignName
      End If
    Case clngTableTypeExcel
      If Right(strForeignName, 1) = cstrSepExcel Then
        ' Worksheet. Strip traling "$".
        strTrueName = Left(strForeignName, Len(strForeignName) - 1)
      Else
        ' Named Range. Leave as is.
        strTrueName = strForeignName
      End If
    Case clngTableTypeText
      lngPos = InStr(strForeignName, cstrSepText)
      If lngPos > 1 Then
        strTrueName = Left(strForeignName, lngPos - 1)
      Else
        strTrueName = strForeignName
      End If
    Case Else
      strTrueName = strForeignName
  End Select
  
  GetTrueForeignName = strTrueName
  
End Function

Public Function IsTablePresent( _
  ByVal lngFlag As Long) _
  As Boolean
  
' Returns True if a table is not flagged as deleted in MSysObjects.
' To be used in a query to extract currently attached tables.
'
' 2004-04-21. Cactus Data ApS. CPH.

  Const clngFlagDeleted As Long = &H1001
  
  Dim booDeleted        As Boolean
  
  booDeleted = lngFlag And clngFlagDeleted
  
  IsTablePresent = Not booDeleted

  End Function

Public Function GetTableType( _
  ByVal lngFlag As Long) _
  As Long
  
' Returns the table type from lngFlag in MSysObjects by rounding it to
' &H100000 which strips all minor flags including the hidden flag
(&H8)
' and all major flags - like &H20xxxxxx used by MS MSDE/SQL Server.
' To be used in a query to extract currently attached tables of a
specific type.
'
' 2004-04-21. Cactus Data ApS. CPH.

  ' Flag mask.
  ' Const clngFlagMajor As Long = &HFF000000
  ' Const clngFlagMinor As Long = &HFFFFF
  Const clngFlagMask  As Long = &HFF0FFFFF
  
  Dim lngType         As Long
  
  lngType = lngFlag And Not clngFlagMask
  
  GetTableType = lngType

End Function

</code>

Have fun!

/gustav

>>> accessd667 at yahoo.com 19-11-2004 14:57:55 >>>
Hi group,
 
I've got some code from the ADHB2K to search for linked tables.
However, it is also possible to link documents...office documents that
is. So I mean files with extions like:
.xls;
.txt;
etc
 
Q1: how can I search for these linked 'documents'?
Q2: how can I pinpoint the xls files from none xls files?
 
TIA
 
Sander