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:

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

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.

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.

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.