How can I call a module from an event

[MacG]

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

[JConsulting]

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

[ADezii]

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().
   

   Private WithEvents mobjPkgEvents As DTS.Package
   . . .
   Public Sub RunPackage()
   'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
   Dim objPackage      As DTS.Package2
   Dim objStep         As DTS.Step
   Dim objTask         As DTS.Task
   Dim objExecPkg      As DTS.ExecutePackageTask
    
   On Error GoTo PackageError
   Set objPackage = New DTS.Package
   Set mobjPkgEvents = objPackage
   objPackage.FailOnError = True
    
   'Create the step and task. Specify the package to be run, and link the step to the task.
   Set objStep = objPackage.Steps.New
   Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
   Set objExecPkg = objTask.CustomTask
   With objExecPkg
       .PackagePassword = "user"
       .FileName = "C:\DTS_UE\TestPkg\VarPubsFields.dts"
       .Name = "ExecPkgTask"
   End With
   With objStep
       .TaskName = objExecPkg.Name
       .Name = "ExecPkgStep"
       .ExecuteInMainThread = True
   End With
   objPackage.Steps.Add objStep
   objPackage.Tasks.Add objTask
    
   'Run the package and release references.
   objPackage.Execute
    
   Set objExecPkg = Nothing
   Set objTask = Nothing
   Set objStep = Nothing
   Set mobjPkgEvents = Nothing
    
   objPackage.UnInitialize
   End Sub