Linking Access tables into SQL Server 2005

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

General SQL Server Forums

New to SQL Server Programming

Linking Access tables into SQL Server 2005

Author

Topic

homeguard
Starting Member

32 Posts

Posted - 2008-05-22 : 13:40:58

I am trying to add a linked table to my server, it is an access table.

Here is the code i am using but i get an error:

EXEC sp_addlinkedserver 
	@server = 'AITdb_be2', 
	@provider = 'Microsoft.Jet.OLEDB.4.0', 
	@srvproduct = 'Access',
	@datasrc = '\\kcapp03\depts\Common\EIQ\AIT DB\Test\AITdb_be.mdb'
GO

-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin 
	@rmtsrvname = 'AITdb_be2',
	@useself = 'false',
	@rmtuser = 'Admin', 
	@rmtpassword = ''
GO

-- List the tables on the linked server
EXEC sp_tables_ex 'AITdb_be2'
GO

ERROR:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\\kcapp03\depts\Common\EIQ\AIT DB\Test\AITdb_be.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-22 : 13:51:15

Does user have permission to access \\kcapp03\depts\Common\EIQ\AIT DB\Test\AITdb_be.mdb'?