Calling DTS package from VB/Ms-Access 2000

Hi All,
My name is kanishk. I am facing some issue in calling the DTS package from Visual Basic code.
The brief history of issue is :
We have a application which uses Ms-Access as front end and MS-Sql server 2000 as backend. Perviously we has MS-Access 97 version and MS-Sql 2000 .In this comination the DTS package was working fine.
Now We have changes the fornt end to MS-Access 2002. Here the DTS package is not working .

Can you please help me out to get the proper syntax to call the DTS package from VB code.What's your current syntax and what SP you have on the server?|||The DTS package is called from VB code. There is no SP at server side for DTS calling.

The Syntax in MS-Access(VB code )in version 97 is :

Sub RunDTS(strServer As String, strUser As String, strPassword As String, strDTS As String)
Dim dtsp As New DTS.Package

dtsp.LoadFromSQLServer _
ServerName:=strServer, _
ServerUserName:=strUser, _
ServerPassword:=strPassword, _
PackageName:=strDTS

dtsp.Execute
DoEvents

End Sub|||First make sure you have : Microsoft Dtspackage Object library activated in visual basic references.

Here is a visual basic code template :

Dim cn as ADODB.connection
Dim oPKG As DTS.Package
dim i as integer

SET cn = currentproject.connection

Set oPKG = New DTS.Package

if cn.Properties("Integrated Security") <> "SSPI" Then
'oPKG.LoadFromSQLServer cn.Properties("Data Source"), cn.Properties("USER ID"), cn.Properties("PASSWORD"), DTSSQLStgFlag_Default, , , , "DtsName"
Else
'oPKG.LoadFromSQLServer cn.Properties("Data Source"), ,, DTSSQLStgFlag_UseTrustedConnection, , , , "DtsName"
End If


On oPKG.FailOnError GoTo HandleErr

' -------- Facultatif , Use only in case you want to determine source file location at runtime

i = 1
For i = 1 To oPKG2.Connections.Count
FileSourceName = oPKG.Connections.Item(i).DataSource

oPKG.Connections.Item(i).DataSource = Me.FileLocation '( source file full name )

Next i
' -------

oPKG.Execute
oPKG.UnInitialize
Set oPKG = Nothing

ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & vbCrLf & vbLf & Err.Source & vbCrLf & vbLf & Err.Description, vbCritical
Resume ExitHere

Call SQL Stored Procedure

Greetings,

Even though this may be not right place with this issue I would like to try!
I facing with the problem Object Variable or With Block variable not set while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.

Many thanks in Advancehowzabout

Dim oConn, oComm, oRS
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
Set oRS = CreateObject("ADODB.RecordSet")

' Open connection to database
oConn.ConnectionString = Application("ConnectionString")
oconn.Open

' Use the stored proc
oComm.CommandType = 4 ' adStoredProc
oComm.ActiveConnection = oConn
oComm.CommandText = "spProcName"
oComm.Parameters.Refresh
oComm.Parameters("@.ParameterName") = sParameterName

oRS.CursorLocation = 3 ' adUseClient
oRS.Open oComm, , 3 ' adOpenStatic

Dunno if that will help with the access form or not. This is from a VBScript snippet that I use.

Regards,

hmscott

Calculating Median value from measures and dimensions

I am facing some problem in calculating Median

I am trying to calculate the median value using one of the measures and a dimension value.

Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:

opid time median

1 55

2 23

3 23

Total 23

The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.

What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.

I am using a calculated field Median with the following expression.

MEDIAN

( [Dim Operation].[Dim Operation].currentmember.children ,[Measures].[Elapsed Time])

Thanks

I think the reason you're getting a null for the first three rows is that the .Children function won't return any members if you're at the leaf level already. Instead, you'll want to use the Descendants function. Something like the following should do the trick:

Median(

Descendants([Dim Operation].[Dim Operation].CurrentMember, ,LEAVES)

,[Measures].[Elapsed Time]

)

Now if you're expecting to calculate a median value for opid 1 for more than simply the opid 1 value, then you'll need to add another dimension to the equation.