Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cnyd
Starting Member
3 Posts |
 Posted - 2010-11-22 : 04:35:28
|
| Hi All,We have some accdb databases for compatibility. Our services already insert data to MSSQL. So I can solve the problem if I can insert data to accdb database using trigger.But I do not know how can I insert records to accdb database from a trigger.DECLARE @CustomerID intDECLARE @AccessFileName varchar(255)Select @CustomerID = CustomerID From insertedSet @AccessFileName = 'G:\Archive\' + Cast(@CustomerID as varchar) + '.accdb'-- Commands to insert new records on @AccessFileNameCould you please show me the way to do this.Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 13:52:51
|
It's an Access 2008 2007 database.Using a trigger is a bad idea here. If it fails for any reason, the whole batch will fail.Also likely to experience performance problems.A better idea is to schedule a job to periodically export the data to MS Access.Or, if it has to be closer to real time, perhaps service broker. Just not a trigger.EDIT: Typo |
 |
|
|
bobmcclellan
Starting Member
46 Posts |
Posted - 2010-11-22 : 23:43:46
|
| cynd,Is it an option to move your accdb table to the sql db and connect via odbc?or better yet... move all the tables up and use .adp as opposed to accdb. |
|
|
|
cnyd
Starting Member
3 Posts |
Posted - 2010-11-23 : 01:48:20
|
| Hi All,Sorry guys accdb is Access 2007 database. Other system is not configurable to run with ODBC it is using accdb databases. I agree with russell. This method (using trigger) has some defects (performance, data loss etc.).I need a quick solution to update the accdb databases until our services are updated.Thanks all |
|
|
|
|
|
|
Inserting record accdb database from a trigger