access 2003 join on two sqlserver 2000 tables (one table is from a linked navision 3.70 server on sq

[Hubert Mayr]

Hi,

I have a sql server 2000 database with a linked server to navision 3.70 with
its own database.
I see all the navision tables in enterprise manager.

My question:

I want make in access 2003 a query (join with one table from the sqlserver
and one table from that linked server on sqlserver 2000).
How could I make that.
When I link in access a new table I only get the table on the sqlserver 2000
and not that from the linked server.
How could I also get the second table in my query window?

What is technical the difference against all two tables lying direct on my
sqlserver 2000 (table two not linked on sqlserver 2000)

thanks

 

[Ron Hinds]

You have a couple of options.

1.) Link the tables in the navision 3.70 database separately. To do this you
would need an ODBC connector for the navision 3.70 database.

2.) Use a SQL Pass-Through Query. Using this method, you don't need to link
the navision 3.70 table(s) in Access, since they are already linked to the
SQL Server. Set the Connect property of the QueryDef to be the same as your
SQL Server linked tables. You can find this out by opening the Debug Window
(Ctrl-G) and typing in the following and hitting Enter:

?CurrentDb.TableDefs("linked_SQL_table_name").Connect

Option 2 is probably the best since the query will execute on the SQL
Server, meaning it should perform better by an order of magnitude.