Updating multiple tables

[Kalisto]

In my Access DB I am trying to write a single stored procedure (if possible) that will act upon multiple tables.

This procedure will be called via an external program (c#.net)

At present, I need to agg a new record to one table, query this table back to get the primary key of this new record, then do a second insert into a relational table to update the relational link. To my mind, there must be an easier way than sending 3 queries. If tehre was a conneciton loss / power cut etc between queries, then the data would be adrift and no use to anyone.

Any ideas appreciated
K

 

[fredericofonseca]

Access does not have Stored procedures as such.

You will need to have as many queries as required to do what you intend, and on your .Net code you start a transaction before your first SQL, and at the end you commit (or rollback) your changes.

e.g.
(with ado, vb style)

dim cnn as adodb.connection

cnn.open ...
cnn.begintransaction
cnn.execute "my_sql"
...
...
...
cnn.CommitTrans
cnn.close

Note that if you use linked tables on Access THESE ARE NOT part of the transaction, and can not be rolledback if you do any changes to them.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Kalisto]

OK, thanks.

I'll give that a try

K