Results 1 to 2 of 2

Thread: Access front end MSSQL Backend and a trigger inserting on another table.

  1. #1

    Thread Starter
    Fanatic Member Dnereb's Avatar
    Join Date
    Aug 2005
    Location
    Netherlands
    Posts
    863

    Access front end MSSQL Backend and a trigger inserting on another table.

    Hi All,

    I'm trying to solve an issue.

    The basic situation is like this:
    There's an Access front-end and it inserts a record on a MSSql Back-end database with a Integer as identity. But the table on the back-end needs to have a trigger to send the data to another intermediate database. (a Gateway database) a thrid party reads this database using en window service to update their database.

    The issue is this:
    The trigger inserts a record in the gateway database using a GUID as identity after the record is inserted in the back-end database. By Access
    Access retrieves the Identity of the last insert with @@identity and recives the |Guid from the other database. Instead of the integer off the first insert.

    Access should use Scope_identity() to retrieve the correct (integer)Id


    Now the question:
    Is there a way to make Access use Scope_identity instead of @@identity? or another workaround NOT using MAX(Id), as it's a multi user enviroment?
    why can't programmers keep and 31 Oct and 25 dec apart. Why Rating is Useful
    for every question you ask provide an answer on another thread.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Access front end MSSQL Backend and a trigger inserting on another table.

    And you have to live with the identity PK in access? You cannot switch to a GUID PK or add a GUID as an alternate KEY, so that you can produce a guid upfront?

    In an accounting system that I have we maintain the next batch and transaction numbers for a fiscal year in a control table and get the next ID using this logic in a SPROC. I just checked on of my client sites and one of these tables has 374,000 rows and never got a problem using logic like the below.

    Nice thing about this logic is no transaction/lock is required - it's multi-user SQL safe from the get go.

    Code:
    RedoB:
      Set @DataValue=(select DataValue from Control_T where FiscalYr=@FiscalYr and Item='LAST BATCH')
      Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
      Update Control_T Set DataValue=@NewDataValue,Tdate=@Tdate
        Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
      If @@RowCount<>1 Goto RedoB

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width