By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,825 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,825 IT Pros & Developers. It's quick & easy.

How can I call a module from an event

P: 1
I created a module from a DTS package in SQL Server. Basically I want to run the DTS package by clicking a button on an Access form. The DTS package is simply populating a table in SQL Server from a query in the Access database.

From another person's answer, I have saved the DTS pkg as a visual basic file (.bas) and imported it into Access as a module.

I then created a form and placed a button on it and I want to run the Access module (basically running the DTS pkg) by calling the module but don't know how to do it. Can someone help?

The VBA code for the button is:

Private Sub Command1_Click()

End Sub

The module name is called: DocInfoExport

Thanks
May 21 '07 #1
Share this Question
Share on Google+
2 Replies

JConsulting
Expert 100+
P: 603
I created a module from a DTS package in SQL Server. Basically I want to run the DTS package by clicking a button on an Access form. The DTS package is simply populating a table in SQL Server from a query in the Access database.

From another person's answer, I have saved the DTS pkg as a visual basic file (.bas) and imported it into Access as a module.

I then created a form and placed a button on it and I want to run the Access module (basically running the DTS pkg) by calling the module but don't know how to do it. Can someone help?

The VBA code for the button is:

Private Sub Command1_Click()

End Sub

The module name is called: DocInfoExport

Thanks

inside the module there should be a function or sub name that kicks off the DTS. You will need to call it vs trying to call the module name. You call a function by name.
Private Sub Command1_Click()
myfunctionname
End Sub
May 22 '07 #2

ADezii
Expert 5K+
P: 8,750
I created a module from a DTS package in SQL Server. Basically I want to run the DTS package by clicking a button on an Access form. The DTS package is simply populating a table in SQL Server from a query in the Access database.

From another person's answer, I have saved the DTS pkg as a visual basic file (.bas) and imported it into Access as a module.

I then created a form and placed a button on it and I want to run the Access module (basically running the DTS pkg) by calling the module but don't know how to do it. Can someone help?

The VBA code for the button is:

Private Sub Command1_Click()

End Sub

The module name is called: DocInfoExport

Thanks
  1. You do not call a Module - you call Sub or Function Procedures contained within a Module.
  2. I dug up some sample code showing how to Execute a DTS Package from Visual Basic. I'm not sure what you would have to set your Library Reference to in this case, but if you're familiar with DTS Packages, I'm sure you will know.
  3. In the sample code below, you could simply execute the Sub Routine via Call RunPackage().
    Expand|Select|Wrap|Line Numbers
    1. Private WithEvents mobjPkgEvents As DTS.Package
    2. . . .
    3. Public Sub RunPackage()
    4. 'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
    5. Dim objPackage      As DTS.Package2
    6. Dim objStep         As DTS.Step
    7. Dim objTask         As DTS.Task
    8. Dim objExecPkg      As DTS.ExecutePackageTask
    9.  
    10. On Error GoTo PackageError
    11. Set objPackage = New DTS.Package
    12. Set mobjPkgEvents = objPackage
    13. objPackage.FailOnError = True
    14.  
    15. 'Create the step and task. Specify the package to be run, and link the step to the task.
    16. Set objStep = objPackage.Steps.New
    17. Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
    18. Set objExecPkg = objTask.CustomTask
    19. With objExecPkg
    20.     .PackagePassword = "user"
    21.     .FileName = "C:\DTS_UE\TestPkg\VarPubsFields.dts"
    22.     .Name = "ExecPkgTask"
    23. End With
    24. With objStep
    25.     .TaskName = objExecPkg.Name
    26.     .Name = "ExecPkgStep"
    27.     .ExecuteInMainThread = True
    28. End With
    29. objPackage.Steps.Add objStep
    30. objPackage.Tasks.Add objTask
    31.  
    32. 'Run the package and release references.
    33. objPackage.Execute
    34.  
    35. Set objExecPkg = Nothing
    36. Set objTask = Nothing
    37. Set objStep = Nothing
    38. Set mobjPkgEvents = Nothing
    39.  
    40. objPackage.UnInitialize
    41. End Sub
May 22 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.