CODEWITHSUNDEEP
sql-servervbams-accessexecute
missscripty
missscripty

Reputation: 537

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

I use Access VBA to add and remove records in a single SQL table, which contains 2 columns of varchar codes. I am a dbowner on the SQL Server database. My code to add values to this table works:

For Each varItm In Me.lstMyAllTypes.ItemsSelected
            AddSQL = "INSERT INTO xyzMyTable ([MyCode1],[MyCode2]) " & _
                     "VALUES ('" & Me.lstMyAllTypes.Column(0, varItm) & "','" & Me.cboType.Column(1) & "');"
            'EXECUTE SQL
            CurrentDb.Execute AddSQL, dbSeeChanges
            'Debug.Print AddSQL
Next varItm

My delete code throws a run-time error 3086:

For Each varItm In Me.MylstTypes.ItemsSelected
    RemSQL = "DELETE FROM xyzMyTable " & _
              "WHERE xyzMyTable.MyCode1 = '" & Me.MylstTypes.Column(0, varItm) & _
              "' AND xyzMyTable.MyCode2 = '" & Me.cboType.Column(1) & "'"
            'EXECUTE SQL
            DoCmd.SetWarnings False
            CurrentDb.Execute RemSQL, dbSeeChanges
            DoCmd.SetWarnings True
Next varItm

I debug the delete SQL string and I can run it in SSMS, but not in Access. If I create the same delete query in an Access query, it does not work. If I open the table, and try to delete the row, it makes a ding noise, but there is no pop-up error. How can I add but not delete?

This table has no relationships in SQL Server to any other tables, so referential integrity would not be an issue.

Upvotes: 0

Views: 337

Answers (1)

Andre
Andre

Reputation: 27634

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

Upvotes: 1

Related Questions