C# .NET - Updating SQl server from access database enteries(realtime)

Asked By s p on 06-Mar-12 12:19 AM
i have a access database which is updated from a window application . Now i want as soon as a entry is made in access database, access database should update sql server database with the newly added entry. How to achieve this
D Company replied to s p on 07-Mar-12 03:52 AM
If i'm not wrong access DB does not support triggers, so this option u cant try, one more thing that u can try is use access data marcos there is an event known as After* data macros it has AfterUpdate, AfterInsert, and AfterDelete events which  are useful when u update your access data.

for more detail browse the below link

http://blogs.office.com/b/microsoft-access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx


Regards
D
Pat Hartman replied to s p on 08-Mar-12 01:51 PM
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.