Inserting Data from SQL Server to Access and Vice Versa

[garpa]

In Access, you can perform the following:

INSERT INTO 'c:\mynewdatabase.mdb'
SELECT * FROM MyTable

In SQL Server, it falls over, not allowing the data to be transferred from teh SQL Server database into my MDB file.

I know you can do it via a DBO.mydatabase within another SQL database inside SQL Server, but how to you provide a provider to connect to an external database?

This would relieve a BIG headache for me. Thanks!

Carl Harrison.

 

[tlbroadbent]

Read about linked servers in SQL BOL.

Configuring Linked Servers

http://msdn.microsoft.com/library/en-us/adminsql/ad_1_server_4uuq.asp

sp_addlinkedserver

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm