Run a SQL Server DTS from Access (2000)

[horacio]

How can I start a SQL Server DTS from Access? I normally run a DTS to download some tables by ODBC to the SQL Server. This tables are for an ACCESS program. I don't want to go each time I need to download tha tables go to SQL Server. I want to start the DTS from Access. Is this posible?

[WendellB]

Sorry I missed your question - I'm afraid the short/easy answer is no. The DTS utility in SQL Server is run as a job under the tasking system, and can't be invoked with normal procedures as you might with Automation using other Office Apps. I'm curious what you mean by "run a DTS to download some tables by ODBC to the SQL Server?" Generally if you are moving data from Access to SQL Server it is refered to as uploading or upsizing. Or are you simply replacing tables that you have used for test purposes? And how are you using the table in Access - via ODBC, via an ADP or with ADO and OLE DB.

If you are copying tables within SQL Server, it should be theoretically possible to create a pass-through query that calls a stored procedure which in turn kicks off the DTS task. However there are a number of issues associated with security, both in Access and in SQL Server that can cause the process to fail. I should add that I've never attempted to do it in that fashion. Perhaps if you give us a bit more detail on what you are trying to accomplish, we can propose an alternative strategy.

[WendellB]

After running across something in Alison Balter's Mastering Access 2002 Enterprise Development, I need to revise what I posted yesterday. It turns out the DTS is a COM object and can be manipulated from VB, C# and the .NET components, so it may well be possible to use Automation to make it do it's thing. However it appears it would need lots of research and code to make that happen - you might start withProgramming DTS services on the MSDN site.

On the other hand, if you are really downloading tables (or uploading tables) between Access and SQL Server, it's probably simpler to program the import and export functions from Access using Jet capabilities. Those are well documented, and have been thoroughly tested over the past 9 years or so - at least that's how long we've been using them.