May 6, 2002 at 6:49 am
Hi
Please help me out in coming up with the right design, we have a third party software that is in Access form collecting data into Access tables, now we have no support from the company which built it. With the size of DB growing, it has become awfully slow.
I want to either replicate the tables into SQL server DB or set up a linked server and create insert trigger on each one of the Access table to insert into sql table. Then I can set up a nightly batch job to delete records from Access tables; as a short term fix. Eventually we will use ADO to interact with SQL tables directly. My question is which one is better is transactional replication or is it triggers via linked server, please help me decide.
Thanks
Raghu
Raghu
May 6, 2002 at 7:50 am
I think to use a trigger in Access you have to be in project mode (.adp), which means you'd be using MSDE as the storage engine. If thats the case should be fairly easy to move that data into SQL and everything should work fine. If you don't have triggers then I think replication is really your only choice.
Overall I think replication makes more sense anyway, saves a lot of work writing code to do what it does for you. Only other option would be to truncate the SQL tables once a day and reload from the Access mdb - essentially snapshot replication.
Andy
May 6, 2002 at 7:55 am
Thanks Andy I will take the replication route and see where it takes.
Thanks again
Raghu
Raghu
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply