I need to Update multiple tables in my database. I searched around and learned that you had to create a store proc and use BEGIN TRANSACTION and COMMIT to complete this.. However, I was unable to find a clear example for what I want..
I wrote something.... Its probably not fully correct because this would be my first stored proc I ever wrote in SQL...
First what I need to know is if this stored proc will work with my ASP page and MS Access?
Here is my stored proc...
CREATE PROC updateObjTypes
UPDATE RDEObjTypeMstr SET RDEObjType = @uObjType, RDEObjTypeCd = @uObjCd, DescText = @uDesc, Status = @uAStats, LastCngDTime = @uDate, LastCngUser = @uUser
WHERE RDEObjTypeId = @ObjID
UPDATE RDEObjActionTypeMstr Set RDEObjTypeCdAssoc = @uObjCd
UPDATE RDEAbendTypeMstr Set RDEObjTypeCd = @uObjCd
I have not tried this yet because I want to make sure syntactically I am on the write track before I work with my logic. So, if anyone could assist me on this I would appreciate it.
....er - double check your sources. That looks suspiciously like T-SQL to me. JET (the database engine behind Access) supports stored procedures but they are not widely used.
You might be better advised to do this using an ADO recordset. Also, are you sure you do not want a where clause for the last two update statements? You are updating every row in those two tables there....