sql server 2008 - Automatic Information Transfer Between Databases -

sql server 2008 - Automatic Information Transfer Between Databases -

i have application uses access database store records. application 3rd party application , cannot altered have ability open database , view records creates.

what know - there way tell access send new records table in sql database when record created in access. needs done without user intervention. in perfect world application talk straight sql, settle less perfect world if software continues talk access , access talks sql.

access 2010 did introduce event-driven data macros behave triggers. after insert info macro can create records in local access table, unfortunately

queries contain linked tables, action queries, , database references not allowed in info macros.

so cannot utilize after insert info macro in access "push" new records linked sql server table.

however, utilize scheduled job in sql server "pull" new records access database has been set linked server in sql server. example, had table named [inquiries] in access

id - autonumber (incrementing), primary key inquirydate - date/time contactname - text(255)

and corresponding [dbo].[inquiries] table in sql server

id - int, primary key inquirydate - datetime contactname - nvarchar(255)

we set access database linked server named accdbdata in sql server , have sql server run scheduled job every pull new records access database using t-sql statement this

class="lang-sql prettyprint-override">insert dbo.inquiries (id, inquirydate, contactname) select id, inquirydate, contactname accdbdata...inquiries id > (select isnull(max(id),0) dbo.inquiries)

sql-server-2008 ms-access-2010