CODEWITHSUNDEEP
sqlms-access
BobNoobGuy
BobNoobGuy

Reputation: 1645

cannot delete and update records on access linked table

I have access database called road.mdb. Inside road.mdb, I have a linked SQL table and
the table name is student.

I can insert records using query design in MSAccess

But I cannot update nor Delete

when run delete query below, the error is: could not delete from specified table

delete from student where studentid=303;

and when I run update query below, the error is: Operation must use an updateable query

update student set Name='BOB' where studentid= 303;

I have full access to the sql database and I can run the query OK using sql management studio. Is it impossible to delete and update using query design inside MSaccess?? The weird thing is I can insert new records using query design inside MSaccess

thank you

Upvotes: 7

Views: 37827

Answers (5)

Orlando Robles
Orlando Robles

Reputation: 1

In my case the problem was a BIT column. I think the problem occurs when the the bit column contains a NULL value.

To resolve the issue, I either deleted the entire column, or set a default value.

Upvotes: 0

Geoff Schultz
Geoff Schultz

Reputation: 151

In my case, the linked table only had keys. I had to modify one of the keys to be a primary key and then I could truncate truncate the table via a DELETE table.* FROM table via access.

Upvotes: 0

Michael Frelas
Michael Frelas

Reputation: 11

You will find that the following steps will most likely solve your problem:

  1. In SQL Server: set a primary key on the table you are working with and make sure the primary key is of type int, not bigint as Access will not properly deal with bigint data type.
  2. In SQL Server: refresh the table.
  3. In MS Access: re-link the table.

(You can easily check if 'things are OK' afterwards by adding a record to the SQL Server table and accessing it through the MS Access linked table. When all is OK you should not see #Deleted when viewing the data from MS Access side.) Hope it helps ;-)

Upvotes: 1

J.J
J.J

Reputation: 1001

In the case that you can't manipulated the table on SqlServer, you can get around the problem by telling Access which/s column/s are meant to be the primary key. This is done on the last step of creating a Linked table, the window title is "Select Unique Record Identifier".

Upvotes: 1

BobNoobGuy
BobNoobGuy

Reputation: 1645

I SOLVED this by adding primary key to the SQL table and re linked the table to ACCESS

Thanks everyone...

Upvotes: 10

Related Questions