Solved

Using sp value in access vba

Posted on 2009-05-13
3
409 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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

16 Experts available now in Live!

Get 1:1 Help Now