delete/add table in linkedserver

[yetanotherjo]

I've set up an Access database as a linked server. Is there a way to delete and add (ie "insert into") a table from this using a stored procedure in SQL2005 Express?

 

[yetanotherjo]

I also meant to ask...BOL is full of dire warnings that linked servers shouldn't be used anymore. What's the best simple alternative using Express?

 

[mrdenny]

Where do you see in BOL that linked servers shouldn't be used?

Linked servers are the only way to execute DML statements against a remote server.

Yes you can insert into and delete from a table via a linked server.

delete from AccessDB...TableName
where Column = 'Value'

insert into AccessDB...TableName
select Column1, Column3
from SQLTable
Where Column = 'Value'

In order to update and delete via a linked server the remote table must have a primary key defined.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yetanotherjo]

Ok, I can't find the references to them not being supported in future either now ... must've been pregnancy-brain and sleep deprivation induced.

I actually wanted to delete/insert the actual table, not just write records to it...In hindsight, I don't actually and I've gotten my original delete and insert procedures working - I was trying to make life more complicated than it needed to be.