Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

cannot delete and update records on access linked table

Tags:

sql

ms-access

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

like image 328
BobNoobGuy Avatar asked Oct 24 '13 00:10

BobNoobGuy


People also ask

Why can't I edit data in Access query?

Reasons why a Query or Recordset is not UpdateableThe field is a calculated field, so it can't be edited. You don't have permissions/rights to edit the table or database. The query uses VBA functions or user defined functions and the database isn't enabled (trusted) to allow code to run.

Can't delete from specified tables access query?

If you build a delete query by using multiple tables and the query's Unique Records property is set to No, Access displays the error message Could not delete from the specified tables when you run the query. To fix this problem, set the query's Unique Records property to Yes. Open the delete query in Design view.


3 Answers

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

Thanks everyone...

like image 183
BobNoobGuy Avatar answered Oct 20 '22 09:10

BobNoobGuy


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

like image 1
J.J Avatar answered Oct 20 '22 09:10

J.J


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

like image 1
Michael Frelas Avatar answered Oct 20 '22 08:10

Michael Frelas