Creating a View/Linked Tables

Question

Creating a View/Linked Tables

wheelsthatgrip

Old Hand

Points: 376
More actions
February 24, 2010 at 9:34 am

#217256

Hello All,
I spend most of my time in Access and have run into a limitation that may require the use of SQL server. A little background first....
Currently I use Access 2007 to connect to an Oracle Database to run ad hoc queries. This has always worked well but recently changes were made to the Oracle DB that are causing me issues. They increased the number of fields in several tables beyond the 255 that Access is capable of reading.
So...my question is twofold:
1. Can you create Linked data sources like you do in Access in SQL server 2003
2. If so, can you then build a view in SQL server 2003 from that link.
I appreciate any input or ideas you may have.
Regards,
Keith

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

First of all SQL Server 2003 doesn't exists!

You can create Linked Server in SQL Server to Access file, when you create it you can see the tables and views in the Access file, selecting data and retrieving the results from Access file to SQL Server.

You cannot create objects (tables or views) in Access file from SQL Server SSMS (SQL Server Management Studio).

On the other side, you can create linked tables in Access file connected to SQL Server and sure you can create views from Linked Tables retrieving data from SQL Server.

Is it clear to you!?

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

wheelsthatgrip Old Hand Points: 376 More actions · Answer 2

Hi Dugi,

Thanks for your reply and the suggestions!

You are correct, SQL Server 2003 it's not, SQL server 2000 on a W2003 box. 🙂

I will run with the information you have given me and see what I can sort out.

Regards,

Keith

Dugi SSCoach Points: 17998 More actions · Answer 3

I can see that you mentioned the SQL Server 2000...so here is the forum for SQL Server 2005!

Anyway, next time when you have a question about SQL Server 2000 you can post here

N'joy your stay in one of the best forum for SQL Server including versions 6.5, 7, 2000, 2005, 2008 and SQL Azure!

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

wheelsthatgrip Old Hand Points: 376 More actions · Answer 4

I found the following on MSDN that covers what I am trying to do:

http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Thanks for getting me started in the right direction!:-D

Regards,

Keith

Dugi SSCoach Points: 17998 More actions · Answer 5

You are welcome! Comeback again in the forum and you will find much more than your problems ....!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]