 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / January 2007Linking to tables from another SQL database
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  SignatureKevin Hill 3NF Consulting http://www.3nf-inc.com/NewsGroups.htm Real-world stuff I run across with SQL Server: http://kevin3nf.blogspot.com > Hi > I have an adp, lets say it linked to SQL database called SQL_A. [quoted text clipped - 7 lines] > Regards > Vayse 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) > Look up "Linked Servers" in SQL Server books online if the two databases > are on different servers. [quoted text clipped - 22 lines] >> Regards >> Vayse Cool...didn't know you could create linked tables from within the ADP interface :) SignatureKevin 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 [quoted text clipped - 27 lines] >>> Regards >>> Vayse 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 > Cool...didn't know you could create linked tables from within the ADP > interface :) [quoted text clipped - 49 lines] > >>> Regards > >>> Vayse- Hide quoted text -- Show quoted text - Thanks guys, will give that a try and get back to you if I have any issues. > Cool...didn't know you could create linked tables from within the ADP > interface :) [quoted text clipped - 30 lines] >>>> Regards >>>> 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 > Cool...didn't know you could create linked tables from within the ADP > interface :) [quoted text clipped - 30 lines] >>>> Regards >>>> Vayse Access 2000 ADPs gave me lots of goofy issues. 2002 (XP) and up seemed much easier to work with. But it been awhile SignatureKevin Hill 3NF Consulting http://www.3nf-inc.com/NewsGroups.htm Real-world stuff I run across with SQL Server: http://kevin3nf.blogspot.com > Its on the same server, so it worked fine for me creating the view using > T-SQL. [quoted text clipped - 39 lines] >>>>> Regards >>>>> 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 > Access 2000 ADPs gave me lots of goofy issues. 2002 (XP) and up seemed much > easier to work with. But it been awhile [quoted text clipped - 69 lines] > >>>>> Regards > >>>>> Vayse When I tried it on an Access XP machine, that option was there. And very handy it is too. > Access 2000 ADPs gave me lots of goofy issues. 2002 (XP) and up seemed > much easier to work with. But it been awhile [quoted text clipped - 42 lines] >>>>>> Regards >>>>>> Vayse 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 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 > Hi > I have an adp, lets say it linked to SQL database called SQL_A. [quoted text clipped - 7 lines] > Regards > Vayse |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.