By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Skipping over Linked tables

P: 7
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Sub MK5Search()
  4. GenerateFieldSizeInfo
  5. End Sub
  6.  
  7. Sub GenerateFieldSizeInfo()
  8.     Dim cdb As DAO.Database
  9.     Dim oTable As DAO.TableDef
  10.     Dim oField As DAO.Field
  11.     Dim rsTable As DAO.Recordset
  12.     Dim strSQLTable As String
  13.     Dim iMaxSize As Integer
  14.     Dim iHeadroom As Integer
  15.     Dim iFileNumber As Integer
  16.     Dim strOutputFile As String
  17.  
  18.     'Create the header file. (If the file already exists, its contents are overwritten.)
  19.     iFileNumber = 1
  20.     'Enter the location you want the word file to be exported in the quotes below
  21.     strOutputFile = "C:\etc.."
  22.  
  23.     Open strOutputFile For Output As #iFileNumber
  24.     'Open the database.
  25.     Set cdb = CurrentDb
  26.  
  27.     Print #iFileNumber, "Rec Num "; Tab(15); " Table"; Tab(55); "Field"; Tab(85); "Relevant Text"; Tab(110);
  28.     Print #iFileNumber, String(118, "-")
  29.  
  30. 'If oField.Properties("Type").Value <> "Linked Access" Then
  31. For Each oTable In CurrentDb.TableDefs
  32.   If Not oTable.Name Like "MSys*" Or Not oTable.Name Like "MSys*" Then    'ignore System Tables
  33.     Set rsTable = cdb.OpenRecordset(oTable.Name, dbOpenDynaset)
  34.       intNumOfFields = rsTable.Fields.Count
  35.         For intCounter = 0 To intNumOfFields - 1
  36.           Do While Not rsTable.EOF
  37.             If InStr(rsTable.Fields(intCounter).Value, strSearchString) > 0 Then
  38.                Print #iFileNumber, rsTable.AbsolutePosition + 1; Tab(16); _
  39.                   oTable.Name; Tab(55); _
  40.                   rsTable.Fields(intCounter).Name; Tab(85); _
  41.                   rsTable.Fields(intCounter).Value
  42.             End If
  43.  
  44.             rsTable.MoveNext
  45.           Loop
  46.           rsTable.MoveFirst
  47.         Next
  48.   End If
  49. Next
  50. 'End If
  51.  
  52. Close #iFileNumber
  53. End Sub
  54.  
  55.  
  56. Public Function fSearchForString(strSearchString) As Boolean
  57. On Error GoTo Err_fSearchForString
  58.  
  59. If strSearchString("*MK 5*") Then
  60.   MsgBox "Search  for String a success!", vbExclamation, "Success"
  61. Else
  62.   MsgBox "What in the world have you done, ADezii!", vbCritical, "Failure"
  63. End If
  64.  
  65. Exit_fSearchForString:
  66.   Exit Function
  67.  
  68. Err_fSearchForString:
  69.   fSearchForString = False
  70.   MsgBox Err.Description, vbExclamation, "Error in fSearchForString()"
  71.   DoCmd.Hourglass False
  72.   If Not MyRS Is Nothing Then
  73.     MyRS.Close
  74.     Set MyRS = Nothing
  75.   End If
  76.     Resume Exit_fSearchForString
  77.  
  78. End Function
  79.  
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
Jul 24 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. ... OR NOT Isnull(oTable.Connect) then ' ignore system tables and linked tables

-Stewart
Jul 28 '08 #2

P: 7
Hi. Thanks for the help, however, the script seems to ignore any form of that code. I am not quite sure why. Any suggestions?
Jul 28 '08 #3

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. 'Not MSys* or USys* and Not a Linked Table
  2. If (Not oTable.Name Like "MSys*" Or Not oTable.Name Like "USys*") And Len(oTable.Connect) = 0 Then
  3.                                  OR
  4. 'More restrictive
  5. If (Not oTable.Name Like "?Sys*") And (Len(oTable.Connect) = 0) Then
  6.  
Jul 28 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.