| Author |
Topic |
|
cdieterle
Starting Member
4 Posts |
 Posted - 2007-11-21 : 08:56:31
|
| I have created a linked in SQL Server to an MS Access database. I can do select queries without any problem to any of the tables in the Access database through through the linked server but when I try to do an update query, I get the following error:OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "securedjetls" returned message "Operation must use an updateable query.".Msg 7343, Level 16, State 4, Line 1The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "securedjetls" could not UPDATE table "[securedjetls]...[customers]". I have done this successfully on other SQL Server installations, but on some I run into this problem and can't understand why. Can anyone tell me what I'm doing wrong. Thanks in advance. |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-21 : 10:45:14
|
| Does the underlying table have a primary key? Did you try to run the UPDATE in t-sql syntax from sql server or an application? It sounds like it is the jet engine checking the syntax, instead of sql srever. Does your update statement runs in acess? |
 |
|
|
cdieterle
Starting Member
4 Posts |
Posted - 2007-11-21 : 12:06:55
|
| Yes ther underlying table does have a primary key. I am trying to run the updte in t-sql. The crazy thing is, I have a copy of same access database on two different computers, both with SQL server and a linked server to the access database. On one of them, I can update, insert, delete anything I want throught the linked sever, but on other computers, which seem to be configured exactly the same, I can't update any tables in the access database. I can do select statements fine, I just can't do any updates. I'm working with 4 computers, all with SQL Server 2005 express and all with same version of Access. One works just fine and the other three do not work. I can update the tables across the Lan as well as over a WAN VPN connection to the one computer that works. This is driving me nuts!!! |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-21 : 16:39:20
|
| As we all know, two computers could be different in many ways.Try use passthrough query. Make sure it complys with jet first.Access bundles front-end and back-end together. Normally you could just initiate the update from the front-end without even to border sql server.If the update has to be fired off from sql server in t-sql, in the long run, you'd be better of to convert data into sql servers. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-21 : 17:54:27
|
In Access, when you link to a table on a server you must identify the primary key as such in when you link the table. Otherwise Access won't recognize the primary key, and will return that error. I have run into this a fair amount with my users who fail to click the primary key columns when they link the table.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
cdieterle
Starting Member
4 Posts |
Posted - 2007-11-26 : 10:21:36
|
| I'm not trying to update a SQL table from within Access, I'm trying to update an Access table that is attached to SQL server as a "Linked Server" through TransactSQL. It works just fine on a couple of my computers, but on others it does not. The Access tables that I'm trying to update do have a primary key. I am running the update query from t-SQL but I've tried the simplest of queries that there would be no doubt that they would run under access. The SQL server databases are set up the same the Access databases are identical. I'm thinking that there might be a difference in some .dll that is used for this functionality. |
|
|
|
cdieterle
Starting Member
4 Posts |
Posted - 2007-11-27 : 08:05:40
|
| I found the problem. It was a permissions issue on the directory where the Access file was located. Changed the permissions and all works well now. Something as simple as that! |
|
|
|
admill
Starting Member
2 Posts |
Posted - 2008-02-28 : 16:06:41
|
Can you please tell me how you resolved this issue? I am having the same problem? Did you change the permissions under My Documents by turning off the Read-Only or ???? Thank you.quote:
Originally posted by cdieterle
I found the problem. It was a permissions issue on the directory where the Access file was located. Changed the permissions and all works well now. Something as simple as that!
|
|
|
|
|
Operation must be an updateable query