April 17, 2006 at 1:27 am
Hello all,
I have created an excel linked server which i am able access using select query. How ever when I try to update the same I am getting this error.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider indicates that the user did not have the permission to perform the operation.
[OLE/DB provider returned message: Cannot update. Database or object is read-only.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::SetData returned 0x80040e09: The provider indicates that the user did not have the permission to perform the operation.].
I am not able to understand where to set the permissions for update.
PL help me.
April 18, 2006 at 2:43 pm
It is likely that the SQL Server running your linked server is running under a security context that only has read access to the Excel file. If you have not specified a local user account or a domain account for the SQL service to use, it will be running under the Local System account. This account has very limited.
I have seen recommendations in Microsoft's Knowledge Base indicating that it is best to run SQL Server under a user account and not the local system account.
Here's a link to an article in the Knowledge Base on how to change the service account:
http://support.microsoft.com/kb/283811/en-us
Hope this helps.
April 19, 2006 at 7:41 am
In the April 2006, ADVISOR GUIDE to Microsoft Access, there is an article by technical editor Andy Baron titled, "Linked Excel Data No Longer Updatable." In this article he explains that Microsoft has removed the updatablility feature due to a patent infringement lawsuit. He says the only alternatives are to either move the front end from Access to Excel (Excel can link to Access), or rewrite the Access application to import the Excel data, modify it, and then export it back to Excel. The article also mentions a microsoft KnowledgeBase article: http://support.microsoft.com/?kbid=904953.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply