我怎么会做这搜索值的数据库中的所有表中获得的一种形式 [英] How would I make a form which searches for values in all tables of a database in access

问题描述

我试图让该搜索里面的所有表在数据库中的价值形式（有超过1表）。其结果将显示为这本将出现在该表的名称，如果有人能帮助我，这将是很好的。

I am trying to make a form which searches for the value inside all of the tables in the database (there are more than 1 table). The result will be displayed as the name of the table which this appears in. If someone can help me that will be nice.

总之，我有一个文本框和按钮的形式。我输入的搜索字符串（如183939），并单击按钮。它的帮助搜索值（183939）的所有表在数据库中的字段里面，如果找到该值，则显示它出现在表的名称，谢谢。

In short, I have a form with a textbox and button. I enter the search string (for example 183939) and click on the button. It searches the value (183939) inside all the fields in the tables in the database, and if the value is found, then it displays the name of the table that it appears in. Thanks for the help.

推荐答案

我认为这是一个坏主意，因为它可能需要很长的时间，并提供因也在寻求系统表混乱的结果......但以下功能如果没有找到将返回包含搜索词或没有所有表名的数组。调用的例子是这样的： theTables = containingTable（你好）其中theTables是一个变种。一个限制是，这将失败的多值字段。

I think this is a bad idea because it could take a very long time, and provide confusing results due to also searching system tables... but the following function will return an array of all table names containing the search term or nothing if it wasn't found. Calling example is such: theTables = containingTable("hello") where theTables is a variant. A limitation is that this will fail for multi-valued fields.

Function containingTables(term As String)
    Dim db As Database
    Dim tds As TableDefs
    Dim td As TableDef
    Set db = CurrentDb
    Set tds = db.TableDefs
    For Each td In tds
        For Each f In td.Fields
            On Error Resume Next
            If DCount("[" & f.Name & "]", "[" & td.Name & "]", "[" & f.Name & "] LIKE '*" & term & "*'") Then
                If Err.Number <> 0 Then
                    Debug.Print Err.Number, Err.Description
                    Err.Clear
                    On Error GoTo 0
                Else
                    containingTables = containingTables & td.Name & ","
                    Exit For
                End If
            End If
        Next
    Next
    Set tds = Nothing
    Set db = Nothing
    'Alternate Version
    if Len(containgingTables) then containingTables = Left(containingTables, Len(containingTables) - 1)
    'Original Version
    'if Len(containgingTables) then containingTables = Split(Left(containingTables, Len(containingTables) - 1), ",")
End Function

要显示的结果与另一个版本，只需使用： MSGBOX（containingTables（搜索关键词）），其中搜索关键词无论是你正在寻找。

To display the results with the alternate version, just use: Msgbox(containingTables(searchTerm)) where searchTerm is whatever you are searching.

这篇关于我怎么会做这搜索值的数据库中的所有表中获得的一种形式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！