Trigger on an Access database?

[Jim Devenish]

We have an Access database with Access front-end to Access back-end.
Another company has installed a separate application using SQLServer.
At present certain information in the first application is entered
manually into the second. We wish to automate this process.

The other company is proposing that the SQLServer application sets up a
trigger on a selected table in the Access backend. When a new record
is added, information can be transferred to the SQLServer.

I am interested to know if this is really possible. I have found in
BOL that the Access database can be set up as a linked server to
SQLServer. Is this way to handle this and is it then possible to set
up a trigger?

I am not the person who will be doing this but would like to know if it
possible and, in general, how it is done.

Jim

[Rick Brandt]

Jim Devenish wrote:

We have an Access database with Access front-end to Access back-end.
Another company has installed a separate application using SQLServer.
At present certain information in the first application is entered
manually into the second. We wish to automate this process.

The other company is proposing that the SQLServer application sets up
a trigger on a selected table in the Access backend. When a new
record is added, information can be transferred to the SQLServer.

I am interested to know if this is really possible. I have found in
BOL that the Access database can be set up as a linked server to
SQLServer. Is this way to handle this and is it then possible to set
up a trigger?

I am not the person who will be doing this but would like to know if
it possible and, in general, how it is done.

Jim

When using Jet tables (tables in an MDB file) there is no support for triggers.
You can of course use Access as the front end use a server back end and choose
one that does support triggers.

If you didn't misunderstand what the other company said then they are not very
familiar with Access.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Jim Devenish]

Rick Brandt wrote:

Jim Devenish wrote:

We have an Access database with Access front-end to Access back-end.
Another company has installed a separate application using SQLServer.
At present certain information in the first application is entered
manually into the second. We wish to automate this process.

The other company is proposing that the SQLServer application sets up
a trigger on a selected table in the Access backend. When a new
record is added, information can be transferred to the SQLServer.

I am interested to know if this is really possible. I have found in
BOL that the Access database can be set up as a linked server to
SQLServer. Is this way to handle this and is it then possible to set
up a trigger?

I am not the person who will be doing this but would like to know if
it possible and, in general, how it is done.

Jim

When using Jet tables (tables in an MDB file) there is no support for triggers.
You can of course use Access as the front end use a server back end and choose
one that does support triggers.

If you didn't misunderstand what the other company said then they are not very
familiar with Access.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks for that. I suggested to them that they provide a simple means,
such as a View or a Stored Procedure, that I could use in the front-end
to update the SQL tables when the information changed in the Access
back-end. They said that 'their technical people' would prefer to set
up a trigger on the Access back-end and so monitor any changes to a
particular table.

I was not so sure, although my knowledge of SQL Server is limited. You
appear to confirm my doubts.

By the way, you may have realised that I wrote and maintain the Access
system!

Jim

[Rick Brandt]

Jim Devenish wrote:

>
Thanks for that. I suggested to them that they provide a simple
means, such as a View or a Stored Procedure, that I could use in the
front-end to update the SQL tables when the information changed in
the Access back-end. They said that 'their technical people' would
prefer to set up a trigger on the Access back-end and so monitor any
changes to a particular table.

I was not so sure, although my knowledge of SQL Server is limited.
You appear to confirm my doubts.

By the way, you may have realised that I wrote and maintain the Access
system!

If all updates and insertions are done in your Access app via forms then you can
link to the SQL Server tables and then use code in your forms that push
identical updates to the SQL Server link.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Ed Murphy]

Rick Brandt wrote:

Jim Devenish wrote:

If all updates and insertions are done in your Access app via forms then you can
link to the SQL Server tables and then use code in your forms that push
identical updates to the SQL Server link.

But this risks making a mistake in the code. Before going down this
road, you should definitely evaluate migrating the back end from Jet
to SQL Server, thus allowing triggers.

Failing that, if near-real-time is acceptable, then create a stored
procedure that updates SQL to match Jet, and an Agent job to run that
procedure at some appropriate frequency.