MS Access question is further down, the beginning is an overview of what I've
been doing on the server side.

I have a stored procedure in Sybase ASE that is linking to 2 databases which
are on the same server (Its T-SQL so it would be the same on SQLServer). The
stored procedure is something like this:

create table #a
(...)

insert #a
select *
from   db1..tbl1

create table #b
( field1 varchar(10)
, field2 varchar(10)
, field3 varchar(10)
)

insert #b
values ('Heading1', 'Total AH', 'Total NAH')

insert #b /* NOT WORKING IN ACCESS */
select column1, column2, column3
from  #a, db2.tbl2
where ......

insert #b
values ('Heading2', 'Total AH', 'Total NAH')

insert #b /* NOT WORKING IN ACCESS */
select column1, column2, column3
from  #a, db2.tbl2
where ......

select * from #b

The stored procedure produces the correct results when running directly from
sybase:

Heading1     Total AH    Total NAH
abc              1                2
bcd              2                7

Heading2     Total AH    Total NAH
ere              1               3
dse              10             7
.....

I execute the procedure through a pass-through query and I get the following
results:
Heading1     Total AH    Total NAH

Heading2     Total AH    Total NAH
...

I am using a pass-through query on MS Access and the odbc property is set to
db2, which is where the stored procedure sits. I have tried changing the odbc
to db1 and exec db2.dbo.proc but that doesn't work either- I get the same
results with none of the data returning.

I have tried writing some VBA to connect to the first database and then call
the stored procedure whilst that connection is open- but that doesn't work
either.

Is it possible to do what I want?