Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    17

    Question Unanswered: Update Multiple Tables

    Hi everyone,

    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...
    Code:
    CREATE PROC updateObjTypes
    
    BEGIN TRANSACTION
    
    	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
    
    COMMIT
    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.

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ....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....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    More info on JET sprocs:
    http://msdn2.microsoft.com/en-us/lib...acadvsql_procs

    Also, JET sprocs can only execute a single DML statement. Which makes them next to pointless.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •