Sql-server – Replacing the MS-Acccess frontend for accessing linked tables with SQL Server

linked-serverms accesssql server

Is it possible to use SQL Server / SQL Server Management Studio to do queries across linked tables the way that you would do them in MS-Access?

The MS-Access front-end is frustrating and time consuming to say the least, which has led me to search for alternatives. I started using SQuirreL SQL, and was quite pleased with it, but it doesn't have the linked table functionality so you're somewhat limited on the queries that can be run.

Also, can you provide me with suggestion for a tutorial or book on how to set something like that up?

Best Answer

SQL Server Management Studio allows you to create multiple versions of queries where you can see the SQL text and results simultaneously.

You can use dotted-notation to refer to tables in separate databases on the same server like:

SELECT * FROM database.schema.table

If you need to query data from multiple servers, you can create linked servers in SQL Server that provide all the capabilities of an Access front-end. This allows you to use 4-part dotted-notation like:

SELECT * FROM server.database.schema.table

For example, this code creates 2 Databases on the local SQL Server, with 2 tables that contain a single record each. The SELECT at the end joins data from the 2 tables into a single output:

CREATE DATABASE Test1 ON PRIMARY 
    (NAME = 'Test1_Data'
        , FILENAME = 'C:\Temp\Test1_data.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
    )
LOG ON
    (NAME = 'Test1_Log'
        , FILENAME = 'C:\Temp\Test1_log.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
);

CREATE DATABASE Test2 ON PRIMARY 
    (NAME = 'Test2_Data'
        , FILENAME = 'C:\Temp\Test2_data.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
    )
LOG ON
    (NAME = 'Test2_Log'
        , FILENAME = 'C:\Temp\Test2_log.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
);
GO

USE Test1;
GO
CREATE TABLE TestTable1
(
    ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable1 IDENTITY(1,1)
    , TestText nvarchar(255)
);

INSERT INTO TestTable1 (TestText) VALUES ('This is table 1 in database 1');


USE Test2;
GO
CREATE TABLE TestTable2
(
    ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable2 IDENTITY(1,1)
    , TestText nvarchar(255)
);

INSERT INTO TestTable2 (TestText) VALUES ('This is table 2 in database 2');


SELECT * FROM Test1.dbo.TestTable1
UNION ALL
SELECT * FROM Test2.dbo.TestTable2;

The output is:

ID  TestText
1   This is table 1 in database 1
1   This is table 2 in database 2
Related Question