Skipping over Linked tables

[ischwartz88]

Hi. I have written a script that searches through every field in every table in a given database. It works fine in most databases, however, when I run it in a database with Linked tables, the script crashes when it reaches a Linked table and tried to search through it.

My code is written below.

Option Compare Database
 
Sub MK5Search()
GenerateFieldSizeInfo
End Sub
 
Sub GenerateFieldSizeInfo()
    Dim cdb As DAO.Database
    Dim oTable As DAO.TableDef
    Dim oField As DAO.Field
    Dim rsTable As DAO.Recordset
    Dim strSQLTable As String
    Dim iMaxSize As Integer
    Dim iHeadroom As Integer
    Dim iFileNumber As Integer
    Dim strOutputFile As String
 
    'Create the header file. (If the file already exists, its contents are overwritten.)
    iFileNumber = 1
    'Enter the location you want the word file to be exported in the quotes below
    strOutputFile = "C:\etc.."
 
    Open strOutputFile For Output As #iFileNumber
    'Open the database.
    Set cdb = CurrentDb
 
    Print #iFileNumber, "Rec Num "; Tab(15); " Table"; Tab(55); "Field"; Tab(85); "Relevant Text"; Tab(110);
    Print #iFileNumber, String(118, "-")
 
'If oField.Properties("Type").Value <> "Linked Access" Then
For Each oTable In CurrentDb.TableDefs
  If Not oTable.Name Like "MSys*" Or Not oTable.Name Like "MSys*" Then    'ignore System Tables
    Set rsTable = cdb.OpenRecordset(oTable.Name, dbOpenDynaset)
      intNumOfFields = rsTable.Fields.Count
        For intCounter = 0 To intNumOfFields - 1
          Do While Not rsTable.EOF
            If InStr(rsTable.Fields(intCounter).Value, strSearchString) > 0 Then
               Print #iFileNumber, rsTable.AbsolutePosition + 1; Tab(16); _
                  oTable.Name; Tab(55); _
                  rsTable.Fields(intCounter).Name; Tab(85); _
                  rsTable.Fields(intCounter).Value
            End If
 
            rsTable.MoveNext
          Loop
          rsTable.MoveFirst
        Next
  End If
Next
'End If
 
Close #iFileNumber
End Sub
 
 
Public Function fSearchForString(strSearchString) As Boolean
On Error GoTo Err_fSearchForString
 
If strSearchString("*MK 5*") Then
  MsgBox "Search  for String a success!", vbExclamation, "Success"
Else
  MsgBox "What in the world have you done, ADezii!", vbCritical, "Failure"
End If
 
Exit_fSearchForString:
  Exit Function
 
Err_fSearchForString:
  fSearchForString = False
  MsgBox Err.Description, vbExclamation, "Error in fSearchForString()"
  DoCmd.Hourglass False
  If Not MyRS Is Nothing Then
    MyRS.Close
    Set MyRS = Nothing
  End If
    Resume Exit_fSearchForString
 
End Function
 

Line 30 is where I attempted to pass over the linked tables, but it doesn't work. That is why I commented the IF statement out.

If anyone has any recommendations or has done this before, please let me know. I feel that my error is in identifying a Linked table properly; there is no clear explanation in Help that I could find.

Thanks for you help as usual!

Ian

[Stewart Ross]

Hi. If you want to identify a linked table look at the tabledef's Connect property, which will be null for tables that are not linked, and contain the connection path/connection string for tables that are linked.

At line 32 you could add

... OR NOT Isnull(oTable.Connect) then ' ignore system tables and linked tables

-Stewart

[ischwartz88]

Hi. Thanks for the help, however, the script seems to ignore any form of that code. I am not quite sure why. Any suggestions?

[ADezii]

You can check the length of the Connect String for the individual TableDef Objects, a Linked Table will have a value > 0 and a Local Table will have a value of 0. This logic can be incorporated into your existing code and optionally shorten the syntax if so desired:

'Not MSys* or USys* and Not a Linked Table
If (Not oTable.Name Like "MSys*" Or Not oTable.Name Like "USys*") And Len(oTable.Connect) = 0 Then
                                 OR
'More restrictive
If (Not oTable.Name Like "?Sys*") And (Len(oTable.Connect) = 0) Then