connect to multiple odbc in pass-through query

[jordena]

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?

 

[jordena]

I finally worked out the problem. Even though I was running the stored
procedure on the server, MS Access didn't like the fact that I used <> null
instead of IS NOT NULL. Once I had worked this out and tried running the
pass-through query again it worked- it returned the results that I was
expecting.