My Query choked--- then slipped into a coma!!!

[gMAC]

No really, it did. Ok so here’s my problem. I inherited a database used for testing. Field techs enter test data in the database out in the field, come back to the office and load the data to a backend database on a server. The secretary’s print out the reports to send to customers.
This database has nearly a hundred tables, there’s one table for each type of test. The fields in each test are unique except for JobID. All the test tables have a jobID field.
The field techs don’t tell the secretary’s what test they ran, so they have to search through all the test tables for the particular job number. I tried a query with no luck because of the number of tables I have to search. What I’m after in a way to search all tables for a job number and it return the table names that number is in. I have search the forums and saw nothing on this many tables.
Anyone seen or have a solution.

Thanks
gMAC

 

[Ron_dK]

Would this help :

http://www.access-programmers.co.uk/forums/showthread.php?t=100246&highlight=search+tables

 

[antomack]

The following code should allow you to loop through all the tables in your database and check for a particular field name and then if a record exists with a particular value within that field. The code is specified to work with string values only so if you are searching for number or date values you need to change the code to suit.

As it stands this code only outputs the table names to the debug window so you will need to write code to do whatever you want to do once you find each table name, eg. write the values to a table.

To call the code you just need to call the LoopThroughTables function and provide the relevant string values for the field name and search value as in
LoopThroughTables("YourFieldName", "YourSearchValue")

Public Function LoopThroughTables(strFieldName As String, strValue As String)
On Error GoTo Err_LoopThroughTables
  Dim db1 As DAO.Database
  Dim iCount1 As Integer
  Dim strTable As String
  
  Set db1 = CurrentDb
   
  For iCount1 = 0 To db1.TableDefs.Count - 2
    strTable = db1.TableDefs(iCount1).Name
    If isField(db1.TableDefs(iCount1), strFieldName) = 0 Then
      If CheckForRecord(db1, strTable, strFieldName, strValue) Then
        ' table contains a record for the selected value
        ' process the table name as required
        Debug.Print strTable
      End If
    End If
  Next iCount1

Exit_LoopThroughTables:
  db1.Close
  Exit Function
  
Err_LoopThroughTables:
  MsgBox Err.Description & Err
  Resume Exit_LoopThroughTables
  
End Function

Function isField(td1 As TableDef, sField As String) As Integer
On Error GoTo ExitIsFields
  ' check a table to see if it contains a field named sField
  Dim iCount1 As Integer
  
  isField = 0
  
  For iCount1 = 0 To td1.Fields.Count - 1
    If td1.Fields(iCount1).Name = sField Then
      Exit Function
    End If
  Next
  isField = -1

Exit Function

ExitIsFields:
  MsgBox Err.Description & Err
  Exit Function

End Function

Public Function CheckForRecord(db As Database, strTabNm As String, strField As String, strVal As String)
On Error GoTo Err_CheckForRecord
  ' Checks if record exists with field strField of strVal
  Dim rstRecord As DAO.Recordset
  Dim strSQL As String
   
  strSQL = "SELECT " & strTabNm & ".* FROM " & strTabNm
  strSQL = strSQL + " WHERE " & strField & "='" & strVal & "';"
  Set rstRecord = db.OpenRecordset(strSQL)
  
  rstRecord.MoveFirst
  CheckForRecord = True
  
Exit_CheckForRecord:
  rstRecord.Close
  Exit Function
  
Err_CheckForRecord:
  Select Case Err
    Case 3021         ' no record therefore record not available for use
      CheckForRecord = False
      Resume Exit_CheckForRecord
    Case Else
      MsgBox Err.Description & Err
      Resume Exit_CheckForRecord
  End Select
  
End Function

 

[gMAC]

To rak:
I found that same thread when I did my search. The "Cool Search Database" is a great search tool, but it still uses a query for the search form.
The problem isn’t that I don’t know how to build query’s and search forms, it that a query is limited to the amount of tables you can efficiently use in it.
I do appreciate the response.

To antomack:
The code looks promising; I will give it a try and see if I can make it work for me.

Thank for the response,
gMAC