Can I return multiple tables using Access 2000?

Is it possible to bind multiple datatable in one SQL statement?

Some thing like this stored procedure for example that returns three results:
select * from table1
select * from table2
select * from table3


This syntax is not working like it is on access.

Is there a way to get the same job done on Access 2000?

Thanks.

No, there's no way of combining SQL statements like that.

You have to treat them as 3 separate statements.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



wrote in message
om...
Is it possible to bind multiple datatable in one SQL statement?

Some thing like this stored procedure for example that returns three
results:
select * from table1
select * from table2
select * from table3


This syntax is not working like it is on access.

Is there a way to get the same job done on Access 2000?

Thanks.

Dear Yachea:

If the three tables have the same number of columns, and the same
column datatypes in the same order, you could build a union of them
with an itentifying first column TableNumber, like this:

SELECT 1 AS TableNumber, * FROM table1
UNION ALL
SELECT 2 AS TableNumber, * FROM table2
UNION ALL
SELECT 3 AS TableNumber, * FROM table3

From this query you could then perform any other query, filtering on
TableNumber to get the data only from one of the 3 tables. Or you
could get the data from all 3 tables taken together, or any 2 of the 3
tables.

By the way, in the stored procedure this is not one SQL statement, but
3 separate statements returning 3 separate recordsets. In access, the
3 separate recordsets could be returned by putting the 3 separate
statements into a macro and running the macro.

Perhaps if you explain what effect you're trying to achieve I could
advise you better.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 3 Aug 2004 14:46:25 -0700, wrote:

Is it possible to bind multiple datatable in one SQL statement?

Some thing like this stored procedure for example that returns three results:
select * from table1
select * from table2
select * from table3


This syntax is not working like it is on access.

Is there a way to get the same job done on Access 2000?

Thanks.

I am trying to bind multiple tables (different types of tables) in my
strongly typed dataset throw an oleDataAdapter using only one stored
procedure, as I guess, it's could be done on Sql server.

Is it possible then, to call a macro instead of a stored procedure
from my oleDataAdapter ?

Thank a lot for all your advices,

Yacine.


Tom Ellison wrote in message . ..
Dear Yachea:

If the three tables have the same number of columns, and the same
column datatypes in the same order, you could build a union of them
with an itentifying first column TableNumber, like this:

SELECT 1 AS TableNumber, * FROM table1
UNION ALL
SELECT 2 AS TableNumber, * FROM table2
UNION ALL
SELECT 3 AS TableNumber, * FROM table3

From this query you could then perform any other query, filtering on
TableNumber to get the data only from one of the 3 tables. Or you
could get the data from all 3 tables taken together, or any 2 of the 3
tables.

By the way, in the stored procedure this is not one SQL statement, but
3 separate statements returning 3 separate recordsets. In access, the
3 separate recordsets could be returned by putting the 3 separate
statements into a macro and running the macro.

Perhaps if you explain what effect you're trying to achieve I could
advise you better.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Yachea:

I don't know if you can use the macro from oleDataAdapter, but unless
it supports Office Automation, I doubt it. I would stick with using
the SP directly.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On 4 Aug 2004 08:06:28 -0700, wrote:

I am trying to bind multiple tables (different types of tables) in my
strongly typed dataset throw an oleDataAdapter using only one stored
procedure, as I guess, it's could be done on Sql server.

Is it possible then, to call a macro instead of a stored procedure
from my oleDataAdapter ?

Thank a lot for all your advices,

Yacine.


Tom Ellison wrote in message . ..
Dear Yachea:

If the three tables have the same number of columns, and the same
column datatypes in the same order, you could build a union of them
with an itentifying first column TableNumber, like this:

SELECT 1 AS TableNumber, * FROM table1
UNION ALL
SELECT 2 AS TableNumber, * FROM table2
UNION ALL
SELECT 3 AS TableNumber, * FROM table3

From this query you could then perform any other query, filtering on
TableNumber to get the data only from one of the 3 tables. Or you
could get the data from all 3 tables taken together, or any 2 of the 3
tables.

By the way, in the stored procedure this is not one SQL statement, but
3 separate statements returning 3 separate recordsets. In access, the
3 separate recordsets could be returned by putting the 3 separate
statements into a macro and running the macro.

Perhaps if you explain what effect you're trying to achieve I could
advise you better.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts