How to link table from another DB -- sql server dts

orange
1/31/2005 9:47:55 PM

It is possible to link Access database to SQL as linked server - in
enterprise manager or by sp_addlinkedserver procedure. For detail see BOL.
Regards
Orange

[quoted text, click to view]

Mike
2/1/2005 2:13:03 AM

Could you please give me more detailed explanations? Which are the steps to
follow?
I am new in SQL Server environment: what is BOL?

Thanks a lot.
Mike.

[quoted text, click to view]

Mike
2/1/2005 2:15:03 AM

I,m sorry but could you please give me more details?

Regards,
Mike.

[quoted text, click to view]

Geo
2/1/2005 8:44:31 AM

You can create a link from Access to SQL server using new > link table and
selecting ODBC Databases.
You could also create a view of the MS db table from within SQL server.
Hope this helps.
Geo
[quoted text, click to view]

Geo
2/1/2005 11:57:32 AM

Both these methods are on the assumption that you have administrative access
to both db and they are on the same network.

[Link Table]

If you are going to use the linked table method you would open MS Access
highlight the table you want to link to SQL select new>link table then in
the file types menu select ODBC Databases You will then be prompted to
select a source, select new SQL Server and browse to the server you are
connecting to just follow all the prompts.

[SQL View]

To create a view in SQL it would be something like this.

CREATE VIEW MyView AS
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MyAccessdb.mdb';'MyUserName';'MyPassWord', MyTableName)

There is more information at the following URL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp

Geo

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : How to link table from another DB