sql server (alternate): Connnecting to an MS Access DB with an MS SQL 2000 Stored Proc -- sql server (alternate)

Connnecting to an MS Access DB with an MS SQL 2000 Stored Proc

Joe Seamour
6/27/2003 2:45:17 PM

sql server (alternate):

I would like to access a table in MS Access from a stored procedure on MS
SQL 2000. The MS Access DB is running on a diferent computer attached on the
same network. I think I need to create an ODBC connection from the MS SQL
2000 to the MS Access DB but not sure. Secondly how do I refer to the table
in the MS SQL stored proc?

Thanks for your input,

Joe Seamour

Re: Connnecting to an MS Access DB with an MS SQL 2000 Stored Proc

Simon Hayes
6/28/2003 4:30:55 PM

You need to add a linked server, and then you can query the Access database
directly. The BOL entry for sp_addlinkedserver shows an example, and the
topic "OLE DB Provider for Jet" has more details.

I've never set this up to use an Access DB on a different server myself, but
I think it should work if the MSSQL service account has permission to access
the share where the .mdb file is.

When the linked server is configured, you can query it like this.

SELECT * FROM MyLinkedServer...Table

Simon

[quoted text, click to view]