2 different databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

2 different databases

Author

Topic

Todd2006
Starting Member

18 Posts

Posted - 2009-02-11 : 10:06:37

Hello,

I have a table called Members in Access where each member has a Membernumber which is unique and also has a date when the member will expire.

I have another database which is in SQl 2005 which has a table called the Listing which has a listing for the member.

If the member expires in the Members table in access he is removed from there and I want him to be removed from the Listing database in SQL.

Can someone tell me how I can write a trigger or stored procedure or a sql job which will make this happen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 10:16:27

you need to add the access db as linked server in sql 2005 and then use like


DELETE t
FROM SErverName.DBName.dbo.Listing t
LEFT JOIN AccessServer.AccessDB..Member m
ON m.Membernumber=t.Membernumber
WHERE m.Membernumber IS NULL

Todd2006
Starting Member

18 Posts

Posted - 2009-02-11 : 10:30:57

Can you tell me how to add access db as a linked server to sql 2005 I have not done this before so can u please guide me.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 11:02:41

http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources