Link a View from one database to another

[Zoomy]

I have an microsoft access project (access 2000), in that project I have connected it to a sql server (2000) called "ServerDev" using the datalink form. The database I connected to is called "HazardManagement". Here is my problem. I used datalink because this access front end will be used on about 200 computers on a very large network and we didnt want to have to set up dsn's on each machine. Now I have view called "Facility" from another database called "Warehouse" which is on the same sql server. I need to include that view in my access project but the datalink form allows only one defined link. How best to inlcude that view without a dsn? Or can I link that view inot my other sql srver database?

 

[cmmrfrds]

In your access project you can use the warehouse database directly by setting up another connection in vba code. The only problem is that you won't be able to join tables in that database to tables in HazardManagement - if you need to.

If it is just another database on the same server, then qualify the select with the db name. It won't show the table in the list of tables in Access project but you can use it anyway on the same connection as your primary link. You can setup as many connections to different database/servers as you need.

select * from warehouse.dbo.Facility

If warehouse is in a different sql server then, you will need to do a linked server from the ServerDev to the warehouse server.