Can't edit data

[goofaholix]

I'm not an MS Access developer but I need to troubleshoot some connection issues with a database.

This is an MS Access database with data in linked tables that are in an SQL Server back end.

Currently users can read data into the forms but can't edit data in the form, if they overtype a value nothing changes, even though they have dbo access to the database on sql server.

MS Access connects to the database through odbc connections that are created with a form reading server, uid, and password info from a table in the ms access database.

The linked table is created by a form with VBA code that does all this (I can post the vba code if necessary).

I haven't come across this approach before but i've tried tinkering with the data in the odbc table trying various users and server names or ip addresses etc.

I can't see any evidence the db or forms etc are locked or read only.

Any ideas why users can't edit data?

Thanks.

Bruce

http://homepages.woosh.co.nz/bf/

 

[boblarson]

1. The SQL Server tables must have primary keys.

2. What version of SQL Server are you using and what driver are you using in your connection string?

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[goofaholix]

I forgot to mention that we are in the process of migrating our sql server tables from 2005 to 2008.

The tables on both are exactly the same, the security on both is exactly the same as far as I can tell.

If I create linked tables pointing to 2005 they work ok, if i create them pointing to 2008 they don't.

I'm wondering if there is a server side setting?

http://homepages.woosh.co.nz/bf/

 

[dhookom]

Bob mentioned "The SQL Server tables must have primary keys" which you have not addressed in your reply. You stated the tables are exactly the same but didn't confirm whether the tables have primary keys and if Access correctly identifies the keys.

Duane
Hook'D on Access
MS Access MVP

 

[goofaholix]

The tables don't have primary keys, however if that were the issue then this wouldn't work on the 2005 server as well as the 2008 server I'd think.

To test I've created a linked table to another table that does have a primary key in the same database on 2008.

This one has the same problem.

Bruce

http://homepages.woosh.co.nz/bf/

 

[boblarson]

What ODBC driver are you using for SQL Server 2008? You SHOULD be using the SQL Native Client 10 driver and NOT the normal SQL Server driver. Have you done that?

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[goofaholix]

Correction. I found when I exited the database and went back I could then edit the data in the linked table with the primary key I just added. I've added a primary key to the actual table I was using and all seems well.

Thanks.

http://homepages.woosh.co.nz/bf/