Run Sproc on SQL Server

[Scott]

I have a tradional (all access mdb) frontend/backend app that user types in
figures on a form. However, when the form is closed, I have the below DTS
job fire. It's job is to send/convert the backend mdb to our sql server. I
then have many asp web pages that reference the numbers on our data
intranet.

Here's the issue. I have chosen this method because I have a few laptop
users that like to copy the mdb files to their pcs. If I convert the
frontend to a sql project, they no longer while enjoy the freedom of
portability.

I'm trying to do something similiar to below, except I need to excute a
SPROC stored on a different sql server to grab a few figures and insert into
the backend mdb. I know how to open a recordset in vba, but do I need to
install a new Reference Library for sql?

Could someone provide a few lines of code showing how to open a SQL
connection and execute a sproc?


' below uses the Microsoft DTSPackage Object Library
Dim oPKG As DTS.Package, oStep As DTS.Step
Set oPKG = New DTS.Package

Dim sServer As String, sUsername As String, sPassword As String
Dim sPackageName As String, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String

' Set Parameter Values
sServer = "myserver"
sUsername = "sa"
sPassword = "mypassword"
sPackageName = "dailyupdate"

oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
DTSSQLStgFlag_Default, , , , sPackageName

For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

oPKG.Execute

For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize