Solved

Recent Question Revisited - SELECT * FROM

Posted on 2001-07-08
12
270 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vb.net 2008 2 59
to transfer string from C lanaguage to VBA 4 66
Spell Check in VB6 13 113
Protecting vb6 & .Net code Obfuscation 18 121
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

773 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