Can't update single table 1

[hsitz]

I seem to have a problem with being able to edit/update tables from an Access .adp or using odbc connection.

I'm using the trial version of SQL Server 2k at the moment.

I have about 20 tables in a SQL Server database. I can access and edit them all from Enterprise Manager. Most I can also edit from Access via ado or odbc. But there a few tables that I can't update, even though they have primary keys and even though all I'm trying to do is edit them in straight "table view" in Access.

I believe all of the tables that are uneditable in Access have creation dates of yesterday or greater. I've modified table definitions in previously existing tables and they still seem to work fine. It's just the new tables.

Seems like it would be some permission/security issue, but I can't for the life of me figure out what it is. I'm using WinNT security and the login I'm using is System Administrator and dbo of all tables, even the new ones. And like I said, I can edit all tables with this login in Enterprise Manager. Just can't figure out why I can't do it from Access.

Any ideas? Help!

Thanks,

Herb Sitz

 

[SQLSister]

Often in Access the problem lies in the table not having some kind of a unique identifuier, but you say these tables have a primary key, so that can't directly be the problem. But if you defined the key after you made the original link, maybe you just need to refresh the link.

When you make the connection from Access to SQL Server is it using your login or defining a specific login in the connection string, like a login for AccessUser. If the system is using an application login to connect, maybe that is where your permission problem is.

You could always compare the permissions for a table which works with the permissions for one that doesn't work to see if there is something different.

 

[hsitz]

Thanks SQL Sister. I actually did figure it out without your help, by taking a break for lunch and calming down a bit. But your suggestions get right at the problem.

I was aware that I needed an unique identifier to update when using ODBC, but wasn't sure whether that was the case with ADO (as it does seem to be). And I was also aware that the links have to be refreshed. But still I was having a hard time tracking down the problem, going back and forth changing index and refreshing links.

I think part of the problem must have been that I wasn't careful enough about refreshing links. Then I'd think it wasn't fixed and go back and change something else in the back end. And also part, perhaps, in thinking that closing and opening the Access database would refresh the links. (I think it did using ADO in the Access project, but that you need to do a manual update in an .mdb using odbc, but could be wrong.)

In any case, I ended up wasting a bit of time tracking this down even though I was aware of the two issues (need for primary key and need to refresh links). So it's something for other newbies to stay aware of.

Thanks again.

 

[SQLSister]

I am truly amazed at how often taking a break is the most effective problem solving solution. Glad you fixed your problem. And believe me I know about those two issues because I learned them the hard way!