Cannot insert data into SQLServer table linked in MSACCESS db

Question

Cannot insert data into SQLServer table linked in MSACCESS db

dimsandwich

Mr or Mrs. 500

Points: 543
More actions
June 3, 2008 at 7:14 am

#188501

Dear all, I am trying to configure an MSACCESS db to link a table from SQL server and enable the user to insert records via msaccess. I have linked the table but all the records show #Deleted in every cell of every row.
I know I must have some kind of index or security issue but I cannot figure it out and have spent 6 hours trying.
The SQLServer table has a an index field called ID that has a unique, non-clustered index on it. There is no primary key on the table.
I am linking it to MSACCESS 2002 db via ODBC using an ID (Let's call it MYMAN) which is defined
as a sysadmin login in the server roles (I recognise this is likely a very bad idea but this is where I have arrived through my experiments)
MYMAN is in the database user list as being a member of a number of roles, db_datareader, DataEditor,APAC_OWNER,EMEA_OWNER,NA_OWNER.
DataEditor,APAC_OWNER,EMEA_OWNER,NA_OWNER are user defined roles in the database that Secure other tables and don't refer to the one I'm dealing with (I think).
The table itself (let's call it tblIssues) has no specific permissions set.
Can anyone offer any help in trying to figure out what I need to change in order that the Access db can open the table and insert/delete/update any rows/columns.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I've seen this before, but it's been a few years. It may have to do with the missing primary key.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

I've seen this too. Sometimes it is caused by Access not knowing what the primary key of the table is. You should designate the primary key when linking to the table.

It also can happen when Access doesn't understand a SQL Server datatype.

Greg

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 3

From a very far and distant memory of Access and SQL I have also seen #deleted sometimes.

Uhmmm, how about trying to refresh your tables in your linked table manager?

Not sure it works but worth a try

~PD

dimsandwich Mr or Mrs. 500 Points: 543 More actions · Answer 4

Thanks folks, I will experiment with the datatypes for sure. Also I think I've tried almost every permutation of indexing I can think of. What's not clear to me is whether it makes a difference which database has the index set, or whether both should have. I'm seeing odd and inconsistent results in my experiments with indexing. For instance, if I set a unique index on the SQL server table (there's no primary key) and I link it in access, I don't get offered the option to select and index in the linking wizard. If there's no index on the SQL server table, then I am presented with the option to choose an indexed field in access. Either way I'm still unable to update records in the way I want. I'll revisit that in a more structured way soon.

dva2007 SSCertifiable Points: 7707 More actions · Answer 5

Hi there,

i have got the same problem and i found solution as primary key on internet. But it is something more required to solve it.

Please insert one column in your sql table called timestamp and datatype should be timestamp as well. Make sure datatype is timestamp then refresh your link in the access with the table.

Try it again and i m sure it will solve ur problem. i spend many hours to solve this and finally timestamp did my work.

obviously, you can find more of timestamp in google.

thanks,

vijay

dimsandwich Mr or Mrs. 500 Points: 543 More actions · Answer 6

Thanks for all your help but I can't solve the problem.

I have a Primary key on the ID field, and I have created the timestamp column with a type of timestamp.

I've deleted and recreated the table. Then inserted the rows after adding the new timestamp column

I have also tried with no primary key and just a unique index on the ID field.

Also tried with no index at all.

The timestamp field shows as when I open the table in SQL Server Mgmt Studio. Is that normal?

I am linking the table in MSAccess 2002 via file DSN ODBC using an ID that is in the SYSADMIN group on the server.

I will experiment some more with the field datatypes and any more ideas greatfully received.

dimsandwich Mr or Mrs. 500 Points: 543 More actions · Answer 7

btw, I have other tables linked to the same msaccess db using the same ID and I see all the data.

dimsandwich Mr or Mrs. 500 Points: 543 More actions · Answer 8

:D, very happy now. It seems to have been the fact that I used Nvarchar as a datatype for some of the fields. I changed those to varchar and all is behaving as expected.

Thanks to you all.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 9

I had a similar problem. Access doesn't like nvarchar or ntext in a table that also has a REAL data type on the SQL Server side. I changed the REAL to another numeric type and the problem went away.

Todd Fifield

mayuri312 Grasshopper Points: 15 More actions · Answer 10

Thank you so much for your solution..Even I was facing same issue from almost an hour but now it is fixed.. I am so Happy Thanks Again!!!