Solved

Using sp value in access vba

Posted on 2009-05-13
3
413 Views
Last Modified: 2013-12-05
I have a stored procedure in SQL Server 2005 that return a value,I  want to use that value the access VBA 2007 without using ADO. Is is possible? or there is no way I should use ADO.
0
Comment
Question by:Yadtrt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24379197
Returns a value how?

If you return it as a recordset (i.e. SELECT the value rather than return it) then you could use the Access Application object to return it - but you'd be implementing default ADO methods from that application (so not circumventing ADO).

If you simply return the value then you'll not necessarily even get that value at all!

Ideally you'd return it as an Output parameter - but you will then need be using ADO explicitly to retrieve that value.
0
 
LVL 6

Author Comment

by:Yadtrt
ID: 24379300
I want to return single value just like this procedure,

create procedure testD
@nVal int
@Dval int output
as
begin
If EXISTS (select Ncol from Table1 where F1=@Nval)
set @Dval=2
end
 

How can I use the @Dval value in the Access VBA
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 24379445
By using ADO.  
To be fair, IMO, if you're going to work in ADPs then you need to be familiar with ADO.
That's less true for DAO in MDBs - but I feel you're tying one hand behind your back by avoiding it.
OK - maybe two hands. ;-)

    Dim cmd As ADODB.Command
 
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdStoredProc
        .CommandText = "testD"
        
        .Parameters.Append .CreateParameter("@nVal", adInteger, adParamInput, 4, intSomeValue)
        .Parameters.Append .CreateParameter("@Dval", adInteger, adParamOutput, 4)
        .Execute
        
        'Grab the output parameter value
        Debug.Print .Parameters("@Dval")
    End With
    
    Set cmd = Nothing

Open in new window

0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

690 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