Access and Triggers

• hrodberaht

  Old Hand

  Points: 348

  More actions

  February 16, 2004 at 5:52 am

  #59120

  Hi,

  My applicaton sends a t-sql INSERT statement to SQL Server. I have a trigger on the table specified in the previous statement, that returns the value of a 'auto increment' indentity field, and thus my application can identify what record has been inserted, and insert foreign keys in other tables, that reference the identity column in my primary table.

  Now I need to be able to connect to a Access database and do the same. To my knowlege, access, does not support triggers.

  Any ideas?

   

  
  Robert

• cjensen

  SSC Veteran

  Points: 222

  More actions

  February 16, 2004 at 9:24 am

  #494209

  As you probably have already determined, MSAccess only provides events for Forms and Controls.  If your AutoNumber (aka Identity) field is incrementing, not random, then after you insert the record, query for the max value of your identity field.

  select max(identity_field) from your_table

   

• hrodberaht

  Old Hand

  Points: 348

  More actions

  February 17, 2004 at 12:27 am

  #494309

  Thanks,

  Thats a good idea, but i have multiple workstations posting entries at undetermined times. I guess adding a workstation ID to my table will be a solution. Then I could include the workstation ID in the Where Clause. I havnt thougth of this before though.

  Thanks for your help.

  
  Robert

• WSquared

  SSCrazy

  Points: 2393

  More actions

  February 18, 2004 at 1:24 am

  #494474

  If you do the insert using a recordset, Access will supply the autonumber before it does the insert

• hrodberaht

  Old Hand

  Points: 348

  More actions

  February 18, 2004 at 1:38 am

  #494476

  I am using T-SQL, but this sounds interresting. If I understand correctly, you'll use a ado recordset and using the addnew/update functions, the autonumber value will be returned?

  I'll give it a try.

  Thanks.

  
  Robert