Solved

Recent Question Revisited - SELECT * FROM

Posted on 2001-07-08
12
268 Views
Last Modified: 2012-06-21
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?


0
Comment
Question by:groone
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 6262819
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 6262821
of course the sql statement should be on one line.
0
 
LVL 1

Expert Comment

by:guyfromeast
ID: 6262837
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
 
LVL 1

Expert Comment

by:guyfromeast
ID: 6262842
CJ_S, if it will work, it will return some records twicely, i adviced UNION between selects.
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6262849
How would it return records multiple times? I am doing 2 seperate select statements...

regards,
CJ
0
 
LVL 1

Expert Comment

by:guyfromeast
ID: 6262852
CJ_S, if it will work, it will return some records twicely, i adviced UNION between selects.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Expert Comment

by:guyfromeast
ID: 6262861
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 6262863
*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
 
LVL 3

Author Comment

by:groone
ID: 6262893
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 6262903
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
 
LVL 1

Accepted Solution

by:
Aaron_Young earned 100 total points
ID: 6262975
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
 
LVL 3

Author Comment

by:groone
ID: 6282058
I ended up enumerating and it worked just fine.  Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now