Convert SQL Server Procedure To Access Query

  • Thread starter Thread starter rn5a
  • Start date Start date
about changing the table design from a current state table to a
transaction log table; that way, you always change the state via an
INSERT, therefore you can achieve the same in Access/Jet table + single
statement proc, using VIEWs (Query objects) to show the current state,
plus you get historical ('audit trail') data into the bargain. Sometime
when you have limitations you can try thinking outside the box
Click to expand...

Another perspective on this.

The logic in OP's original proc was (I think): if the row already exits
then update it using all parameter values else insert a new row using
all parameter values. Consider that SQL UPDATE is logically equivalent
to a DELETE followed by an INSERT (ever wondered why there is no
'updated' logical/conceptual table when working with triggers in SQL
server?) So perhaps the better logic is: DELETE the row then INSERT a
new row (both actions in a transaction, of course), no control-of-flow
logic required. Still not possible to do both DELETE and INSERT in a
Jet proc, though :(

Jamie.

--
 
Back
Top