Stored Proc - Access work ok? - 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

Development Tools

Other Development Tools

Stored Proc - Access work ok?

Author

Topic

o9z
Starting Member

23 Posts

Posted - 2007-05-10 : 11:01:43

I need to create a stored procedure for a vb.net app that does multiple things. Some of the data being processed is actually in MS Access. Does SQL Serer 2005 interact ok with Access? I am new to stored proc's, so I just want to find out if Access and SQL will play nice together as part of the same stored proc.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 11:16:57

You will need to attach MSAccess database as Linked Server and refer it inside your SP.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

o9z
Starting Member

23 Posts

Posted - 2007-05-10 : 11:22:30

How do I link the access table in SQL Server?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 11:32:48

You need to make use sp_addlinkedserver system stored proc.

Below example taken from Books online:

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources