Linking to tables from another SQL database

[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

 

[Kevin3NF]

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

 

[Sylvain Lafontaine]

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.

 

[Kevin3NF]

Cool...didn't know you could create linked tables from within the ADP
interface

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

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com

 

[aaron.kempf]

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

 

[aaron.kempf]

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

 

[Vayse]

Thanks guys, will give that a try and get back to you if I have any issues.

 

[Vayse]

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

 

[Kevin3NF]

Access 2000 ADPs gave me lots of goofy issues. 2002 (XP) and up seemed much
easier to work with. But it been awhile

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

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com

 

[aaron.kempf]

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

 

[Vayse]

When I tried it on an Access XP machine, that option was there.
And very handy it is too.

 

[aaron.kempf]

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