How to connect to a SQL Linked Server

[GREABR]

How do I do this

I have set up a Linked Server on my SQL Server.
I can see all of my tables for the Linked Server in Enterprise Manager.

How do I create an Access project to use these tables from the linked server?


"Don't be afraid to do things wrong, before you do them right"
Bradley Greaves

 

[cmmrfrds]

When you connect your project to the database the linked server tables should be available like any other table in the database.

 

[GREABR]

When I setup the connection in the Access Project, I specify the SQL server and login, then I can pull down a list of databases available on that SQL Server. This list does not include the Linked Server or it's tables.

In Enterprise Manager, the tree displays the SQL Server, and under the server is the databases folder which contains all of the databases available to my Access Project.

But the Linked Server is in the Security Folder under the SQL Server.

How can Access Find this Linked Server from the connnection information?


"Don't be afraid to do things wrong, before you do them right"
Bradley Greaves

 

[cmmrfrds]

Read up on the OPENQUERY Function is SQL Server, it is used to select data from the linked server. The OPENROWSET Function does the same type of thing, but it creates the link dynamically so that you don't need to use the linked server - need to supply all the link information in the SQL. Probably the easiest is to use the 4 part naming convention and access the server and database from SQL in a pass-thru query, or thru ADO. Of course, all this depends on your login having the correct permissions. You won't be able to see the table under the access project, but can get at the data from one of the mentioned suggestions.

Example.
Select * from remoteServName.dbname.dbo.tablename as A
inner join dbo.connectedtable as B
on A.key = B.key

Enterprise Manager Help has examples on all of the above.