Why not modify the Access application to link to SQL Server rather than Jet? If the application was professionally developed, the conversion could take from 5 minutes to an hour depending on whether any DAO or DAO code was used. The changes are pretty simple. Usually it involves changing something like - Set rsData = qdData.OpenRecordset(dbOpenDynaset) to Set rsData = qdData.OpenRecordset(dbOpenDynaset, dbSeeChanges).
Newer versions of SQL Server require the dbSeeChanges argument whenever the underlying table(s) contain identity columns.
If the application was not professionally developed, the changes would be more extensive because at a minimum, you'd need to modify all the forms to use queries with criteria as their RecordSources. The last thing you want in a SQL Server environment is an open Access form, sucking all the rows of a table over the network.
The alternative that was suggested was to convert the Access app to A2010 which does support Triggers but INMNSHO, that's just a band aid. The best solution is converting the Access app to link to SQL Server directly. It will probably take less work over all and give you a single data source which is a much cleaner situation.