Moving data from SQL Server to Access  
Author Message
AJBopp





PostPosted: Tue Feb 07 10:46:44 CST 2006 Top

SQL Server >> Moving data from SQL Server to Access

Could anyone please offer advice on the following:

I have a table in a SQL Server database, that I would like to put a
trigger on. When an Insert is ran on the table, I would like to also
run an insert on an Access database. The Access database is not on the
same server, but is on a networked drive.

Could someone please indicate how I would connect to Access within a
trigger?

Thanks.

SQL Server295  
 
 
Ryan





PostPosted: Tue Feb 07 10:46:44 CST 2006 Top

SQL Server >> Moving data from SQL Server to Access How about using OPENDATASOURCE to talk to the Access table. You could create
a view in SQL like :-

( i used the BOL opendatasource example, it's an Excel not Access)

CREATE VIEW vwAccessTable AS
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions

Then within your trigger just reference the object vwAccessTable

You will need error handling in your trigger, see here :-
http://www.sommarskog.se/error-handling-II.html#triggers



--
HTH. Ryan




> Could anyone please offer advice on the following:
>
> I have a table in a SQL Server database, that I would like to put a
> trigger on. When an Insert is ran on the table, I would like to also
> run an insert on an Access database. The Access database is not on the
> same server, but is on a networked drive.
>
> Could someone please indicate how I would connect to Access within a
> trigger?
>
> Thanks.
>