Can't edit linked SQL table data in Access

[ibwebn]

I'm using ODBC in Access 2000 to link to an SQL Server table. When I try to edit existing data in the SQL Server table I receive the following error message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

I am the only one using both the Access file and the SQL Server table. I have all the latest patches and updates to MS Access, and the latest MDAC. My SQL Server table also has a primary key field. The rights are setup correctly, because when I use an Access project I can edit the SQL Server table, but I'm not allowed to use a project. Any suggestion?

 

[cmmrfrds]

I am curious, why can't you use an Access project?

ODBC is an extra layer and has additional restrictions/problems. The only reason to link SQL Server tables to an mdb is if you need to join local Access tables and even that can be overcome.

The ODBC layer (there are multiple providers/vendors) may require an index in addition to the primary key on the SQL Server table. Do you have an unique index on the table in question.

Another possibility is that you have multiple connections open to the table from the mdb. For example, a Form open on the table and then trying to do an update in the Table or Query views.

You may need to provide additional information to diagnose the problem. What is Open, the query in the Form, etc....

 

[thornmastr]

If you are using Enterprise manager to update your table, there is absolutely no reason why you would ever get this message. It sounds like you are getting the problem from a form. If so, you have another form,usually a continuous form, which has this record locked. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[ibwebn]

There are no forms in the Access database file. The user will only be using Tables view. The user needs to make quieries to the tables. The queries are simple. They only query one table.

I would use and Access Project, but I can't get it to make a query. I try using the Views section to create a query, but it won't let me add a table. If I can get the Access Project to write a query, then I will use it.

The problem is occuring when I edit a tables data with Tables view in MS Access. The user does not use SQL Server's Enterprise Manager, just Access.

I think I got all the questions. Let me know if you need more information. Thanks.

 

[cmmrfrds]

You are right, the Access Project does not have queries like an Access mdb. It has Views which reside on SQL Server and are SQL Server Views. A View in SQL Server can hide some of the complexity of joining tables, add security, restrict view to certain fields, etc...

If you cannot update from a table view using ODBC, it is either no primary key, or no unique index, or no update permissions on the table.

IF the user needs to write their own queries then it is easier to stay with linked tables.

 

[ibwebn]

Unfortunately SQL Server Views are out. The user needs to use Access.

One of the things that is confusing me is I do have a primary key, I do have a unique index, and I do have update permissions on the table. For some reason it still does'nt work.

 

[cheerio]

We previously had similar problems with Access 97 that were cured by applying a service pack. Basically the Access record locking is faulty. Because, Access 2000 was probably in production before the Access 97 fault was identified and cured it is likely that the problem carried through.

I am currently using A2000 with SR1 and SQL7 and do not get the problem.

Ken

 

[ibwebn]

I figured out my problem with linking the table. I had two columns that were using Bit variables in SQL Server. For some reason Access did not like these Bit variables. Once I removed the columns with Bit variables everything worked fine. I was able to use Access to link to a SQL Server table, and edit the data in the table. Luckily I did not realy need the two Bit variable columns I deleted. Thanks for your help.