[AccessD] Search for linked documents....IN TABLES....

S D accessd667 at yahoo.com
Tue Nov 23 07:05:40 CST 2004


Hi group,
 
it seemed that the users where not talking about linked tables but about linked documents in a table!
 
So I've got a table, this table has an oledb field in it. I found out how to search for this.
However, I now need to figure out how I can do the following:
1 - check if file is linked
2 - what type of file is linked (Word, Excel, etc)
3 - what is the version of the matching officeprogram (eg Word 97, Excell 2002)
4 - what is the location of the file
5 - what are the create, last modified, last opened date
 
Did anybody ever do something like this?
Does anybody know if there is some sort of object model that I can use for this eg like in Excell: Application => Workbook => worksheet, etc? 
Does anybody know to any tips on how to figure this out?! 
 
Thnx in advance!
 
Sander
 

S D <accessd667 at yahoo.com> wrote:
Have fun....haha..ha...hmmm I think we've got different ideas about fun 
Thnx for the, once again, great response.

Sander

Gustav Brock wrote:
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:



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



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

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


---------------------------------
Do you Yahoo!?
Meet the all-new My Yahoo! – Try it today! 
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

		
---------------------------------
Do you Yahoo!?
 Discover all that’s new in My Yahoo!


More information about the AccessD mailing list