Cannot Delete and Update Records on Access Linked Table

cannot delete and update records on access linked table

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

Thanks everyone...

SQL Linked Table Can Insert Can't Delete Run-Time 3086

To delete or update in a linked ODBC table, the table needs to have a Primary Key.

The PK must be recognized by Access: open the linked table in design view, check there is a "lock" icon in the PK column(s).

Access linked table Connect property corrupted - How to fix (or delete)?

I've tried using code to update/restore the 'connection' property but that doesn't work.

I was able to recreate your issue by hacking an .accdb file and setting the .Connect property of a linked table to Null. Although ...

• I was unable to delete the link (I got "Invalid use of Null", same as you), and

• Compact and Repair Database did not fix the problem

... I was able to revive the link with the following VBA code:

Option Compare Database
Option Explicit

Sub ReviveBadLink()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim tbd As DAO.TableDef
    Set tbd = cdb.TableDefs("Clients")
    tbd.Connect = ";Database=C:\Users\Public\Database1.accdb"
    tbd.RefreshLink
End Sub

Once the link was revived I could then delete it as usual.

---

Related Topics