Stored Procedure in Access to SQL Server 2005. Passing Parameters

[dashen]

Dim stStoredProcCall As String
stStoredProcCall = "sp_SaveDefJobInfo " & 1234567 & " " & 1 & " " & "'09/18/06'" 'this is just metadata, but needs to be passed
DoCmd.OpenStoredProcedure stStoredProcCall

I tried something like this, but it gave me an error.

How do I pass parameters with a stored procedure call in Access to SQL Server 2005?

 

[Crowley16]

declare and open an adodb connection object,

then use the execute method of the connection object

if you want to retrieve any output parameters then you need to use a adodb command object's parameters collection...

--------------------
Procrastinate Now!

 

[dashen]

Dim myConn As New ADODB.Connection
Dim myDSN As String
Dim myCmd As New ADODB.Command
Dim myRS As ADODB.Recordset

Set myConn = New ADODB.Connection

On Error GoTo ErrorHandler

myDSN = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;Initial Catalog=Employment Tracking;" & _
    "Data Source=CSC-S54"

With myConn
    .ConnectionString = myDSN
    .CommandTimeout = 20
    .CursorLocation = adUseClient
    .Open 'Something is wrong with opening a connection using ADODB
End With

Set myCmd = Nothing

Can you tell me what is wrong with this code then? Am I not referenceing something that needs to be referenced?

 

[JerryKlmns]

Dim myConn As ADODB.Connection
Dim myDSN As String
Dim myRS As ADODB.Recordset

Set myConn = New ADODB.Connection

On Error GoTo ErrorHandler
myDSN =""
myDSN = myDSN & "Provider=SQLOLEDB;"
myDSN = myDSN & "Data Source=CSC-S54;"
myDSN = myDSN & "Initial Catalog=Employment Tracking;"
myDSN = myDSN & "Integrated Security=SSPI;" "
myDSN = myDSN & "Persist Security Info=False;"

With myConn
.ConnectionString = myDSN
.CommandTimeout = 20
.CursorLocation = adUseClient
.Open 'Something is wrong with opening a connection using ADODB
End With
'If ADO 2.6 and above a quick way for 1 parameter
Set myRS = New ADODB.Recordset
myConn.YourStoredProcedureName youParameterValue, myRS
'For 3 parameters using the default value of 2nd
'myConn.YourStoredProcedureName Param1, , Param3, myRS

'If not ADO 2.6 and above check this faq222-2067

'When done
myRS.Close
Set myRS = Nothing
myConn.Close
Set myConn = Nothing

 

[dashen]

Sorry Jerry,

but that doesn't answer my problem

I am quite familiar with ADODB connections. I just didn't want to bother hard coding unless I had to.

The problem goes to the ErrorHandler section on the myConn.Open method, so I was wondering why this was the case.

I am running Access 2000/2003 ADP. I am trying to hit SQL Server 2005. I know these are slightly incompatable, any ideas?