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 |
|
yoggiis
Starting Member
2 Posts |
 Posted - 2005-09-22 : 20:03:37
|
| Hi, I want to Import data from a SQL Server to a MS Access database by writing a DTS package which should be scheduled on a periodic basis. The SQL server is online and the MS Access is also in a remote system. I was able to do a transfer locally, but cant seem to choose the database for a remote system using the New package feature in DTS.I would have used merge replication but I dont have publishing or subscription rights for the SQL Server.Any help will be appreciated.Thanks Yogesh |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2005-09-23 : 02:59:40
|
quote:
Originally posted by yoggiisbut cant seem to choose the database for a remote system using the New package feature in DTS.
Connecting Access remotely from DTS is possible. Create a DSN and access it from DTS (EM->DTS->local packages->New connection ) OTHER CONNECTION.DTS and merge replication are two different subjects. And have different uses. Decide which one you want...------------------------I think, therefore I am - Rene Descartes |
 |
|
|
yoggiis
Starting Member
2 Posts |
Posted - 2005-09-23 : 12:44:07
|
| Thanks for the reply...Now heres the deal .. My client has a SQl server databse as a backend to its website but locally in their office they use ACcess.I want to have all the updated data on their SQL Server reflected on the Access database. Now, the SQL database is hosted on the web server of the hosting service and is a shared database. I thought of Merge replication as a solution but since the hosting service cannot give me publishing rights as its a shared db. Hence, I thought of Writing a DTS package is another solution...Will that be possible considering the fact that they are remote databses. Thanks Yogesh |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2005-09-24 : 07:21:56
|
| What you need is one way replication or log shipping. DTS can not solve the problem, unless you want to export the whole DB every time. How will you idenityfy the modified records? Or may be you can have a date column (for tables which needs to be exportred) which gets updated every time you fire a DML against the table. later you can use DTS to export the cummulative data. Also depends on how much you are exporting...------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
|
|
Want to Import Programatically