Search entire Access database.

[UKnod]

I have an access database which I am using with .net, I can search tables and maker very specific searches no problem, trouble is that I can't work out how to make global searches. IE search entire databse for "fred" or search a single table for "fred". Anyone know what the SQL string might look like:

[Begginnerdev]

This will be an extremely complex thing to do it VB.NET, but here is an example for SQL Server (Syntaticly Close to Access)

[UKnod]

Unfortunatly that example is writen in c# and I use .net. However I think I get the jist, basically there is no single sql statement, instead you get a list of tables and then search in each table in turn.

[Begginnerdev]

That example is actually SQL, and C# is a .Net Language.

The problem exists on querying each table.

If you have a DataSet with each table strongly typed you can try a For Each loop.

Either way, there is no single line statement to solve the problem.

[TnTinMN]

Not sure of what use this would be, but for OLEDB connections you could do it like this:

Private Sub Search()
   Dim conn As New OleDb.OleDbConnection("Your Connection String ")
   conn.Open()
   ' get all tables filtered to user tables
   Dim dbTables As DataTable = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"})
   conn.Close()

   For Each tableentry As DataRow In dbTables.Rows
      Dim cmd As New OleDb.OleDbCommand("Select * From [" & tableentry("Table_Name").ToString() & "]", conn)
      Dim da As New OleDb.OleDbDataAdapter(cmd)
      Dim tmpTable As New DataTable
      da.Fill(tmpTable)

      ' get a list of string datatype columns in tmpTable
      Dim cols As List(Of DataColumn) = (From col In tmpTable.Columns _
                                         Select dc = CType(col, DataColumn) _
                                         Where dc.DataType Is GetType(String) _
                                        ).ToList()

      For Each r As DataRow In tmpTable.Rows
         For Each dc As DataColumn In cols
            If r.ItemArray(dc.Ordinal).ToString().Contains("fred") Then
               'found one
               Debug.WriteLine("Table:  " & tableentry("Table_Name").ToString())
               Debug.WriteLine("Column:  " & dc.ColumnName)
               Debug.WriteLine("Value:  " & r.ItemArray(dc.Ordinal).ToString())

            End If
         Next
      Next

   Next
End Sub