Recent Question Revisited - SELECT * FROM

I recently asked a question concerning this line:
Set rst = mydb.OpenRecordset("SELECT * FROM People WHERE Name LIKE 'Tim*' ", dbOpenDynaset)

Okay, so I am able to search the People table for the appropriate  records that include the letters Tim.  One step further, how would I search the entire database (Multiple tables) for the same 3 letters?


LVL 3
grooneAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aaron_YoungConnect With a Mentor Commented:
You could also enumerate all Tables in the Database and perform a SQL Select on each (providing they have the same field defined), i.e.

'--------------------------------------------------------
Private Sub Command1_Click()
    Dim oDB As DAO.Database
    Dim oTable As DAO.TableDef
    Dim oRs As DAO.Recordset
    Dim lField As Long
    Dim sFieldName As String
    Dim sValue As String
   
    ' Using the NWIND MDB for this example, displaying ALL records in ALL tables
    ' Where a "CustomerID" field has a value beginning with "D"
    sFieldName = "CustomerID"
    sValue = "D*"
   
    Set oDB = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB")
   
    ' Enumerate all tables in the Database
    For Each oTable In oDB.TableDefs
        If (oTable.Attributes And dbSystemObject) = 0 Then
            ' It's not a system table, so process it
            For lField = 0 To oTable.Fields.Count - 1
                ' Examine each field to see if the field we're checking is available
                If LCase(oTable(lField).Name) = LCase(sFieldName) Then
                    ' If it is, build a SQL Select statement to pull records matching the specified criteria
                    Set oRs = oDB.OpenRecordset("SELECT * FROM [" & oTable.Name & "] WHERE " & sFieldName & " Like '" & sValue & "'", dbOpenForwardOnly)
                    ' Display the results to the Immeadiate Window
                    Do While Not oRs.EOF
                        Debug.Print oTable.Name & " - " & oRs(sFieldName)
                        oRs.MoveNext
                    Loop
                End If
            Next
        End If
    Next
       
    ' Clean Up
    Set oRs = Nothing
    Set oTable = Nothing
    oDB.Close
    Set oDB = Nothing
End Sub
'--------------------------------------------------------

Regards,

- Aaron.
0
 
CJ_SCommented:
Set rst = mydb.OpenRecordset("SELECT * FROM People WHERE Name LIKE 'Tim*';SELECT * FROM People2 WHERE Name LIKE 'Tim*' ", dbOpenDynaset)
do while not rst is nothing
   do while not rs.eof
      ...
      rs.movenext
   loop
   set rs = rs.nextrecordset
loop
0
 
CJ_SCommented:
of course the sql statement should be on one line.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
guyfromeastCommented:
First of all, if You use MS SQL server or Access, it is not possible to search  using something like
SELECT * FROM * WHERE ... it's verry funny SQL statement :) (* mean ALL). But ...
You can use dynamic SQL statement.
I am not super expert in Access, but I think it possible to obtain all table names from Access database, using some tricks or TableDefs maybe. In MS SQL DB, You have sysobjects table, from which You can obtain all table names. Important point here, You must have Name field in all databases You wish to search.
If You got all tablenames You need You can loop them in VB code and open RS again and again. In MS SQL server, You can create SQL statements dynamically, and execute it with exec(SQLStatement), so, just create StoredProc with one parameter (for using in LIKE part), create SQL stats in it and exec em there. Complete result(record)set can be build by usin UNION between selects.

GoodLuck().
0
 
guyfromeastCommented:
CJ_S, if it will work, it will return some records twicely, i adviced UNION between selects.
0
 
CJ_SCommented:
How would it return records multiple times? I am doing 2 seperate select statements...

regards,
CJ
0
 
guyfromeastCommented:
CJ_S, if it will work, it will return some records twicely, i adviced UNION between selects.
0
 
guyfromeastCommented:
Ok, I seen, so questin here, need this guy separate RSs or just single one from multple tables.

BTW, CJ_S, nice info, i nether used OpenRecordset in this way, thanx.
0
 
CJ_SCommented:
*LOL* I never use OpenRecordSet only ADO's Connection.Open and RecordSet.Open / Execute. I'm only assuming it will work in the OpenrecordSet too.

regards,
CJ
0
 
grooneAuthor Commented:
CJ, in order for yours to work you have to create a search query for each table.  In a db where their are 100 tables this string could be quite long.  There may also be a time where the tables for the database are unknown.

Hmmm
0
 
CJ_SCommented:
So will be the recordset.

Are you using SQL Server? If so I could show you a stored procedure which will do this.

Regards,
CJ
0
 
grooneAuthor Commented:
I ended up enumerating and it worked just fine.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.