Search all tables in MS Access for similar value with 1 query. 1

[cdgeer]

I have about 100 tables in a MS Access database. They are all related by a field named "EEID". They all also have a field named "AccountName". I want to search all the Tables' AccountName column for 1 certain value and return the results. Is this possible with MS Access?
I tried writing 1 query that included all the Tables but it came back, "Query too complex".

Any help would be appreciated.

 

[PWise]

Select EEID ,AccountName ,"table1"
from table1
where AccountName=xxxx
union
Select EEID ,AccountName ,"table2"
from table2
where AccountName=xxxx
Union
Select.........

 

[cdgeer]

Do I actually have to do this for every table? I'm looking for a short cut. I talked to 1 of our DBAs and he said that you can query all the tables in an Oracle database for a certain value in a certain column but, he wasn't sure if or how you could do it in SQL for MS Access.

 

[sxschech]

You could probably do it in vba. Would need to have an sql statement that lists all the tables you want to search and then use looping to build the final select statements. For the list of tables you could do

select name from msysobjects;

You may need a where clause to filter the table list. Alternatively, you could store the table names in another table if the naming convention is not easily filtered in a where clause.

After that you could use pwise code substituting the actual table names with the variable names defined in your code.

 

[kjv1611]

Here's how I would do it - or at least I'd try to do it this way - in VBA:

Using pwise's SQL method, or else using an OUTER JOIN for the SQL portion, but this is how I'd build it in VBA:

Public Function SearchAll(strAccount As String)
On Error GoTo ErrHandle

  Dim db As DAO.Database
  Dim rs as DAO.Recordset
  Dim tdf As DAO.TableDef
  Dim strAccountName
  Dim strSQL as String
  Dim QU As String
  
  Set db = CurrentDb
  QU = Chr(34) [green]ASCII Code for Double quote, "[/GREEN]

  For Each tdf in db.TableDefs
    If InStr(tdf.Name,"~") Then
    ElseIf InStr(tdf.Name,"MYS") Then
    Else
      If strSQL = vbNullString Then
        strSQL = "SELECT EEID ,AccountName ," & tdf.Name & _
       " FROM " & tdf.Name " & _
       "WHERE AccountName = " & DQ & strAccount & DQ & " UNION "
      Else
       strSQL = strSQL & _
       "SELECT EEID ,AccountName ," & tdf.Name & _
       " FROM " & tdf.Name " & _
       "WHERE AccountName = " & DQ & strAccount & DQ & " UNION "
      End If
    End If

  Set rs = db.OpenRecordset(strSQL)
  rs.MoveLast
  rs.MoveFirst
  If rs.RecordCount = 1 Then
    SearchAll = rs.Fields("AccountName")
  ElseIf rs.RecordCount > 1 Then
    [green]'Not sure how you want to return it..[/green]
  Else
    MsgBox "No Records Found!"
  End If

ExitSub:
On Error Resume Next
  Set tdf = Nothing
  db.Close
  Set db = Nothing

  Exit Sub

ErrHandle:
  MsgBox "There was an error!" [green]'Customize to your preferences.[/green]
  Resume ExitSub

End Sub

[green]'Then Call the Function in your Form Control VBA[/green]
Private Sub Search_Click()
  txtAnswer = SearchAll(txtAccountName)
End Sub

That's not a total method, for sure, as I don't know what info you're wanting to return. I'm assuming, though, that you want to search by AccountName, which is why I set it up the way I did.. However, this function, if it works correctly, would build the SQL statement, then return the same value you searched with. You can modify it however you need.

Another method could be to build the SQL query, and then set the form's record source to the newly built SQL statement in case you have multiple records. Then that form could be set as a continuous form - you put all your other stuff in the Form Header/Footer, and the text boxes for showing your data in the Form Detail section..

Hopefully this'll at least get you a start in the right direction. I've not tested it at all, just typed it here.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Be sure to ask if you have any questions I didn't clarify in the code above.

Here's what it does (in English):

1. Loops through all tables, skipping system and temp tables (If you have ANY tables you don't want included, then add another "ElseIf" line for each of those, before the "Else" line)... or if you have more tables that wouldn't be included than that will be included, you'd need to programatically build the table names (if you named them accordingly) or you could also just split that portion of the database off into another database, and link to it..

2. Builds the SQL statement, based off of the table names... so it builds it one SQL statement at a time, sticking a UNION between... and actually, I've got the UNION in the wrong place... so I'll put the corrected code here:

CORRECTED:

 For Each tdf in db.TableDefs
    If InStr(tdf.Name,"~") Then
    ElseIf InStr(tdf.Name,"MYS") Then
    Else
      If strSQL = vbNullString Then
        strSQL = "SELECT EEID ,AccountName ," & tdf.Name & _
       " FROM " & tdf.Name " & _
       "WHERE AccountName = " & DQ & strAccount & DQ
      Else
       strSQL = strSQL & _
       "[HIGHILGHT]UNION [/HIGHLIGHT]SELECT EEID ,AccountName ," & tdf.Name & _
       " FROM " & tdf.Name " & _
       "WHERE AccountName = " & DQ & strAccount & DQ
      End If
    End If
[HIGHLIGHT]  Next tdf[/HIGHLIGHT]

I [HIGHLIGHT]highlighted[/HIGHLIGHT] the corrected code.

So, anyway, after it builds the SQL statement, it sets a RecordSet to that statement, and then returns the one value (if it's only one value) to the Function.

However, if you may be returning multiple records and/or multiple fields, then setting the form's recordsource (as I mentioned above) may be the best route... you'd set the form recordsource to the SQL statement.... I think..

So that part at the end might be something more like:

  Forms!MyFormName.RecordSource = strSQL

--

"If to err is human, then I must be some kind of human!" -Me

 

[cdgeer]

Thanks to all for your help! I couldn't get the VB code to work. It's a little too advanced for me. I know that I need to make some changes for it to work the way I need it to but don't know how.

I do have a Table called "TblNames" containing a list of all the table names that I would want to search if that makes it easier.

I would like to return the names of the tables that contain the AccountName that I am searching for.
How would I write the SQL code to use each record in TblNames as the table name , search the AccountNames in that table for a certain value and if it is there return the table name?

 

[kjv1611]

How would I write the SQL code to use each record in TblNames as the table name , search the AccountNames in that table for a certain value and if it is there return the table name?

Well, you'd need to loop through that table, picking out the table names, and applying them to the SQL code within the loop..

You should be able to gather some of the ideas from the VBA code provided. Other than doing it that way, you'd have to manually type it all out in a SQL view for a query, or else manually do so in the Query Design view. Another thought is that if you manually create it, instead of using VBA in a dynamic manner, then if you change the table list at any time, the query will also need to be manually updated.

You said the code "I couldn't get the VB code to work." What didn't work? I mean, what error message(s) did you get? Or else what did or did not happen according to what you expected?







[/CODE]





--

"If to err is human, then I must be some kind of human!" -Me

 

[cdgeer]

Thanks KJV,

This is your code but I changed the "DQ"s in your orig. to "QU"s.
I am not sure what to put after the "Then" statements where stuff seems to be missing. I know it's because you weren't sure what values I wanted to return. Like I said, I'm not very good with VB but I can understand some of it. I have a form that I want to use to run the code. I have the command button linked to:
Private Sub Search_Click()
txtAnswer = SearchAll(txtAccountName)
End Sub
But I get an error. I know txtAnswer isn't associated with anything on the form. I really need the table name not the account name.
On the form I have 2 text boxes: txtAccountName to enter the search criteria and txtTableName for the results of the search.
Also, I'm confused about this part of the code:
For Each tdf In db.TableDefs
If InStr(tdf.Name, "~") Then
ElseIf InStr(tdf.Name, "MYS") Then
Else...
I'm not sure what to put after "Then

 

[kjv1611]

-------------------------------------
==>But I get an error.

..what error message(s) did you get?

Please tell us WHAT error message you are getting.
-------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-------------------------------------
==>I know txtAnswer isn't associated with anything on the form. I really need the [HIGHLIGHT]table name[/HIGHLIGHT] not the account name.

There we go, then. So you want to feed the Account Name to the function/procedure, and you want to return the table name to the form (Answer).
-------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-------------------------------------
==>On the form I have 2 text boxes: txtAccountName to enter the search criteria and txtTableName for the results of the search.

So, txtTableName = txtAnswer in the example...
txtTableName = SearchAll(txtAccountName)
-------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-------------------------------------
==>Also, I'm confused about this part of the code:
For Each tdf In db.TableDefs
If InStr(tdf.Name, "~") Then
ElseIf InStr(tdf.Name, "MYS") Then
Else...
I'm not sure what to put after "Then"

That's a method I've used for some time now, and I believe others here use it, in order to eliminate system and temporary tables from the results. You wouldn't put anything in the Then portion, but rather under the final Else portion.

For Each tdf in db.TableDefs
  If InStr(tdf.Name, "~") Then
  ElseIf InStr(tdf.Name, "MYS") Then
  Else
    [GREEN]'Code to add the table name into the search/query SQL code.[/GREEN]
  End If

Well, actually that method may not be the best method, in case you end up having "MYS" in the middle of any of your table names... so you could end up needing to use a Left function in that as well to make sure the left 3 characters do not equal that... I just saw a reference on that yesterday - I may can find it again if you need to see it.
-------------------------------------

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

I'd use this:

If Not (tdf.Name Like "~*" Or tdf.Name Like "MSys*") Then
    'Code to add the table name into the search/query SQL code.
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

I'd go with PHV's code change, cdgeer. I think that's a better variation than what I typed up. What I put would look for the ~ or the MYS anywhere in the table name, whereas his code would look for table names beginning with those strings... different but similar to the other reference I mentioned finding the other day.

--

"If to err is human, then I must be some kind of human!" -Me