Access and Triggers

Question

Access and Triggers

hrodberaht

SSC-Addicted

Points: 428
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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

cjensen SSC Veteran Points: 222 More actions · Answer 1

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 SSC-Addicted Points: 428 More actions · Answer 2

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 · Answer 3

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

hrodberaht SSC-Addicted Points: 428 More actions · Answer 4

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