You're along the right lines - "Select FieldName(s) From tableName
Where CustomerName = 'Criteria'"
But perhaps a better question would be why you have one table devoted
to each customer? A better database design would be to have a
customers table with each record devoted to a customer with proper ID
fields and linked foreign key fields. This will greatly simplify your
database and also your method of searching.
As for the way you have it now, you could make a seperate table with
all your table names in it, then when you go to search for a certain
customer, you get the table of table names in a recordset then in a
for loop use your recordset to build the sql to search for each one.
There is probably a better way in Access to do this, but I don't know
how - someone else may post it later. The best way would be above -
make a customers table (just one table) that will keep track of all
your customers.
st**********@Excite.com (Stuart Clark) wrote in message news:<c7**************************@posting.google. com>...
Hello,
I haven't done much with ASP but now I'm giving it a go! However, I'd
like to ask if anybody knows if what I need to do is possible.
I have a database with a lot of tables. Each table represents a
customer. As customers come and go, so tables will be added and
removed.
I need to create a search utility that will search all tables for
(e.g.) a customer name. I'm learning ASP so I'm not that advanced, but
I'm assuming it will use some kind of SQL statment along the lines of
SELECT [criteria] FROM [tablename]. Do I need to manually add each
table name or is there a command that will automatically select all
tables?
Many thanks in advance! All advice will be of use. I'm using
dreamweavr MX and Access 2000.
Cheers,
Stu.
moonklash