how to search all tables for a value?

[Hcoms]

Hello ,

Thanks to Steve i know have linked a number of excel docs in access. What i
now would like to do is to search all tables in access for a specific value.
The SQL statement would be select F2 from ( all tables ) WHERE F2="Customer
Name".

Any help would be greatly appreciated

Cheers!!

 

[Hcoms]

To give you some more details on why i am doing this. A number of excel docs
are used by different sales people to store transaction with customers. What
needs to be done is to be able to search all of these sales sheets for a
particular client name. The way that i am doing this ( i have written a vb
program to open up each excel doc and search , but on their network it is to
slow) is to link the tables in access and then to do the search . This is
considerable quicker . However at present i do not know the best way to
search multiple tables for a value in the field "F2"

Cheers

 

[Steve]

You could store the Linked table names in a seperate
table, then Use a Do....Loop to search through each table,
replacing the required part in your sql with the Linked
table name.

Dim rs as recordset
dim rs2 as recordset

Set rs = db.openrecordset("Linked_Tables")

Do Until rs.eof

Set rs2 = db.openrecordset("SELECT F2 FROM " & rs
("Linked_Table_Name") & " WHERE F2 = 'Customer Name')

rs.MoveNext

Loop

You would also need somewhere to store the tables where
the customer name was found - Maybe a Yes/No field in the
Linked_Tables table......

Hope this makes sense

Steve.

 

[onedaywhen]

You could UNION ALL:

SELECT
'Workbook1' AS WorkbookName,
ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM
Workbook1
WHERE
ColC = 'Customer Name'
UNION ALL
SELECT
'Workbook2' AS WorkbookName,
ColX AS Col1, ColY AS Col2, ColZ AS Col3
FROM
Workbook2
WHERE
ColZ = 'Customer Name'
UNION ALL
...

Or create a summary table and query from there:

INSERT INTO
WorkbookSummaryTable
(WorkbookName, Col1, Col2, Col3)
SELECT
'Workbook1', ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM
Workbook1
UNION ALL
SELECT
'Workbook2', ColX AS Col1, ColY AS Col2, ColZ AS Col3
FROM
Workbook2
UNION ALL
...
;
SELECT WorkbookName
FROM WorkbookSummaryTable
WHERE Col3 = 'Customer Name'
;

 

[John Nurick]

If there are only a few of tables, you can use a union query, e.g.

SELECT F2, "tblOne" AS TableName FROM tblOne
UNION
SELECT F2, "tblTwo" AS TableName FROM tblTwo
UNION
....
WHERE F2 = "Customer Name"

But can't you persuade them to keep their important sales data in a
proper database instead of a bunch of worksheets scattered all over the
network?