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!