Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Are you just wanting to perform queries that can join table data, or are you looking to replace a front-end entirely? –  Eric Higgins Sep 26 '12 at 20:32
    
@Eric I'm pretty sick of the frontend. Mainly because you can't look at the results of a query you ran, without making the text of the query disappear. Well you can, but it requires an external program. I'd really like the results of the query to re-appear in a tabbed interface like SQuirreL has; this allows you to look at differences between result sets when writing a query. –  leeand00 Sep 26 '12 at 20:36
    
(and yes I'd like to do joined queries as well) –  leeand00 Sep 26 '12 at 20:38

2 Answers 2

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
share|improve this answer
1  
I'm thinking more along the lines of connecting to both a FoxPro and an MS Access (JET) database using SQL Server as the front-end for them both (and running join queries between them). –  leeand00 Sep 27 '12 at 3:37
1  
You can use SQL Server to run distributed queries against various OLE-DB sources such as FoxPro and Jet databases. Check duckduckgo.com/… for more info on how to do it. Be warned, it is a fairly involved process and querying remote databases can be slow if there is a lot of data involved. –  Max Vernon Sep 27 '12 at 3:44
    
You may be better off automating an import of the data into SQL Server and running local queries. –  Max Vernon Sep 27 '12 at 3:44

Since you've indicated in comments that you may want to replace the front-end entirely, check out Visual Studio Lightswitch. I'm working with it presently, and it's great... Too simplistic for a full-fledged .NET developer, but great for throwing a front-end on some data without having to engage a development team.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.