Run stored procedure

[IcesMaster]

With access XP as front end and MSSQL SERVER 2000 As Back End:
It not satisfying me using "DoCmd.OpenStoredProcedure" when Executing non-select SQL, and I still questioning the effectiveness of using ADO in VBA.
Is somebody know the best way?
Thank you

 

[jrbarnett]

Hi,

Create a new query, write the SQL to run it, set its type to Pass Through.

This will be passed by Access through to the SQL Server engine and the results returned to access when it is run.

John

 

[IcesMaster]

Sorry. T can't find how to set passrthough in Access project.
I need the way to pass parameter in Access project

 

[pweegar]

Try opening the query wizard. In Access2000, you set a pass thru by selecting it under the Query heading. Should be aboutt he same with Access XP.

 

[IcesMaster]

'This is what I did to Run stored procedure "TEST"
'ALTER PROCEDURE "Test" (@YourName nvarchar(20), @YourPass nvarchar(10))
'INSERT INTO dbo.UserList (Name, Password)
'SELECT @YourName As Name, @YourPass As Password
'----------------------------------------------------------

Private Sub mycmd_RunSP_TEST()

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
'--
Set gcn = Nothing
'Dim gcn As Connection
Dim sConnect As String
sConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=i-GLSQL;DATA SOURCE=LEGENDA"
Set gcn = New ADODB.Connection
gcn.CursorLocation = adUseClient
gcn.Open sConnect
'---

With cmd
.CommandText = "TEST"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Period", adVarChar, adParamInput, 20, "KUNCUNG&quot
.Parameters.Append .CreateParameter("@UNIT1", adVarChar, adParamInput, 10, "balung&quot
Set .ActiveConnection = gcn
End With

cmd.Execute

End Sub