calling stored procedure from access but will not open query

any error(s)

note that so far, you only execute the procedure, but do nothing with the data (if any)

I see this line:
cmd.ActiveConnection = cnn

in vba, it should be:
set cmd.ActiveConnection = cnn

otherwise you are only passing the connection string, and not the open connection ...

Correct i want to view a stored procedure

Try to execute it directly in SQL server and see if it returns anything. If yes then change your code to actually grab the result set and do something with it.

.execute is used to run an action query or sp on the server.  It doesn't return a result set.
.openrecordset is used to open a recordset that you can manipulate via vba

There is no ADO or DAO method that opens a recordset you can interact with in the GUI.  If you want to present the recordset to the user, bind it to a form or report and open that.

ok, I guess, a little baffled.
I have a pass through query now.
I have the DSN connection string stored in the pass through query.

I simply do this do view the stored procedure from sql server.
DoCmd.OpenQuery "sp_Vendor_Detail"
I see the results.

I created another stored procedure on sql server named
 "sp_Pass_Vendor_Detail"

but i must pass a variable to sql server from access.

DoCmd.OpenQuery is an Access VBA command.  It is NOT ADO, it is NOT DAO.  It works differently in that it works with the Access GUI.  DAO and ADO are not tied to Access.  They can be used from any application that supports them.  I think DAO only works with VB, VBA, and VBScript but ADO has a .net option that works with any .net language.  They do not work with any GUI.  They are batch only operations.

However, you can open an ADO recordset and bind it to a form or report.  That crosses the bridge to allow you to use the Access GUI.

so once i get to this part:
cmd.Parameters.Append cmd.CreateParameter("@Account", adVarChar, adParamInput, 10, ACCOUNT_NUMBER)
Set rst = cmd.Execute

How do i view the recordset ?

i tried this:
rst.Open cmd.Execute

but will not work

fordraiders

I don't use ADO so I can't give you an example.  I think it is similar to the DAO method which is

Dim db as DAO.Database
Dim td as DAO.Tabledef
Dim rs as DAO.Recordset
    Set db = CurrentDb()
    Set td = db.TableDefs!tblImportLog
    Set rs = td.OpenRecordset

this is working as far as executing the code: and returning a records.


BUT, I need to see/view those records in a query ??????


Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConnect As String
strConnect = "provider=sqloledb;Server=PR.campus..com;Database=SS_flow;Uid=xxx;Pwd=xxxx;"
' Instantiate the connection object
Set cnn = New ADODB.Connection
' Open the connection based on the strConnect connect string arguments
cnn.Open strConnect
' Instantiate the command object
Set cmd = New ADODB.Command
' Assign the connection and set applicable properties
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_qsupp_LOOKUP"
' Instantiate the recordset object by using the return value
' of the command's Execute method. Supply the parameters by
' packing them into a variant array
cmd.Parameters.Append cmd.CreateParameter("@Account", adVarChar, adParamInput, 10, ACCOUNT_NUMBER)
Set rst = cmd.Execute()

thanks, i just created a view. and embedded the argumnt.

Good solution.