Solved

Recent Question Revisited - SELECT * FROM

Posted on 2001-07-08
12
269 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

861 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

25 Experts available now in Live!

Get 1:1 Help Now