Solved

calling stored procedure from access but will not open query

Posted on 2016-08-11
14
29 Views
Last Modified: 2016-08-12
access 2010
sql server 2008 r2


Trying to open a stored procedure in query view
passing a parameter from access

Will not open stored procedure

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConnect As String
Dim ACCOUNT_NUMBER As String

strConnect = "provider=sqloledb;Server=RMDB5.campus.com;Database=SS_Pro;Uid=xxxx;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


ACCOUNT_NUMBER = Me.SUPPLIER_LOOKUP

' Assign the connection and set applicable properties
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_qsupp_LOOKUP"



cmd.Parameters.Append cmd.CreateParameter("@Account", adVarChar, adParamInput, 10, ACCOUNT_NUMBER)
Set rst = cmd.Execute

Set rst = Nothing
Set cnn = Nothing
Set cmd = Nothing

Open in new window



Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41753393
any error(s)

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41753394
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 ...
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41753748
Correct i want to view a stored procedure
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41753868
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41753900
.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.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41753911
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41753920
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.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 3

Author Comment

by:fordraiders
ID: 41753927
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 ?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41753935
i tried this:
rst.Open cmd.Execute

but will not work

fordraiders
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41753960
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
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41754025
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()
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41754070
BUT, I need to see/view those records in a query ??????
We're going in circles here.  If you want to view the resultset, bind it to a form or report.  No ADO method will open a query for you to view.

Did you try creating a passthrough query?  The problem is the argument.  You can't have a passthrough query that takes an argument.  Therefore, you must create the passthrough query using VBA with embedded arguments.  Save the query.  Then you can open the saved querydef with the GUI as long as its returns records property is set to yes.

You cannot do what you are trying to do by using ADO in code.

Do you actually need to run a stored procedure?  Can you create a view?  You can link to the view and work with it as you would with a linked table.  Typically stored procedures do stuff.  They don't gather data for presentation.  You use views and Access queries for that.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41754174
thanks, i just created a view. and embedded the argumnt.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41754194
Good solution.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now