What is the SQL Server equivalent to a linked table in MS Access?

Question

I come from the MS Access database world where I'm used to link to tables in other databases using the Linked ODBC function.

What is the SQL Server equivalent to this feature?

PS: In SQL Server I want to link to other SQLServer databases on other servers and some Access databases.

Answer 1

On SQL Server you can set up LINKED SERVERS. From MSDN:

Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:

The ability to access data from outside of SQL Server.

The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

The ability to address diverse data sources similarly.

Here is an MSDN article on Configuring Linked Servers

Answer 2

I would agree with the other posters and say that linked servers is the way to go. Once you link the servers, you can address the table using the server.database.schema.tablename format to get to the table.

If you want to be elegant, you can create what is called a SQL VIEW around that remote table using a SELECT statment:

CREATE VIEW ViewName AS SELECT * FROM server.database.schema.tablename

This will get you closest to the Access-style linked table you seek. As long as you are only using one table in your view, records should updatable, insertable and deletable from that view.