locked
Search entire access database RRS feed

  • Question

  • User-1782670433 posted

    Hi,

    Is there a way to do a search on all tables in my access database? I would like the user to enter a keyword in my textbox then when the user it hits search it will search all tables the populate in a gridview? I have like 10 tables and I was trying a sql join but the qry is way to big for my liking. Any other ways? Thanks!

    Monday, August 27, 2012 2:17 PM

Answers

  • User-1716253493 posted

    Is all columns each table identic? Try using UNION ALL

    SELECT A, B, C FROM TABLE A

    UNION ALL

    SELECT A, B, C FROM TABLE B

    UNION ALL

    SELECT ...

    and save it in access as Query1

    You can use selectcommand="SELECT A, B, C FROM Query1 WHERE ......" and populate the result to gridview

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2012 4:48 AM

All replies

  • User-1716253493 posted

    Is all columns each table identic? Try using UNION ALL

    SELECT A, B, C FROM TABLE A

    UNION ALL

    SELECT A, B, C FROM TABLE B

    UNION ALL

    SELECT ...

    and save it in access as Query1

    You can use selectcommand="SELECT A, B, C FROM Query1 WHERE ......" and populate the result to gridview

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2012 4:48 AM
  • User-1782670433 posted

    Thanks I will give that a shot and let you know

    Tuesday, August 28, 2012 8:38 AM
  • User3866881 posted

    I have like 10 tables and I was trying a sql join but the qry is way to big for my liking.

    Hi,

    If the structures of the tables are quite different from each other,and you have to use join+Full Text Search to deal with the problem.

    Regaurds!

    Tuesday, August 28, 2012 9:58 PM
  • User-1199946673 posted

    you have to use join+Full Text Search

    In Access?

    Wednesday, August 29, 2012 2:08 AM
  • User-1782670433 posted

    Ya, this is an access database and there are about 10 tables. Just about all have a different table structure so will this join full work?

    Thursday, August 30, 2012 12:28 PM
  • User-1199946673 posted

    Ya, this is an access database and there are about 10 tables. Just about all have a different table structure so will this join full work?

    No, a UNION, as suggested in the first reply most likely will work, although performance might be terrible when you have many records.

    Thursday, August 30, 2012 5:46 PM
  • User-1716253493 posted
    better way is merge all into a new table. Add one colomn to determine from witch table like category. Use one table, remove the 10 tables.
    Thursday, August 30, 2012 7:08 PM