SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-01-2008, 11:34 AM
| ||||
| ||||
 Trigger on an Access database? 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      |
|
03-01-2008, 11:34 AM
| |||
| |||
| Re: Trigger on an Access database? 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 |
|
03-01-2008, 11:34 AM
| |||
| |||
| Re: Trigger on an Access database? 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 |
|
03-01-2008, 11:34 AM
| |||
| |||
| Re: Trigger on an Access database? 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 |
|
03-01-2008, 11:35 AM
| ||||
| ||||
| Re: Trigger on an Access database? 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. |
 |
« Select Query - can I use a Pivot table?
|
Transfer data from an excel sheet to table in sql data base »
| Thread Tools | |
| Display Modes | |
Linear Mode
|
|
All times are GMT. The time now is 08:04 AM.
