MS-Access: TableAdapter UpdateCommand for table without primary key

Question

What's the syntax for an Update query for a table without a primary key?

Disclaimer: Frustratingly, adding a primary key is not an option. My program is a small program in a much larger system with poor data management. My development time does not include rewriting the other software.

Note: The database is Microsoft Access.

Note: Similar to: Excel: TableAdapter UpdateCommand for table without primary key

UPDATE: Am I correct in saying, "If the table in the database has no explicit primary key, then there can be no valid TableAdapter UpdateCommand?"

Answer 1

It's no different than if you have a primary key. However, you will have to set some sort of where clause that will allow you to uniquely identify a row.

Answer 2

Which DBMS are you using ? On Oracle (and probably others, but I have more experience with Oracle), there is a ROWID pseudo-column that you can use as a unique row identifier, even when there's no primary key

Answer 3

If there isn't an explicit primary key, there should at least be an implicit primary key (even if it's every column). Without any sort of key, you won't be able to safely update the table.

If you go through the wizard when creating the dataset, you should get an update query that includes an update statement similar to this:

update TableA
set Column1 = @Column1, Column2 = @Column2 ...
where Column1 = @PreviousColumn1 and Column2 = @PreviousColumn2 ...

EDIT
You won't be able to use the wizard for update or delete commands without a PK on the table. You can, however, make a copy of the Access file put a PK on the table (if you can't derive a short implicit key, you may have to use every column) and use that to create the commands via the wizard.

If you don't want to go through that step, then you'll have to create a query similar to the one above. The @PreviousColumnX parameters would have their SourceVersion values set to Original.

update TableA
set Column1 = @Column1, Column2 = @Column2 ...
where (Column1 = @PreviousColumn1 or @PreviousColumn1 is null)
    and Column2 = @PreviousColumn2 ...