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.
| Author |
Topic |
|
zaty2405
Yak Posting Veteran
58 Posts |
 Posted - 2010-10-15 : 09:00:45
|
| Hi,Here is the linked server script:EXEC master.dbo.sp_addlinkedserver @server = N'OFMACCESSDB', @srvproduct=N'OFMACCESSDB', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\\abc.prod.com\americas\E & P\SEPCO Woodcreek\PROJ_09\Magnolia Project\OFM\MagnoliaOFM2007.mdb' /* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OFMACCESSDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'admin',@rmtpassword='########'The problem is when perform 'test connection' :1) Using my account ( with Sysadmin Role and Windows authentication) , I encountered error:"The test connection to the linked server failed."------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "OFMACCESSDB".OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "OFMACCESSDB" returned message "The Microsoft Access database engine cannot open or write to the file '\\abc.prod.com\americas\E & P\SEPCO Woodcreek\PROJ_09\Magnolia Project\OFM\MagnoliaOFM2007.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.". (Microsoft SQL Server, Error: 7303)2) But Using my other account ( with Sysadmin Role and SQL authentication) , the 'Test Connection' was successful.Please helpThanksZaty |
|
|
|
|
|
Linked server to MS Access issue