Search every table for a value? 1

[Varga]

Hello,
How can I create a recordset that will search every table in my database for a specific value?

Not all the tables contain the field [Customer].

E.g.
Dim sSQL As String
sSQL = "SELECT *.[Customer], *.[User] FROM * " & _
"WHERE (*.[Customer] = "John&quot "
Dim Rs As Recordset
Set Rs = CurrentDb.OpenRecordset(sSQL)

If Rs.RecordCount > 0 Then
DoCmd.OpenForm...

If the value exists in another table I want to open a form and display the matching records. Once this code works, I should be able to fill a listbox with the matching records, using the same SQL code, in the forms' Load procedure.

Please let me know of a good way to do this,
Thank you,
Blair

 

[SBendBuckeye]

There is no From * in SQL. Can you explain what it is you are trying to do? Perhaps there are other ways to help.

Good Luck!

 

[Varga]

I need to use a current value (e.g. John Smith) and search all the other tables in my database to see if a matching record exists.
I want to select the tables where (LIKE "*cust&quot.
Then search all of thoses tables for the current value.
If the value exists I'll open a form showing the matching records.

I need help with creating the table recordset then searching it.

 

[SBendBuckeye]

Still not sure I follow what you are trying to do. Are you saying you have tblEastCust, tblWestCust, tblNorthCust, etc. with identical field layouts or that you just want to search all fields of all tables for a match? Perhaps if you gave an example including table names, field names and values showing what you are trying to do, it would be easier to help you.

Good Luck!

 

[Varga]

(This is how I'm trying to select the appropriate tables)
Dim sSQL As String, Rs As DAO.Recordset
sSQL = "SELECT MSysObjects.Name, MSysObjects.Type " & _
" FROM MSysObjects " & _
" WHERE ((MSysObjects.Name) LIKE "*Cust" " & _
&quot AND ((MSysObjects.Type)=1)"

Set Rs = CurrentDb.OpenRecordset(sSQL)

(How can I display the results of the recordset to see if it worked?)

Yes all the specific tables would have the same layout. I need to search all these tables for a specific value (e.g. John Smith)

When the user selects a customer name, I want Access to select only tables where the name ends with "Cust", then search those tables to see if that customer allready exists. If it does, I want to open a "Duplicate Customer" form displaying those customers and the tables they came from, in a listbox.

(Maybe recordsets are not the best way? I don't know.)

(fyi I have trouble writing SQL statements when there is three or more "WHERE" statements. I don't know why.)

I need to pick the tables,
Search the tables for the value,
<If matches exist>
Open form,
Set forms listbox rowsource to matching records.

I hope this helps to explain what I'm trying to do.
Thank you,
Blair

 

[SBendBuckeye]

You are on the right track. Try the following and see if it works. Using single quotes ' around the Like phrase makes building the SQL string a little bit easier. You don't need all of the parentheses that Access generates when it builds the code for you.

Dim sSQL As String
Dim Rs As DAO.Recordset

sSQL = &quot;SELECT MSysObjects.Name, MSysObjects.Type &quot; & _
&quot;FROM MSysObjects &quot; & _
&quot;WHERE MSysObjects.Name Like '*cust' &quot; & _
&quot; AND MSysObjects.Type=1 &quot; & _
&quot;ORDER BY MSysObjects.Name;&quot;

Set Rs = CurrentDb.OpenRecordset(sSQL)
Rs.MoveFirst 'Go to first record

Do While Not Rs.EOF 'Read until end of file
MsgBox Rs.Fields(0).Value & &quot;, &quot; & Rs.Fields(1).Value
Rs.MoveNext
Loop

Rs.Close
Set Rs = Nothing

May not be able to return any more today so a couple more questions to build upon what you are doing.

1. Do all of the tables selected by the above have the same formats and field names (eg CustName, Customer, etc)?

2. Do you want to match all table references matching Smith or just the first one?

Good Luck!

 

[Varga]

Private Sub test_Click()

Dim sSQL As String, Rs As DAO.Recordset, mytext As String Dim mytext2 As String, sSQL2 As String, mytext3 As String
        sSQL = "SELECT MSysObjects.Name, MSysObjects.Type" & _
        " FROM MSysObjects " & _
        " WHERE MSysObjects.Name LIKE '*Cust'" & _
        " AND MSysObjects.Type=1" & _
        " ORDER BY MSysObjects.Name;"
    
    Set Rs = CurrentDb.OpenRecordset(sSQL)
    
    Rs.MoveFirst 'Go to first record
    Do While Not Rs.EOF 'Read until end of file
        mytext = mytext & "[" & Rs.Fields(0).Value & "],"
        Rs.MoveNext
    Loop
    
    Rs.MoveFirst
    Do While Not Rs.EOF 'Read until end of file
        mytext2 = mytext2 & "[" & Rs.Fields(0).Value & "].[Customer Name],[" & _
        Rs.Fields(0).Value & "].[Cust User],"
        Rs.MoveNext
    Loop
    
    Rs.Close
    Set Rs = Nothing
    
    If Len(mytext) > 0 Then
        mytext = Left(mytext, Len(mytext) - 1) '(take off last ,)
    End If
    If Len(mytext2) > 0 Then
        mytext2 = Left(mytext2, Len(mytext2) - 1) '(take off last ,)
    End If

    sSQL2 = "SELECT " & mytext2 & _
        " FROM " & mytext & ";"
        
    Set Rs = CurrentDb.OpenRecordset(sSQL2)
    Rs.MoveFirst
    Do While Not Rs.EOF 'Read until end of file
        mytext3 = mytext3 & Rs.Fields(0).Value & ", " & Rs.Fields(1).Value & _
        ", " & Rs.Fields(2).Value & ", " & Rs.Fields(3).Value
        Rs.MoveNext
    Loop
    Set Rs = Nothing
    MsgBox (mytext3)
End Sub

This basically works but I still have a few problems.

First I choose the tables that end with &quot;Cust&quot;.
Then set mytext to &quot;[A Cust],[B Cust]&quot;
Then set mytext2 to &quot;[A Cust].[Customer Name], [A Cust].[Cust User], ...&quot;
I then set sSQL2 to &quot;SELECT &quot; & mytext2 & _
&quot; FROM &quot; & mytext & &quot;;&quot;
Then create a RecordSet from sSQL2.
Then display the results of the recordset.

The Query that I'm basing this on is:

"SELECT [A Cust].[Customer Name], [A Cust].[Cust User], [B Cust].[Customer Name], [B Cust].[Cust User]
FROM [A Cust], [B Cust];"
But this query stores it's info into many fields, but all in one record.

I wont know how many fields will be in the recordset.
e.g. [code]mytext3 = mytext3 & Rs.Fields(0).Value & ", " & Rs.Fields(1).Value
'then field 2 value and field 3 value and so on.

How could I get a messagebox to display field 0 and 1 on the first line then field 2 and 3 on the second line, and so on for each set of fields?

Is there a better query I could use to create 2 fields per record, and multiple records?

Please let me know,
Thanks,
Blair :-)

 

[Varga]

Well, I figured out a MUCH easier way to do this. I'm using the (For Each 'Table' In CurrentDb.Tabledefs ... Next) method to cycle through one table at time. I then use (If...Then) statements to select or exclude certain tables. Then for each one of the selected tables I use the 'T.Name' property to create a recordset that searches for a matching 'Customer Name' record in the 'T.Name' table, and write the result to the strMsgBoxTxt string.

Then, if there is any data stored in the strMsgBoxTxt string, I open a &quot;vbInformation&quot; message box and use the strMsgBoxTxt to create the content of the message box, with the title &quot;Another User has a matching record for this customer:&quot;.

Here's how it looks:

Dim T As TableDef
Dim strMsgBoxTxt As String
Dim rsCustNam As DAO.Recordset, strCustNam As String

strMsgBoxTxt = ""

For Each T In CurrentDb.TableDefs    
 If (T.Name Like "* Cust") Then	‘(If Table name ends with “Cust”)
  If Not T.Name = “UserID Cust” Then	‘(Not user’s Cust table)
  
    strCustNam = "SELECT [" & T.Name & "].[Cust User], [" & T.Name & _
      "].[Customer Name] FROM [" & T.Name & "] WHERE [" & T.Name & _
      "].[Customer Name] = " & Chr(34) & Me.Customer_Name.Value & Chr(34)
    
    Set rsCustNam = CurrentDb.OpenRecordset(strCustNam)
    
    If (rsCustNam.RecordCount > 0) Then
      
      rsCustNam.MoveFirst
      
      Do While Not rsCustNam.EOF
       Let strMsgBoxTxt = strMsgBoxTxt & rsCustNam.Fields(0).Value & _
         " has a matching record for " & rsCustNam.Fields(1).Value & _
       vbCrLf & vbCrLf      
       rsCustNam.MoveNext
      Loop
            
    End If
    rsCustNam.Close
    Set rsCustNam = Nothing

  End If
 End If
Next

If Len(strMsgBoxTxt) > 0 Then
  MsgBox strMsgBoxTxt, vbInformation, _
    "Another User has a matching record for this customer:"
End If

I hope this helps someone else trying to do something similar. This way works very well, and is much easier than the method I was trying before.

Blair