Linking to tables from another SQL database

  • Thread starter Thread starter Vayse
  • Start date Start date
V

Vayse

Hi
I have an adp, lets say it linked to SQL database called SQL_A.
In another SQL database, SQL_Staff, there is a list of staff.
I need to look up that list in my adp. However, as an adp can only link to
one database, how do I get around this?
Is is possible to create a view in SQL_A that links to the table in
SQL_Staff?
The staff list changes on a daily basis, and SQL_A would need to be able to
see the updated list at any time.
Regards
Vayse
 
Look up "Linked Servers" in SQL Server books online if the two databases are
on different servers.

If on the same server create a view that uses the database name in thefrom
list

Create View myView as
Select *
From SQL_Staff..MyStaffTable join
SQL_A..MyOtherTable on SQL_Staff..MyStaffTable.ID =
SQL_A..MyOtherTable.id
Where whatever....

Use that view in your ADP

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
 
If it's located onto another server and you don't remember the T-SQL syntax,
the File | Get External Data | Link Tables... will create the linked server
and its associated view for you. You might want to play with this option.
 
are you talking about on the same server?

Select * from master.dbo.sysobjects

you can write a view in northwind that does that right?

likewise; this naming convention database.owner.object allows you to
easily pull stuff from an unlimited # of databases; out of the box--
without any code--

it's about 100 times better than crap in MDB

-Aaron
 
Kevin;

that is because Microsoft sent up a bunch of retards to market this
feature.
it _SHOULD_ be one of those features that you see tv ads for.

the benefits of ADP are everywhere and retarded blu-heads are still
bitching how it's impossible to do all this stuff

when in all reality ADP and SQL Server are about 100 times more
powerful than MDB

-Aaron
 
Its on the same server, so it worked fine for me creating the view using
T-SQL.
Sylvain - I don't get that option in my adp. I only have File/Get External
Data/Import Data.
I'm using Access 2000, so maybe thats why?
Either way it works now!
Thanks
Vayse
 
yeah.. 2000 was a bit buggy; but I still prefer it for some tasks.

like table creation; the table creation in 2000 is a lot better than
anywhere else; it's simple like a spreadsheet; and its' flat.

you can churn from table to table really fast in 2000.

but sproc design is an entirely different matter; the wizards included
with 2002 / 2003 are to die for.. I love this interface; and I dont
think that visual studio or anywhere else has this same functionalty

well technically; I think that SSMS includes this and it's superior; it
will allow a join and an update at the same time i beleive

but then again SSMS doesn't have data entry forms and reporting

-Aaron
 
of course you will then have issues such as 'double hop authentication'

'trust for delegation' and setspn on the service account for sql
server.. if you're having problems; i'd look into going that route

-Aaron
 
Back
Top