Cannot insert data into SQLServer table linked in MSACCESS db

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Cannot insert data into SQLServer table...

11 posts, Page 1 of 2

1 2 »»

Cannot insert data into SQLServer table linked in MSACCESS db

Rate Topic

Display Mode

Topic Options

Author

Message

dimsandwich

Posted Tuesday, June 03, 2008 7:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:14 AM
Points: 9, Visits: 39

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

Post #510627

GSquared

Posted Tuesday, June 03, 2008 11:21 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #510846

Greg Charles

Posted Tuesday, June 03, 2008 11:55 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 4,007, Visits: 4,854

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

Post #510867

pduplessis-723389

Posted Tuesday, June 03, 2008 12:01 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, November 03, 2010 2:32 AM
Points: 1,249, Visits: 400

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

Post #510870

dimsandwich

Posted Wednesday, June 04, 2008 4:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:14 AM
Points: 9, Visits: 39

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.

Post #511251

dva2007

Posted Wednesday, June 04, 2008 7:49 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:05 AM
Points: 530, Visits: 1,020

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

Post #511378

dimsandwich

Posted Thursday, June 05, 2008 3:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:14 AM
Points: 9, Visits: 39

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.

Post #511976

dimsandwich

Posted Thursday, June 05, 2008 3:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:14 AM
Points: 9, Visits: 39

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

Post #511978

dimsandwich

Posted Thursday, June 05, 2008 3:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:14 AM
Points: 9, Visits: 39

: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.

Post #511982

tfifield

Posted Thursday, June 05, 2008 12:01 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, April 05, 2013 8:10 PM
Points: 958, Visits: 2,873

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

Post #512378

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions