cannot delete and update records on access linked table

Question

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

Relucent

I do something similar with Access and linked tables, but with a redbrick database. I run select queries and then edit the results. – Neomaneomah

According to Google "Try using a PassThrough query: it's a query in SQL/Server syntax which Access will hand to the SQL engine uninspected. See PassThrough in the help for details." – Takeshi

Open the table through your Access database, create a new test entry with bogus data, and then close the table. Re-open the table, select the test record and hit the DELETE button on your keyboard. Let me know if the record deletes when you do that, or if it gives you a specific error message. – Respect

I cannot use the access UI. the insert has to be done using query design. Also the delete button is greyed out. Looks like SQL pass through is what I have to use. unless there is another way to configure the link table to allow update and delete without it – Relucent

Answer 1

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

Thanks everyone...

Relucent

That worked! Hint for re-linking the table -> right click on the linked table and select "Linked Table Manager", select the table you want to re-link, and hit OK. Thank you so much for figuring this out. I would have chased my tail on this one for quite a while. – Politi

How to re-link table? What I can do are only delete the table and recreate that link table. Using "Linked Table Manager" cannot let me set primary keys. – Nightwear

Answer 2

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

Variole

Answer 3

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 ;-)

Felonry

Answer 4

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.

Caviness

Answer 5

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.

Budget

Related: this answer to bind checkbox with a bit column in a SQL Server linked table. – Bibeau