Run DTS Package and Stored Procedure from Access

[jojones]

Here's the deal...

I have an entire system set up in Access at the moment and am slowly moving it over to SQL. Unfortunately I have just started this and need to keep the current system running until I can get the whole thing working.

So, until then... I have one task which I would like to run in SQL because the query in Access takes about 4 hours. What I thought I could do was set up a DTS package to import the data into SQL Server, run some SQL (which takes about 25 seconds in SQL Server) and then export the data from SQL Server back to Access. I need to do this from Access, as there are several steps before and after which run from Access.

Can anyone help me do this? Or does anyone have any better ideas? The access db does not live on the same box as SQL Server. I am using Access 97, but have ADO libraries available.

Hope to get some feedback soon as this manual intervention is really screwing up my sleep patterns

Thanks
Jo

 

[tb]

Have you set up the DTS already or is this only still an idea?

If not and you still want to . . setting up the DTS package is real easy ... Need help ?

"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[jojones]

Hi tb

I have already set up the DTS packages for both importing the data to SQL Server and exporting from. I have also written the Stored Procedures for running the SQL that needs to be run.

I just need to know how to kick them off from Access (VBA), and have Access wait til they finish to continue with the rest of the procedure.

I have tried executing a SQL SP from Access, where the SP calls dtsrun, but I think dtsrun requires that the Access db to import data from resides on the same drive as SQL Server... so it failed.

Any other suggestions?
ta
Jo

 

[SQLSister]

Why don't you stop using access tables altogether. Put the data in a SQL server table and link it in Access so it appears as an Access table to access (you will probably have to change the name after you link as Access adds the user as I recall to the table name and you want the original table name so all your existing stuff will work). Then you can run the stored procedure through vba instead of an access query, you aren't messing with copying tables back and forth and everything is all happy.

 

[tb]

Hi Jo

This is my code from VB - might have to adapt for VBA.

Referencing the following:
Microsoft DTSPackage Object Library - dtspkg.dll

Dim objPackage As DTS.Package2
Dim iCount As Integer

'Instanciate the DTS package object
Set objPackage = New DTS.Package2

'Set the server/username/password and the DTS package to be run
objPackage.LoadFromSQLServer "Server IP", "Login User", "Login Pwd", DTSSQLStgFlag_Default, , , , "DTS Package Name"

'Pass the date values to the DTS Package Global variables
objPackage.GlobalVariables("FromDate&quot.Value = dtpFrom.Value
objPackage.GlobalVariables("ToDate&quot.Value = dtpTo.Value

'Set object to raise an error on failure and to do eventlogs
objPackage.FailOnError = True
objPackage.WriteCompletionStatusToNTEventLog = True

'Run DTS Package
objPackage.Execute

Set objPackage = Nothing


Hope you find this useful

Have fun !



"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[perfectchaoss]

I am trying to do the same thing (run a DTS package from VBA) It looks like the code above will work and I also found some similar code on the Microsoft website. However I can not find the "Microsoft DTSPackage Object Library" to reference. I tried going to tools then References, but that one was not available to reference. Does anyone have any ideas how i can get that object type to work. I also searched my pc for the dtspkg.dll and it was not found. I am using Access 2K so it should be available for referencing right?

 

[perfectchaoss]

OK nevermind, I guess that the Microsoft DTSPackage Object Library is only availible on computers that have SQL server installed.

 

[tb]

perfectchaoss - yip

jojones - any luck ?



"All I ask is the chance to prove that money can't make me happy." - Spike Milligan

 

[jojones]

hey fellas - sorry for the delay

Thanks for all your suggestions and code. I decided that moving the tables over to SQL Server and linking them back to Access was the smartest move for now. Now it runs so quickly. Yah!

I am learning more about SQL everyday and it's brilliant. Can't understand why anyone would use Access anymore - SQL Server is so easy and so powerful. Can't wait til my app is finished.

cheers all
Jo
ps. yeah Spike - me too!